Announcement

Collapse
No announcement yet.

VBA Code To Open Multiple Excel Files

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code To Open Multiple Excel Files



    Hi, ......first time caller - long timer listener

    I have a folder with 20 .xlsb files. Each file has to be opened manually they each run an automatic macro which is pulling SQL data and then sending the results via email, and then the file automatically closes.

    So, I open the first file, wait for the process to complete and close (can take 10 mins), then open the second etc etc.

    I'm trying to automate this process, using the following code:

    Code:
    Const FOLDER As String = "C:\\test\"
     
    Sub ProcessEachFileInFolder()
     
    On Error GoTo ErrorHandler
     
    Dim fileName As String
     
      fileName = Dir(FOLDER, vbDirectory)
     
      ' loop through folder, only process .xls files
     Do While Len(fileName) > 0
        If Right$(fileName, 4) = "xlsb" Then
          Call ProcessFile(fileName)
        End If
        fileName = Dir
      Loop
     
    ProgramExit:
      Exit Sub
    ErrorHandler:
      MsgBox Err.Number & " - " & Err.Description
      Resume ProgramExit
    End Sub
     
    Sub ProcessFile(fileName As String)
     
    Dim currentWkbk As Excel.Workbook
     
      ' open workbook
     Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName)
     
    ' do whatever you need to do with currentWkbk
     
    End Sub
    but it only opens the first file, and nothing else.

    Can someone please help?

  • #2
    Re: open multiple files sequentially

    Try using this in case you have files with different capitalization.
    Code:
    If UCase(Right$(fileName, 4)) = "XLSB" Then
    Also when closing the currentWkbk, do you save it?
    Do you reference the currentWkbk cells correctly?

    Comment


    • #3
      Re: open multiple files sequentially

      Code:
      Const FOLDER As String = "C:\test\"
       
      Sub ProcessEachFileInFolder()
           
          On Error GoTo ErrorHandler
           
          Dim fileName As String
           
          fileName = FOLDER & "*.xlsb"
           
           ' loop through folder, only process .xls files
          Do While Len(fileName) > 0
              Call ProcessFile(fileName)
              fileName = Dir
          Loop
           
      ProgramExit:
          Exit Sub
      ErrorHandler:
          MsgBox Err.Number & " - " & Err.Description
          Resume ProgramExit
      End Sub

      Comment


      • #4
        Re: open multiple files sequentially

        Hi, this doesn't seem to work. Do I need to change something else as well?

        I get an 1004 error, where is tries to open an actual file called "*.xlsb" and can't find it.

        thanks

        Comment


        • #5
          Re: open multiple files sequentially

          Originally posted by dbrown14 View Post
          Try using this in case you have files with different capitalization.
          Code:
          If UCase(Right$(fileName, 4)) = "XLSB" Then
          Also when closing the currentWkbk, do you save it?
          Do you reference the currentWkbk cells correctly?
          it closes/saves automatically...
          not sure...

          basically, this is an old script/macro that i use from someone else. But... clicking the file in explorer - it opens and then closes the WB by itself.

          Comment


          • #6
            Re: VBA Code To Open Multiple Excel Files

            Sorry, forgot to add the first Dir().
            Code:
                fileName = Dir(FOLDER & "*.xlsb")

            Comment


            • #7
              Re: VBA Code To Open Multiple Excel Files

              thanks, that got the script running again, but the same old problem is there: opens and runs my first XLSB file - but not the rest.

              So - could the problem be with either 1) the way my other files open/close or 2) the fact that once open, my files are locking excel up for 10 mins while they go off and do a SQL query?

              Cheers.

              Comment


              • #8
                Re: VBA Code To Open Multiple Excel Files

                To see if it is getting the next file if your process is not to blame, comment it out and add a debug.print and view the Immediate Window.

                Code:
                Const FOLDER As String = "C:\test\" 
                 
                Sub ProcessEachFileInFolder() 
                     
                    On Error Goto ErrorHandler 
                     
                    Dim fileName As String 
                     
                    fileName = Dir(FOLDER & "*.xlsb") 
                     
                     ' loop through folder, only process .xlsb files
                    Do While Len(fileName) > 0 
                        'Call ProcessFile(fileName) 
                        Debug.print filename
                        fileName = Dir 
                    Loop 
                     
                ProgramExit: 
                    Exit Sub 
                ErrorHandler: 
                    MsgBox Err.Number & " - " & Err.Description 
                    Resume ProgramExit 
                End Sub

                Comment


                • #9
                  Re: VBA Code To Open Multiple Excel Files

                  wierd, it just rolls throught the macro without displaying anthing. I also added a ...Debug.Print "PROCESSING FILENAME"... but it doesn't popup with that either....

                  Comment


                  • #10
                    Re: VBA Code To Open Multiple Excel Files

                    If you are going to use Len(), you need to give it the fullname of the file to check the length.
                    Code:
                    Do While Len(Folder & fileName) > 0
                    Here is how I normally do it.
                    Code:
                    Sub DirFiles()
                        Dim FileName As String, FileSpec As String, FileFolder As String
                        Dim wb As Workbook
                        
                        FileFolder = ThisWorkbook.Path & "\"
                        FileSpec = FileFolder & "*.xlsm"
                        
                        FileName = Dir(FileSpec)
                        If FileName = "" Then Exit Sub
                        
                    '   Loop until no more matching files are found
                        Do While FileName <> ""
                            If IsWorkbookOpen(FileName) = False Then
                              MsgBox FileName
                              'Set wb = Workbooks.Add(FileFolder & FileName)
                              DoEvents
                              'wb.Close True
                            End If
                            FileName = Dir()
                        Loop
                    
                    End Sub
                    
                    
                    Function IsWorkbookOpen(stName As String) As Boolean
                        Dim Wkb As Workbook
                        On Error Resume Next ' In Case it isn't Open
                        Set Wkb = Workbooks(stName)
                        If Not Wkb Is Nothing Then IsWorkbookOpen = True
                        'Boolean Function assumed To be False unless Set To True
                    End Function

                    Comment


                    • #11
                      Re: VBA Code To Open Multiple Excel Files

                      thanks Kenneth. Using your example - how do I get it to open the files as it reads them? When I run the macro, it cycles through the files, giving their names in the popup - but they don't open.

                      Sorry for the stupid questions

                      Comment


                      • #12
                        Re: VBA Code To Open Multiple Excel Files

                        ooh I just uncommented the :

                        'Set wb = Workbooks.Add(FileFolder & FileName)

                        and it now runs through and opens them!!! Now it wants to save them though... hmm.. need to tweak a bit more - but looks like we might be getting there!!

                        Comment


                        • #13
                          Re: open multiple files sequentially

                          Code:
                          Sub test()
                            ChangeBaseHyperlinks "http://rrwebsvr/", "http://cottwebsvr/"
                          End Sub
                          
                          Sub ChangeBaseHyperlinks(oldBase As String, newBase As String)
                            Dim h As Hyperlink, sht As Worksheet
                            Dim Len1 As Integer
                            
                            Len1 = Len(oldBase)
                            
                            For Each sht In Worksheets
                              For Each h In sht.Hyperlinks
                                If oldBase = Left(h.Address, Len1) Then
                                  'h.Address = newBase & Right(h.Address, Len(h.Address) - Len1) 'memory overflow
                                  If oldBase = Left(h.Range.Value, Len1) Then
                                    h.Range.Value = newBase & Right(h.Address, Len(h.Address) - Len1)
                                  End If
                                  sht.Hyperlinks.Add Anchor:=h.Range, Address:= _
                                    newBase & Right(h.Address, Len(h.Address) - Len1)
                                End If
                              Next h
                            Next sht
                          End Sub
                          
                          Sub test2()
                            ChangeSheetBaseHyperlinks "http://rrwebsvr/", "http://cottwebsvr/"
                          End Sub
                          
                          Sub ChangeSheetBaseHyperlinks(oldBase As String, newBase As String)
                            Dim h As Hyperlink, sht As Worksheet
                            Dim Len1 As Integer
                            
                            Len1 = Len(oldBase)
                            Set sht = ActiveSheet
                            
                            For Each h In sht.Hyperlinks
                              If oldBase = Left(h.Address, Len1) Then
                                'h.Address = newBase & Right(h.Address, Len(h.Address) - Len1) 'memory overflow
                                If oldBase = Left(h.Range.Value, Len1) Then
                                  h.Range.Value = newBase & Right(h.Address, Len(h.Address) - Len1)
                                End If
                                sht.Hyperlinks.Add Anchor:=h.Range, Address:= _
                                  newBase & Right(h.Address, Len(h.Address) - Len1)
                              End If
                            Next h
                          
                          End Sub

                          Comment


                          • #14
                            Re: VBA Code To Open Multiple Excel Files

                            Hi... that code looks strange - did you mean to post if in my thread?

                            By the way, I'm still stuck - it keeps asking to save the open files to a new file name. What can I do to avoid that? Thanks!!

                            Comment


                            • #15


                              Re: open multiple files sequentially

                              Yes, not sure why that was posted here.

                              You need to Open the workbook, not Add.
                              Code:
                              Sub DirFiles()
                                  Dim FileName As String, FileSpec As String, FileFolder As String
                                  Dim wb As Workbook
                                   
                                  FileFolder = ThisWorkbook.Path & "\"
                                  FileSpec = FileFolder & "*.xlsm"
                                   
                                  FileName = Dir(FileSpec)
                                  If FileName = "" Then Exit Sub
                                   
                                   '   Loop until no more matching files are found
                                  Do While FileName <> ""
                                      If IsWorkbookOpen(FileName) = False Then
                                          'MsgBox FileName
                                          Set wb = Workbooks.Open(FileFolder & FileName)
                                          'wb.Saved = True
                                          DoEvents
                                          wb.Close True
                                      End If
                                      FileName = Dir()
                                  Loop
                                   
                              End Sub
                               
                               
                              Function IsWorkbookOpen(stName As String) As Boolean
                                  Dim Wkb As Workbook
                                  On Error Resume Next ' In Case it isn't Open
                                  Set Wkb = Workbooks(stName)
                                  If Not Wkb Is Nothing Then IsWorkbookOpen = True
                                   'Boolean Function assumed To be False unless Set To True
                              End Function

                              Comment

                              Working...
                              X