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

EXCEL-LENCY

Board Regular
Joined
Feb 9, 2010
Messages
75
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
This will loop through all files in a folder. Change the necessary path and macro name to suit your needs.

Code:
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
 
Upvote 0
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!
 
Upvote 0
Whatever macro you are running on a single file should work exactly the same when called from this code.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
I've adopted the code that you posed @pboltonchina, however it only loops through 1 document and then closes.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top