Importing data from other workbooks

Hi,

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:

Please Login or Register  to view this content.

where Count is the counter for the current workbook I am importing data from.

This is in Excel 2003.

Thanks for your help!

I may be out-of-date on this matter but I recall the only way of extracting data from a closed workbook is to treat it as a database and use the ActiveX Data Objects, ADO, to read it. (Can also use DAO?) This, in turn, demands the data be structured in a table format in the source workbook.
If the underlying data is nice and tidy, you stand a chance but it means some VBA code and knowledge of SQL, the query language usde to define what is to be extracted. It does work in Excel 2003 (& up) as I have used it as part of a larger application to return about 1,000 rows of 50 columns. Other data was coming from a SQL Server database. I cannot comment on speed as I didn't test it but it was acceptable for one look-up.
Is your underlying data structured?

Yes, the data I am trying to import is structured into tables. I need to read about 15 of the 50 columns and all 1440 rows for these columns.

Can you post a sample of the data (just a few rows to keep the size down) to show the structure and indicate which column you require (perhaps colour-code them)? With that it should be possible to produce a bit of code to read the data into a new workbook to illustrate the method.
By the way about how many workbooks do you have to process?

I'm not sure how to make a table so I just took a picture.
I need to copy columns R to W from rows 5 to 1444.
There are more columns to be copied but I can work those out after.
I am processing a maximum of 15 workbooks but usually around 3 to 5.

sample.jpg

I think I managed to do it using the ActiveX objects. Thanks for mentioning that!

Does that you mean you've solved it? If so, super! As you may have found, there is quite a bit of information out there about VBA, databases e.g. http://vbadud.blogspot.com/2008/05/u...-database.html