I was recently working with a set of excel spreadsheets that have macros to consolidate data from multiple diffrent files into a single consolidated file. I needed to move these to sharepoint online, and it took me a long time to find the correct URL format to pass to the Workbooks.Open Filename parameter in order to open the sharepoint URL. What worked for me was the following URL format:
\\tenant.sharepoint.com@SSL\sitename\path\filename