Import/Export Data From Excel Stored on OneDrive

[Harshil Mehta]

I am trying to build a tool which exports data from desktop excel to another excel file which is stored on OneDrive and vice versa.

Is this possible with VBA? If yes, then how should I go about it?

 

[severynm]

Hi.

No need to build anything - Excel already has a tool built in called Power Query. From the Data Tab → Get Data → From FIle → From Workbook. Point it to the workbook you want and you're good. You have the option to apply any transformations you want to clean or shape the data, and all you need to do is hit the "Refresh" button on the same ribbon tab to automatically update the data and any applied transformations (if any).

 

[Harshil Mehta]

Hi.

No need to build anything - Excel already has a tool built in called Power Query. From the Data Tab → Get Data → From FIle → From Workbook. Point it to the workbook you want and you're good. You have the option to apply any transformations you want to clean or shape the data, and all you need to do is hit the "Refresh" button on the same ribbon tab to automatically update the data and any applied transformations (if any).

View attachment 40303

This will solve the problem for importing data. How about sending data to the file stored on OneDrive?

 

[severynm]

You could always do the same for the OneDrive workbook and query the data in, but I have no idea how it would work if the query was circular, and now that I'm thinking about it, it seems rather redundant. What is the purpose of having the two different workbooks with the same data?