Excel ADO

charleslliu · January 11th, 2006, 4:09 pm

could anyone let me know ------------- 'how to access more than 2 excel files at the same time with VBA ADO?' thanks lot

afoster · January 12th, 2006, 7:35 am

LOLWhy on earth would you use a database access technology to read a spreadsheet? Just reference the Excel object library from your host VB application and code against that.

sjoo · January 13th, 2006, 4:18 am

even if it can be able to access more than two files at the same time with ADO,it may be veeeeeeeeeeery inefficient!!how about finding another way?best regards,sjoo

charleslliu · January 13th, 2006, 1:35 pm

many thanks everyone, the problem I am facing is to pull information (around 100 rows based on what kind of info I want) from 4 excel files and each of them has more than 65,000 rows. Then I have to dump selected info into another excel file which is the template. It looks like ADO is not the choice. Any ideas, method to solve this problem? your help is much appreciated. Thanks

sloane · January 13th, 2006, 2:25 pm

You might try linking the spreadsheets into an access DB, do the filtering in queries there, and write the data out to another linked spreadsheet. Not pretty, but it is effective.

afoster · January 13th, 2006, 3:00 pm

It sounds as if the 4 files you are reading are probably not xls files, but probably csv. If you want to use SQL to select the correct records, then you need to get this data into a database - Access is fine for this kind of thing. The quickest way to insert the data is using a bulk copy procedure. You can implement this in MS Access using a macro, and the TransferText ActionTo output the data, reference the Excel Object Library from within Access, you can then open up your spreadsheet and write the data to each cell as required.

charleslliu · January 13th, 2006, 3:06 pm

thanks, definitely will try

gallag · January 21st, 2006, 6:13 pm

Heres a good tutorial that will show you, step by step, how to get the information you want out of an Access database and into Excel using ADO: www.jsgrant.com/tutorials/ado/If you want to do the opposite (put information into an Access database from Excel) you can use the same code but using an INSERT statement.

charleslliu · January 24th, 2006, 8:35 pm

thanks gallag, much appreciated

alvincho · January 24th, 2006, 11:46 pm

I have an article in my blogReading Excel files from .NET/C#It might be the same to do that in Excel VBA.

charleslliu · January 25th, 2006, 7:34 pm

thanks Alvin, I tried "excel.Workbooks.Open" function before and found out it is not user friendly. The problem I am facing is how to load data from 4 excel files and each of them has 65,500 rows.

alvincho · January 26th, 2006, 1:30 am

I think 65500 is the limit that Excel can handle data in a friendly way. Beyond the limit, you have to do it by programming VBA.