Using an Array for Filenames in Excel VBA

Help,

I'm trying to use an array to list filenames in a particular directory in Excel, open the files or close the files.
I can get he file open, but when I close one of the files, I want all the files to close and am having problems with this.

Here's my code so far:

Dim filenames() As String, filenum As Integer
Dim i As Integer, 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)
    For i = 1 To FileCounter
        filenames(i - 1) = PathandFile
    Next i
    If opening = True Then
        Workbooks.Open FileName:=PathandFile, ReadOnly:=True
        Application.Run Macro:=searchFile + "!Auto_Open"
    Else
        For Each w In Application.Workbooks
        MsgBox w.name
        longname = sourcedir + w.name
        If member(longname, filenames) = True Then
            Application.Run Macro:=w.name + "!Auto_Close"
       
        w.Close (False)
        End If
        Next
    End If
    searchFile = Dir()
Loop

Any help would be appreciated.

Thanks!!

Tammi
TammiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
Your problem is here:

   ReDim Preserve filenames(FileCounter)
   For i = 1 To FileCounter
       filenames(i - 1) = PathandFile
   Next i

You are setting every entry in the array to the same filename each time, when you should be setting only the last entry to the new filename.  Replace this with:

   ReDim Preserve filenames(FileCounter)
   filenames(FileCounter) = PathandFile

Now, your lookup of the filename in the Member routine will work.
0
 
TammiAuthor Commented:
Thanks!!  This did the trick.

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.