What's the most efficient way in VBA to pick up data from a different closed Excel file, and place it in the current file.? I've tried various Copy /Paste commands but it complains that I've exceeded the memory limit (after selecting only about 30 cells)?
I think I've seen how it can be done in one statement - but lost track of the suggested solution.
something like Workbook(name).Sheet(name1).Cells(A5:B60).Copy Workbook(name2).Sheet(name3).Cells(A5) ?
Thank you kindly. Susanne
Hi Susanne,
The most efficient way to get data from one workbook and place it in another is with Power Query, not VBA.
1. Power Query tab, or Data tab in Excel 2016
2. From File > From Excel
3. Browse to the file location
4. Choose the Sheet/Named Range/Table containing the data
5. Either Edit to make changes to the data before loading to Excel, or simply click Load.
Let me know if you need help installing Power Query, and if so what version of Excel you have.
Mynda
I have Power Query installed, but the end users of the spreadsheet/macro won't have it . It's just data from a few cells in the other spreadsheet (which is different each time the user runs the macro), so now I'm looking into using Vlookup to pull the data from the other spreadsheet.
Thanks for your suggestion, I'm going to try it out for another request I've gotten.!
Susanne
