Migrating Macros to SharePoint

Question

Hello, 

I was Reviewing this forum for answers of the problem I'm having to migrate Macros to SharePoint, I'm having an issue to send data from excel to access or get data from other excel sheets (all of them uploaded to SharePoint), obviously prior to SharePoint the way of storage was to enter your local address (e.g.  C:\Username\Documents\FileName.xlsm). 

At the moment I haven't found yet a correct way to structure the SharePoint address in order to pull data from another file or to save data to another file from your current Macro usage. Can you please give me support of examples of the correct way to write the code on my Macro to access another file and have live data being communicated one from another?

Please reach out to me if your having difficulty on understanding what my problem is. 

-Daniel Escobar

Answer 1

Hi Juan,

We understand it is not possible to directly connect to Excel workbook stored in SharePoint online but there’s a way to achieve your requirement. If you are trying to import data from Excel workbook that is stored in SharePoint online, open that workbook in desktop application and then click File>Info>Copy path.

Clicking “Copy path” will give you a path in this format: https://Tenant.sharepoint.com/sites/SiteName/Shared%20Documents/FileName.xlsx?web=1

With this starting point, all you need to do is remove the ?web=1 query string parameter at the end of the URL, and you have the path to the workbook that Power Query needs.

If you are importing data to Excel, you can use Get Data tab. Choose “From Web” as the data source in the Get Data menu, and paste this URL into the Dialog.

Click Ok and it will open a small Window, select Organizational Account, click Sign in and enter your work or school account. This will open a navigator, where you can select tables and worksheets, and can start manipulating and analyzing your Excel data.

Please check if this works for you. If you need nay help let me know. Have a nice day and stay safe😊

Regards,

Neha

Answer 2

Hi Juan,

Did you check the above reply? Please update us if you still need any help.

Regards,

Neha

Answer 3

Hi Neha, thanks for the support it did help and I hope it can help anyone who is having the same problem, if I have any further question I will reply again. 

Regards, 

-Daniel Escobar

Answer 4

Hello Neha, what I'm about to ask is not for my current problem is just for further applications. How can I generate an API web key from an access file that's on my SharePoint? 

-Daniel Escobar

Answer 5

Hi Daniel Escobar,

Please check the following links, see if this help.

Get to know the SharePoint REST service

Working with folders and files with REST

Further, since you want to generate Rest API if you still get any issue and need further help, we’ll suggest you post in Microsoft Q &A forum for help. In Answers forum, we don’t not have much resources to help you with your query and in Microsoft Q &A forum you will get the qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction.

We appreciate your understanding. Have a nice day and stay safe.

Regards,

Neha