Code in one macro to open 3 files if not already open

Posted by Tom Urtis on July 31, 2001 6:44 AM

Re-posted after last week's Black Hole incident:

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? Here's how I do it for one file but I don't know how to do 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"
'Now the file is open either way
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

Posted by Ivan F Moala on July 31, 2001 6:17 PM

Tom, not sure if you saw my post and if it was
acceptable but here is a repost of the code....
I suggested that your error has not been reset
via Err.Clear or on error goto 0.
To do this you will have to place them @fter most
of your error calls.....this can be long winded
especially when coupled with GOTO statements.
In general it is good practice to refrain from
too many GOTO as they can give you what prgmers
refer to as spagetti programing....hard to untangle or read and decipher latter.

Can I make this suggestion;

Option Base 1

Sub Open3Files()
Dim sFiles
Dim x As Integer

sFiles = Array("File1.xls", "File2.xls", "File3.xls")

For x = 1 To 3
If IsItOpen(sFiles(x)) Then
Windows(sFiles(x)).Activate
Else
ChDir "C:\ExcelFiles"
Workbooks.Open Filename:=sFiles(x)
End If
Next x

End Sub

Function IsItOpen(Filename) As Boolean
On Error Resume Next
Windows(Filename).Activate
If Err Then
IsItOpen = False
Else
IsItOpen = True
End If
On Error GoTo 0
End Function

If you need it the way you have set up the by all means do it just claer your errors after and befor another error occurs.

Ivan