VBA and Sharepoint

[Gringoire]

Hi guys,
for the first time I have to use Excel VBA to merge some excel files stored on SharePoint.
When I try to get the file path from SharePoint document folder I obtain something like:

https://xxxxxxxxx.sharepoint.com/:w:/s/sales-OfferTemplate/Eb63gnx65xxxxxhUXoU2tcywBdPCzMLlD9u-RShpKabEo2g?e=xxxxX

This link seems not works on VBA. I'm not even sure that the link is the same for different users.
Is there a simple way to obtain a more "human readable" link to the file? Soething like https://xxxxxxxxx.sharepoint.com/:w:/s/sales-OfferTemplate/myFile.xls ?

thanks.

 

[cooper645]

could you record a macro that saves a test file to the filepath you want to use. Then review the recorded macro for the filepath?
I think this is how i did did it 6 months ago when I needed to do a similar thing.

 

[rovert_enrobso]

Just like you I also tried copying the file link from the SharePoint site and passing it as the path parameter to the Workbooks.Open() method. I finally came across a post that provided a breakthrough solution after all the failed attempts at changing the SP file url (removing https, switching forward slashes to backward slashes, etc.). The url I was passing to Workbooks.Open() method was obtained by clicking "Copy link" for the designated file on the SP site. The miracle post that I discovered detailed that a subtle process change needed to be made to obtain the url that would successfully locate and open the file.

The process is as follows:
- On the SP site, click the "Show actions" icon (kebab menu) next to the designated file and select Open -> Open in app
- Once the file opens, navigate to the 'File' tab on the Ribbon and select 'Info' tab on the sidebar
- You should see the file name at the top of the pane and the file breadcrumb trail underneath with the option to copy the path

- Paste the copied path as is to the Workbooks.Open() method and it should open successfully

When I compared the path copied from the file breadcrumb trail on the Info tab to the path that was copied from the SP site, the subtle difference I noticed was that my personal credentials were present in the breadcrumb trail path and there were actually some different folder references.

Info tab path: https://[companyname]-my.sharepoint.com/personal/[myname]-[companyname]-com/[folders leading to file]?web=1
vs
SP site path: https://[companyname]portal.sharepoint.com//r/sites/[folders leading to file]?d=xxxxxxxxxxxxxxxxxx&csf=1&web=1&e=xxxxx

Hopefully this helps. I am no expert but I believe the reason that there are so many posts with different solutions that work for others is just due to the permission settings that are configured for different companies/users. Looking at the paths, when I actually physically access the file it is being pulled from a different path than what is present on the main SP site (looks like it stores the files that I have access to in my personal directory). I just had my first encounter with SharePoint this week and had only created macros that navigated files located on a network drive so I was banging my head for awhile.