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



Posted by Ivan F Moala on July 29, 2001 6:57 PM

Tom
I don't think you are clearing the error
so it is not reset for the next On error.
Means you have to place an Err.Clear after
appropriate code OR On error resume next either
way it means a few more Error coding to place in
your routien.

Since you are repeating some code...cam I make a suggestion.........
Also to get away from the GOTO's as this makes
it had to decipher ....in large code and should be
refrained against.
Any way try something like...


Option Explicit
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


HTH

Ivan