Results 1 to 8 of 8
  1. #1
    snax500
    Guest

    Macro to Open all Files in Folder

    In Excel2000, how would I write a macro that will open all files in a particular folder. For example, I have a Folder called E:Test with 10 Excel files I want opened. Once opened I will then do something to each file and close the file. I only need help with the opening and closing of all files in a folder.

    Thanks

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Open all Files in Folder

    This is some code that allows you to enter a directory and loops through this directory. It finds all *.xls files for you (you can change this if you want, by changing the filter). Then it is up to you to do something with the returned filename. You can open it, change something and then close it. Just try it out. I think this is really what you want.


    Public Sub FindFiles(sPath As String, Files() As String, Optional Filter = "*.*")
    Dim sFileName As String
    Dim Directories() As String
    Dim i As Integer
    ReDim Directories(0)
    If Right(sPath, 1) <> Application.PathSeparator Then
    sPath = sPath & Application.PathSeparator
    End If
    sFileName = Dir(sPath, vbDirectory)
    Do While sFileName <> ""
    If GetAttr(sPath & sFileName) And vbDirectory Then
    If sFileName <> "." And sFileName <> ".." Then
    ReDim Preserve Directories(UBound(Directories) + 1)
    Directories(UBound(Directories)) = sPath & sFileName
    End If
    ElseIf sFileName Like Filter Then
    ReDim Preserve Files(UBound(Files) + 1)
    Files(UBound(Files)) = sPath & sFileName
    End If
    sFileName = Dir
    Loop
    For i = 1 To UBound(Directories)
    FindFiles Directories(i), Files(), Filter
    Next i
    End Sub

    Sub loop_through_dir()
    Dim FileArray() As String
    Dim i As Integer
    Dim sFolder As String
    Do
    sFolder = Trim(InputBox("Enter directory to summarize", "Monthly summary", "c:"))
    If sFolder = "" Then
    If MsgBox("Abort scan?", vbYesNo Or vbQuestion, "No Folder entered") = vbYes Then
    Exit Sub
    End If
    End If
    Loop Until sFolder <> ""
    ReDim FileArray(0)
    FindFiles sFolder, FileArray(), "*.xls"
    For i = 1 To UBound(FileArray)
    MsgBox FileArray(i)
    'do something with returned filename (open it, take out the data you need, close it, put the data in a common worksheet, etc.)
    Next i
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Open all Files in Folder

    I think that there might be an easier way. Try this; it has worked for me.
    Open all the files in the folder. Go to FILE and choose SAVE WORKSPACE (instead of Save or Save AS). Give your WORKSPACE a name; it will have an XLW suffix rather than the usual XLS. Then Open a new file and save it as, say "Seed.xls". Then open the VBA Editor (ALT F11), double click on This Workbook (for the SEED.XLS file). This will bring a code screen that will say "(GENERAL)" at the top. Click the down arrow to the right and chose "Workbook". This should open a screen for code to execute when the workbook is Opened (the Open Event). Insert the following code between the "Private Sub Workbook_Open()" and the "End Sub" statements:
    ChDir "folder address"
    Workbooks.Open FileName:= _
    "full address of the folder+resume.xlw", UpdateLinks:=3
    Workbooks("filename.XLS").Activate

    I am only a VBA beginer, so you may have to do some tweaking to get this to work right for you. For example, I wouldn't know if the 2 forward slashes would be needed in the address in your system and I am not sure if you need the "UpdateLinks:=3" and this may be a leftover from some other thing I was trying to do.
    In the above:
    you would replace "resume" with the workspace name that you chose and "filename" would be the name of the file that you would want to have open ready for input of all the others.
    As I said, this works for me as it is. Some of the VBA experts will probably comment on it and tell you how to make it more sophisticated.
    Regards,
    Stephen


    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Open all Files in Folder

    The following VBA procedure should open all of the .xls files in the directory E:Test

    <pre>Public Sub OpenAllFiles()
    Dim strFName
    strFName = Dir("E:TEST*.xls", vbNormal)
    While strFName <> ""
    Workbooks.Open (strFName)
    strFName = Dir()
    Wend
    End Sub
    </pre>

    Legare Coleman

  5. #5
    snax500
    Guest

    Re: Macro to Open all Files in Folder

    Thanks so much. I liked this macro the most:

    Public Sub OpenAllFiles()
    Dim strFName
    strFName = Dir("E:TEST*.xls", vbNormal)
    While strFName <> ""
    Workbooks.Open (strFName)
    strFName = Dir()
    Wend
    End Sub

    How do I use this if I have sub-folders and want to open all files in all subfolders?

    Thanks again

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Open all Files in Folder

    I know that the smallest macros are the most beautiful ones, but the code in my previous post, takes into account subdirectories. Change it as you wish.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Open all Files in Folder

    Well, that is a bit more difficult. The following code should do it:

    <pre>Public Sub OpenAllFiles()
    Dim strFName As String, strDirList() As String
    Dim I As Integer, J As Integer, iAttr As Integer
    ReDim strDirList(1 To 1)
    strDirList(1) = "E:TEST"
    I = 1
    While I < UBound(strDirList) + 1
    strFName = Dir(strDirList(I), vbDirectory)
    While strFName <> ""
    If (strFName <> ".") And (strFName <> "..") Then
    If GetAttr(strDirList(I) & strFName) = vbDirectory Then
    ReDim Preserve strDirList(1 To UBound(strDirList) + 1)
    If Right(strFName, 1) <> "" Then
    strFName = strFName & ""
    End If
    strDirList(UBound(strDirList)) = strDirList(I) & strFName
    End If
    End If
    strFName = Dir(, vbDirectory)
    Wend
    I = I + 1
    Wend
    For I = 1 To UBound(strDirList)
    strFName = Dir(strDirList(I) & "*.xls", vbNormal)
    While strFName <> ""
    Workbooks.Open (strDirList & strFName)
    strFName = Dir()
    Wend
    Next I
    End Sub
    </pre>

    Legare Coleman

  8. #8
    snax500
    Guest

    Re: Macro to Open all Files in Folder

    Legare,

    Your macro worked perfectly. The only thing was that the line:

    Workbooks.Open (strDirList & strFName)

    Should be:

    Workbooks.Open (strDirList(I) & strFName)

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •