VBA Open File/Run Code/Close & Save/Open Next File?

[EXCEL-LENCY]

Hello,

I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

Thanks,
Jason

 

[pboltonchina]

This will loop through all files in a folder. Change the necessary path and macro name to suit your needs.

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "C:\SAP Imports\Sales Orders\" 'change to suit
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xls")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         
        'Call a subroutine here to operate on the just-opened workbook
        Call Mymacro
        
        
        filename = Dir
    Loop
  Application.ScreenUpdating = True
End Sub

 

[EXCEL-LENCY]

Wow! That works great. Thank you so much... I can't tell you how much time that this will save me.

 

[EXCEL-LENCY]

I haven't tried this on the set of files I will be using it for yet, but wanted to make sure the "Update Links" prompt would not affect this? I'd like to update links in each file when they open... would Application.DisplayAlerts = False work for links that cannot be updated? Some of the files have links that are are no longer valid.

In other words, can the code "Update Links" and, if required, "Continue" if links cannot be updated?

Thanks!

 

[pboltonchina]

Whatever macro you are running on a single file should work exactly the same when called from this code.

 

[EXCEL-LENCY]

Thanks again... one additional concern... is it possible to open the files in the alphabetical order they are in in the folder? It seems to open them at random.

Thanks,
Jason

 

[pboltonchina]

I've never bothered about the order it processes the files, It runs and when it's finished all the files are done. What difference does it make?

 

[EXCEL-LENCY]

Because I will be running a macro that freezes important financial information in several hundred files and I would need to quickly know how far the macro has gotten to if something were to happen. What order does it open it in? It seems to be some sort of order because it opens the same file first... I thought maybe the file size but that is not the case. I'd really like for it do open in alphabetical order. Is that possible?

I appreciate your help.

Jason

 

[pboltonchina]

I don't know what determines the order or how to pre-determine it, sorry.

 

[icomefromchaos]

I've adopted the code that you posed @pboltonchina, however it only loops through 1 document and then closes.

Sub AllFiles()    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "C:\Users\USER\Desktop\OCCREPORTS\Files\" 'change to suit
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xls")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
        Combine
        filename = Dir
    Loop
  Application.ScreenUpdating = True
End Sub
Sub Combine()
    Dim J As Integer
    Dim s As Worksheet
    Dim LastCol As Integer
    
       
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"




For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.Goto Sheets(s.Name).[A1]
            Selection.CurrentRegion.Select
            Sheet.UsedRange.Clear
            LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column
            Selection.Copy Destination:=Sheets("Combined"). _
            Cells(1, LastCol + 1)
        End If
    Next
    ActiveWorkbook.Save
End Sub