Querying multiple Excel files

[Q]

We have a few hundred Excel files, whose structure is the same: a one page
numeric data worksheet and a few graphs. We like to extract some numeric
data out of some of these files.

A colleague has written an Excel macro to do it, and he commented that it is
slow as it takes a min or two in each run. And he needs to run it multiple
times. I read somewhere before that we can query an Excel file. I saw an
example for querying one file. I wonder would it be possible and practical
to query multiple Excel files?

Or any idea on how best to extract data in multiple Excel files?

Thnaks,

Q

 

[Joel]

If you have a few hundred workbook that take ONLY two minutes to open and
close that is pretty good. Thats a 1/2 second a file.

When you say querying I think you mean accessing the files without opening
the files. These are done with database instructtions not a query. You
would create an Access Object and usingg Access Macro instructions (inside
excel) read the data. It is a little bit harder to get the data in this
fashion because in Access you have to move down X number of rows and then
move Y number of columns to get the data. In Excel you can randomly move to
any cells. I'm not sure how much improvement you will really get because you
will still havve to connect to each file.

 

[Ron de Bruin]

Hi Q

See how fast this is
http://www.rondebruin.nl/merge.htm

There is code also on my site

You can try ADO also (you not open the files then)
http://www.rondebruin.nl/ado.htm

 

[Q]

Thanks Ron de Bruin. Somehow, it does not work for me. The .xla setup was
fine, but somehow, it gave me an error when trying to merge the data.

My colleague who needs this came by, and we found a solution. He has a
macro that further processes those Excel files that are generated by my
program. If I incorporate his code to my program, then we are good.

Thanks again,

Q

 

[Q]

Joel

Thanks for your reply. Two mins is for reading and processing only a subset
of the files out of the hundreds.

Q

 

[wojciechg]

We have a few hundredExcelfiles, whose structure is the same: a one page
numeric data worksheet and a few graphs.  We like to extract some numeric
data out of some of these files.

A colleague has written anExcelmacro to do it, and he commented that it is
slow as it takes a min or two in each run.  And he needs to run it multiple
times.  I read somewhere before that we can query anExcelfile.  I sawan
example for querying one file.  I wonder would it be possible and practical
to query multipleExcelfiles?

Or any idea on how best to extract data in multipleExcelfiles?

Thnaks,

Q

www.afin.net/AFIN.NET.Light
or contact me for more info