my code heavily relies of the fact that:
dim dataSource as Worksheet
is set as a sheet in another workbook as Im calling on it many times throughout my code. it works fine as long as that other workbook (sourcefile) is local. but now i need it to be relocated to one drive and i cannot find a way for this to work with onedrive files
at first i created my code to call on a local file using this method;
Dim dataSource As Worksheet
Set dataSource = Workbooks.Open("C:\\Users...\\BMS Monitoring Data Export.xlsx").Sheets("Sheet1")
and as long as the source file was local the code worked. then I had to relocate the source file to a one drive so I just replaced the URL with the new file location only in onedrive. and I mean that i navigated to the file with windows browser and copied the url. but that did not work. it said that the file cannot be located
I tried recoding the macro of how Im opening the file by going file/open/open from one drive... and the macro recorded this:
Workbooks.Open Filename:= "https://companyname.sharepoint.com/sites/EnerzUAB/Shared%20Documents/.../BMS%20Monitoring%20Data%20Export.xlsx"
so I just copied that for this:
Dim dataSource As Worksheet
Set dataSource = Workbooks.Open Filename:= "https://company name.sharepoint.com/sites/EnerzUAB/Shared%20Documents/.../BMS%20Monitoring%20Data%20Export.xlsx"
and this gives an error : "Syntax error"
so I added parenthesis and .Sheets() at the end:
Dim dataSource As Worksheet
Set dataSource = Workbooks.Open(Filename:= \_ "https://companyname.sharepoint.com/sites/EnerzUAB/Shared%20Documents/.../BMS%20Monitoring%20Data%20Export.xlsx%22).Sheets(%22Sheet1")
now it just says that it cannot locate the file. does anyone know what is the problem here?
