How to open a Sharepoint file on Excel with VBA

Question

Hi,

I would like to open an Excel file that is on our Sharepoint. I tried this code:

LeSite ="https://sharepoint.com/:x:/r/sites/xxxxxThenamexxxx.xlsm?d=wea5d3e23c3754266ab5948e0a7b226d4&csf=1&web=1&e=FFvezQ"

ActiveWorkbook.FollowHyperlink Address:=LeSite

But it opens on the webpage of SharePoint (Google Chrome) and I would like to open it on the Excel application.

Then, I tried a code that I found online but it downloaded it.

I searched for an answer online but did not found. How could I do ?

Thank you all :)

Answer 1

Hi BERETTA Vincent,

Thanks for posting your issue in our community. Regarding to your issue, we totally understand your feeling and requirement, you would like to open a SharePoint file on Excel with VBA. I’ve test it on my side, I upload an Excel file with a simple macro and the file can be opened as fine in Excel application.

To further identify your issue is related to your macro or not, please create a new worksheet with a new macro to check the outcome.

Besides, make sure you are using the latest version of Office and check if the new updates can open the file well.

For your reference: Install Office updates

If the issue still persists, could you please provide the following information with us to better investigate your issue?

1. Does the issue happen on all files or a specific file? Please check the new worksheet with a simple code and tell us the result.
2. What’s your Excel version? Please open your Excel application> click File> Account> capture the screenshot of “Product Information” page with us.
3. Do other users in your organization also cannot open this file in app?

Please feel free to contact us with any updates.

Best regards,

Gloria

Answer 2

Hi Gloria,

Thank you for your answer.

Could you post the macro that you used please ?

To be clear, the macro is on a file A and the goal is to open, in the Excel application, a file B which is stocked on a SharePoint.

If the issue still persists, could you please provide the following information with us to better investigate your issue?

1. Does the issue happen on all files or a specific file? Please check the new worksheet with a simple code and tell us the result.
2. What’s your Excel version? Please open your Excel application> click File> Account> capture the screenshot of “Product Information” page with us.
3. Do other users in your organization also cannot open this file in app?

1. It applies on all files of the SharePoint. I tested on a new worksheet and new file, same result.

2. 

3. The file can be opened in the app, but we need to use the "Open in the app" button. If we just click on the file, it opens on the web application.

Thank you for your time Gloria.

EDIT:

I found a part of what I wanted to do ! The file opens, but its on "Read only" mode. Nothing is on the file.

The link is the one that you have when, on Sharepoint, you click on the button "Copy the link".

Sub test_2()

Le_site = "https://sharepoint.com/:x:/r/sites/XXX/Shared%20Documents/General/Suivi%20des%20routines/suivi_routine_montage.xlsm?d=wea5d3e23c3754266ab5948e0a7b226d4&csf=1&web=1&e=7RCazy"

Workbooks.Open (Le_site)

Windows("suivi_des_routines.xlsm").Activate

Sheets("Suivi 2020").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Range("A1").Select

End Sub

Answer 3

Hi BERETTA Vincent,

Thanks for posting back. Regarding to your issue, may I confirm now you prefer to use the "Open in the app" button instead of open in the web application?**(If I misunderstand your requirement, please point out.)**Given your situation, you need to enable the default setting for opening the file in app, please open the library, click Library Settings in the Ribbon> Click Advanced Settings> Open in the client application( or you can choose Use the server default(Open in the browser and then when you click the file you can choose “Open in App” option.)

If this is not what you mean, please post back and we would like to further investigate it.

Thanks for your patience.

Best regards,

Gloria

Answer 4

Hi Gloria,

Thank you for your answer.

I changed the setting as you said but it's still the same, the file opens on the webapp.

Here is the code that I use:

Sub ouverture()

LeSite = "https://sharepoint.com/:x:/r/sites/XXX/Shared%20Documents/General/Suivi%20des%20routines/suivi_routine_montage.xlsm?d=wea5d3e23c3754266ab5948e0a7b226d4&csf=1&web=1&e=Po24ut"

ActiveWorkbook.FollowHyperlink Address:=LeSite

End Sub

Thank you for your time Gloria.

Answer 5

Hi BERETTA Vincent,

Please do not mind my late reply due to Weekends, as I have test it with a simple code in PM, it works at my side, while the code is beyond our support scope, to further help you, we will help you move this thread to the programming category to get the dedicated help, the code experts there would further investigate the issue and provide the related suggestions for you.

Appreciate your understanding and cooperation.

Best regards,

Gloria