Extracting Data from Closed Workbook

[anthony_91]

Hi,

I'm trying to extract data many Excel workbooks. The data isn't in a structured format but rather in specified cells, e.g. cell A3, C5, E7 and so on.

The approach I've currently taken is to use the Workbooks.Open method to open each workbook, copy the data and close the workbook. However, I'm finding this method to be really slow as I have to open the workbooks which is stored on a network drive. Additionally, I have to loop through thousands of workbooks to extract data, therefore, this method is very time consuming. Is there an alternative method to extract data from workbooks which is faster?

Thanks,
Anthony

[jindon]

If the sheet names and the cell address are known, you can use:

1) Formula linked to those specific cells and convert to the value.
or
2) ExecuteExcel4Macro method.

[anthony_91]

Thanks. Will the ExecuteExcel4Macro method work on a closed workbook?

[jindon]

Both should be able to get the data from the closed workbook without opening.

[anthony_91]

I've just tried it out and it works although it's very slow. It's actually quicker to use the workbook.open method.

[jindon]

I don't know how you tried.
I have not experienced such thing like slower than opening.
So, I should say bad luck.

[anthony_91]

Do you know much about ADO connection and whether that might be quicker?

[jindon]

Few examples in this forum

https://www.excelforum.com/excel-pro...onnection.html
https://www.excelforum.com/excel-pro...-workbook.html
https://www.excelforum.com/excel-pro...workbooks.html

[anthony_91]

Thanks Jindon for your help. Much appreciated

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.