Get data from external workbook/Power Query

realtoast

New Member
Joined
Nov 24, 2015
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Experts, I have a macro, GetExcelData (), that retrieves the contents of a single worksheet from a workbook located in a network folder. User clicks a button and, presto, the needed data is deposited in a new worksheet in my affected workbook. Works famously. However, because the macro, based on a Power Query process, creates a very specific query, if someone changes even a single character in a header in the target worksheet the macro crashes.

Is there a means of using a macro/VBA to get generic data from a remote folder and place it in a new worksheet? By generic, I mean, the macro seeks and retrieves whatever data found in the specific network folder.

Example A: NetworkFolder contains an Excel file, Houses.xlsx. That workbook has a single sheet, Sheet1, with three columns, Street, City, Zip. My current Power Query would easily retrieve all that and put it into my active workbook as a new sheet.

Example B: Same as above, but the Excel file located in NetworkFolder is now, Cars.xlsx, a single sheet, named "MyCars" has 5 header fields with various respective data.

Is there a means of having a single, universal macro/VBA that gets the contents of whatever file is presently in NetworkFolder, regardless of the name, contents, or even different sheet names?

If they answer is No, or NFW, that will save me a lot of time. If it is possible, please share.

Thanks!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top