Reading Data from workbooks without opening them ...

[RFraley]

I have a VBA routine (XL2003) that scans the harddrive for workbooks with a
certain pattern (first 4 charac ters of name = "CLSd") in the filename. I
am currently using ExecuteExcel4Macro to read data from each of these
workbooks without having to open each one. I do this for performance
purposes as it is much quicker than opening and closing each workbook
individually. Where I run into a problem is when the workbook is password
protected. When a password is encountered; excel prompts the user to enter a
password. Apparently I can't pass the password to avoid this and that is my
problem. The user doesn't know the password because it is calculated by a
macro. I can calculate the password, but I can't pass it to avoid havint he
user be prompted. The bottom line is that even if the user knew the
password, I don't want the routine to prompt the user for 40 or more
passwords each time the routine runs.

If anyone knows how to get around this limitation, I would very much
appreciate assistance with this issue. If not; is there another way to read
data from a closed workbook that does allow for passing passwords so that
data from all of the workbooks found during the scan can be retrieved
without user intervention?

Thanks in advance for any assistance given.
Ralph Fraley

 

[Don Wiss]

I have a VBA routine (XL2003) that scans the harddrive for workbooks with a
certain pattern (first 4 charac ters of name = "CLSd") in the filename. I
am currently using ExecuteExcel4Macro to read data from each of these
workbooks without having to open each one.

ExecuteExcel4Macro? But xl2003 is Excel 11.

When I want to read a cell from a closed workbook without opening it, I
have my VBA create a link in a cell to a cell or range in the closed
workbook. Then the VBA changes the cell to a value with
Range("xx").Value=Range("xx").Value. But we don't have password protected
workbooks, so I have no idea how this will work with them.

Don <www.donwiss.com> (e-mail link at home page bottom).