macro to open and close Excel workbooks

**norman0000** · 12-09-2010, 06:40 PM

I have created a system to manage mortgage accounting. Each mortgage has it's own spreadsheet. File names: mortgage.xlsx, mortgage1.xlsx, mortgage2.xlsx up to mortgage49.xlsx.

I have created a master control spreadsheet that pulls information from all the accounts (spreadsheets).
Some of the spreadsheets may not exist, so to prevent a Macro error the file is only opened if it exists. Thus:

Please Login or Register  to view this content.

This part works fine. Having opened them I then need to close them in the same Macro.

However the following does not work and results in a Runtime error 9. Subscript out of range.
This trips up at the first statement below, which IS an existing file:

Please Login or Register  to view this content.

**JBeaucaire** · 12-09-2010, 07:59 PM

We could parse the 1-49, too with another loop, but just in case you want to use any random text strings for your workbook names, you can put them all into an array of string values like so, then just run them all:

Please Login or Register  to view this content.

**norman0000** · 12-10-2010, 01:15 PM

Thank you. I really appreciate you taking the time to help me.

Unfortunately your revised version fell apart at the same point as my original one.

Error 9.

Please Login or Register  to view this content.

I added

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

at the beginning and end of the code. Here is the final version

:

Please Login or Register  to view this content.

I just don't understand it as both your coding and mine looks like it should do the job.

**JBeaucaire** · 12-10-2010, 02:16 PM

Please revise your post to add code tags around the code, as shown in my posts and demonstrated in my signature below.

**JBeaucaire** · 12-10-2010, 07:25 PM

I'm thinking it may be as simple as blowing through those errors. Add this near the top with all the other App settings:

Please Login or Register  to view this content.

**romperstomper** · 12-10-2010, 07:33 PM

You don't want the path:

Please Login or Register  to view this content.

**shg** · 12-10-2010, 07:39 PM

When you close a workbook, you're referring to a workbook in memory, not the workbook on disk, so you don't include the path.

I can't imagine why you would want to open all the workbooks at once, rather than process them sequentially:

Please Login or Register  to view this content.

**norman0000** · 12-12-2010, 12:26 PM

Many thanks for all your help. Removing the path did the trick and it now works perfectly.

To answer the question about "why open all the workbooks at once".

This control workbook extracts values from the various individual mortgage accounts. I originally tried to open and close them sequentially.
The result however was that the value from the first workbook flashed on the screen for a second, but disappeared when the second value appeared.

Thanks to everyone who helped me.

Norman