Need help closing multiple files using the Dir function in Excel VBA
Posted on 2002-03-28
I have an excel spreadsheet that opens a code sheet and then if exists, multiple branch*.xls files. I previously used the Application.Filesearch function, but this no longer works with Windows 2000. I am now using the dir function. I can get all the files open, but when I exit Excel, I need to close these branch*.xls files without asking me whether I want to save, they are read only anyway. Below is the code I am using. Any help would be appreciated.
Sub HandleLocalizations(sourcedir As String, opening As Boolean)
' allow the opening and run the auto open code for mulitple branch.xls files
' these files must all be in the same directory as the point schedule tool and
' must be named in the form branch*.xls, open read only in case on network
' added flag for opening or closing, when closing, will call auto_close of
' branch*.xls file(s) and will close w/o prompting for save, even if changes made
Dim filenames() As String, filenum As Integer
Dim i As Integer, justfilename As String, longname As String
FileCounter = 0
sourcedirfile = sourcedir + "\branch*.xls"
searchfile = Dir(sourcedirfile)
Do While searchfile <> ""
PathandFile = sourcedir + searchfile
FileCounter = FileCounter + 1
ReDim Preserve filenames(FileCounter)
filenames(FileCounter) = PathandFile
If opening = True Then
Workbooks.Open FileName:=PathandFile, ReadOnly:=True
Application.Run Macro:=searchfile + "!Auto_Open"
For Each w In Application.Workbooks
longname = sourcedir + w.name
If member(longname, filenames) = True Then
Application.Run Macro:=w.name + "!Auto_Close"
Application.DisplayAlerts = False
searchfile = Dir()
Function member(item, list) As Boolean
' returns true if item is member of collection list, false if not
Dim found As Boolean
found = False
For Each i In list
If i = item Then
found = True
member = found