Need help closing multiple files using the Dir function in Excel VBA

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"
    Else
         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
         w.Close SaveChanges:=False
    End If
    Next
    End If
    searchfile = Dir()
Loop
End Sub

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
            Exit For
            End If
    Next
    member = found
End Function
TammiAsked:
Who is Participating?
 
DanRollinsConnect With a Mentor Commented:
Hi Tammi,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept bruintje's comment(s) as an answer.

Tammi, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
bobbit31Commented:
what's the error?
0
 
TammiAuthor Commented:
There is no error, it just asks to close the file and I want it to close without asking.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bobbit31Commented:
odd, i just have:
w.Close SaveChanges:=False
   
and it doesn't prompt me (w/o that line it does)
0
 
bruintjeCommented:
Hi Tammi, does this prompt too ? > w.Close False
0
 
TammiAuthor Commented:
I have w.Close SaveChange:=False and I still get the prompt.  I also used w.Close (False) and still got the prompt.
0
 
TammiAuthor Commented:
Currently I have three different branch*.xls files that it finds, one doesn't have an Auto_Close function in it, and that one closes without prompting, the other two I get the prompt to save.
0
 
bruintjeCommented:
so it's the auto_close playing up?

you could add a line there

ActiveWorkbook.Saved = True

i don't know if it works if you use it on another place in your code before closing like

Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
w.Close False

:O)Bruintje
0
 
bruintjeCommented:
or open them with this line added

ActiveWorkBook.RunAutoMacros xlAutoDeactivate

HTH:O)Bruintje
0
 
TammiAuthor Commented:
This was so long ago I forgot what I did to fix the problem, but I know I didn't use any of the answers listed.
0
 
DanRollinsCommented:
Tammi,
You did not bother to read my post.  What part of:

    DO NOT accept this comment as an answer.

is unclear?  And why have you ruined by GPA by insulting me with a C?  

-- Dan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.