Opening multiple files
Posted by Tom Urtis on July 29, 2001 6:38 AM
Hi everybody --
Question:
What's the code for opening more than one file if that/those file(s) are not already open on the desktop? It has been solved for one file, but I cannot figure out the code for more than one.
Background:
I created a report template with a macro that imports data from 3 Excel files. My goal is to not worry if any of those 3 files are already open. Here's the code I use for the first file:
On Error GoTo b:
Windows("File1.xls").Activate
'If the file is not open there will be an error and it will go to b:
GoTo c:
b:
ChDir "C:\MyDocuments"
Workbooks.Open Filename:="C:\MyDocuments\File1.xls"
c:
Windows("File1.xls").Activate
OK, so no problem, except that for files 2 and 3 this same syntax does not work, and I've tried On Error Resume Next and various other experiments but keep getting a Run Time Error #9, subscript out of range for File2.
As my macro stands now, I get by with simply using half of the above code for File2 and File3:
ChDir "C:\MyDocuments"
Workbooks.Open Filename:="C:\MyDocuments\File2.xls"
Windows("File2.xls").Activate
ChDir "C:\MyDocuments"
Workbooks.Open Filename:="C:\MyDocuments\File3.xls"
Windows("File3.xls").Activate
So what code am I missing (probably something simple) to give me the convenience of activating File2 and File3 whether or not they are open on my desktop, as I can with File1.
Thanks for any ideas.
Tom Urtis