I have a large number of excel workbooks with data in them (~7 MB each) that I am trying to create a macro to pull data from.
The goal is to select which workbooks I want the data from, pull the required data values, and then create a few graphs.
I previously tried another method that involved opening up the workbook without updating the screen and then copying the range. This is also a relatively slow procedure because each workbook takes a decent amount of time to open.
As of now, I wrote a code that loops over and gets 1 data value each time using the ExecuteExcel4Macro command.
I match the required position of the data value with the current index of the loop but I only pull every 60th value since it would take a long time to run otherwise.
This process takes about 0.1 seconds for each data value so it takes me a minute to pull the ~600 values I am using from each workbook.
I want the macro to be able to pull each value rather than every 60th value and complete this quickly. Is it possible to use ExecuteExcel4Macro or another command to copy a range of cells from the closed workbook I am accessing to a range of cells in my current workbook?
Here is a sample of the code I am using right now:
where Count is the counter for the current workbook I am importing data from.
This is in Excel 2003.
Thanks for your help!