VBA VLOOKUP with OneDrive file

I'm looking for a way to call the data from another file that is in Sharepoint/OneDrive to use it in a VLOOKUP formula in VBA but I can't find a way to do it, I've tried using Workbook.OpenLinks but it also doesn't work

A contribuiting factor is that I don't actually know what link to use, here's an example of the links I've tried:

File explorer:C:\Users\Me\Organization\Different person - His folder\Subfolder\subsubforlder\Required file.xlsx

Chrome:https://organizationindustrie-my.sharepoint.com/:x:/r/personal/different_person_organization_com/_layouts/15/Doc.aspx?sourcedoc=%xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx%xx&file=Required%20file.xlsx&action=default&mobileredirect=true

formulas in excel: 'https://organizationindustrie-my.sharepoint.com/personal/Different_person_organization_com/Documents/his Folder/subfolder/subsubfolder/[Required file.xlsx]Sheet name'!$A:$C

(I've tried this one also without the range $A:$C and Sheet name)

formulas in excel with the required workbook opened: '[Required file.xlsx]Sheet name'!$A:$C

Currently my code works but I need to open the required file before running the macro,

Here's the code that currently works with the required workbook opened

With ActiveSheet

    .Cells(i, lastcol + 1).Value = Application.VLookup(Cells(i, "B"), Workbooks("Required file.xlsx").Worksheets("Sheet name").Range("A:C"), 3, False)

End With

I don't need the workbook to open, I just need to call the data

To open the workbook you will need to use Workbooks.Open("path") where path is the full https link to the OneDrive file. To find the file path the easiest way is to load the file manually into Excel, then in the Debug Window type in this command:

Print Workbooks("Required file.xlsx").FullName

This should then show you the full path starting https://..... and ending with .../Required file.xlsx.

Once the file has been opened you can refer to the sheets in the file as normal. You should of course close it after getting the data you need.