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

 

[JimL243]

Thanks everyone - this post helped get me started and I have hit a roadblock - the open new folder works but it does not execute the new routine on the newly opened workbook.

Can someone take a look and let me know where I went wrong?

Ultimately I am trying to follow these steps:

1. Save existing workbook as new workbook with current date
2. Open new current date workbook and remove formulas and paste values. The current formulas are UNC and cannot be opened by MAC Excel, just want the data.
3. Save the newly normalized workbook
4. Email new workbook to set address. (Have not figured out this code yet)

Here is my code:

Sub SaveNewReportandClean()


    'Unprotect Sheet
    ActiveSheet.Unprotect


    'Save current open and now unprotected sheet to new workbook with time & date in filename
    ActiveWorkbook.SaveAs Filename:="S:\Communications_Bills\2015_Forecasts\Presentation Files\2015 Opex " & Format(Now(), "yyyy-mm-dd"), _
    FileFormat:=51, CreateBackup:=False
    
    ActiveSheet.Protect
    
    'Application.ScreenUpdating = False
    'Open newly saved workbook
    
    Dim wb As Workbook
    
    Set wb = Workbooks.Open("S:\Communications_Bills\2015_Forecasts\Presentation Files\2015 Opex " & Format(Now(), "yyyy-mm-dd") & ".xlsx")
    
    'Call a subroutine here to operate on the just-opened workbook


    ActiveSheet.Unprotect
    
    Dim wSheet As Worksheet
    
     
    For Each wSheet In Worksheets
        With wSheet.UsedRange
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next wSheet
     
    Application.CutCopyMode = False
    
    ActiveSheet.Protect




End Sub

 

[UMAKEMESIK]

Hello,

I have a word file with Visual basic code running on the sheet.

I need to change the code on some 300 documents.

The change is the same for all documents. Can I run something similar for my word files
to loop through each document, open it , change the code, save and close?

thanks for your time.

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

 

[Drewgarry007]

Hi,

I have also tried to adopt this code and its very slow and I get the message "there is a large amount of information on the clipboard. do you want to paste this information into another program later?" every time. The data in the sheets "wSheet" are only small, number of rows vary between 10-25 on average cols are "A:K" this isnt a large amount of info to cut and paste is it? Can you see anywhere the below code can be changed?

Sub TradeCount()

Dim folderPath As String
Dim filename As String
Dim wb As Workbook


Application.ScreenUpdating = False
    
    folderPath = "Z:\Middle Office\OMS Trade Count\"
        
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xls")
    Do While filename <> ""
      
    Set wb = Workbooks.Open(folderPath & filename)
    Dim wSheet As Worksheet
         
    For Each wSheet In Worksheets
        With wSheet.UsedRange
            .Copy
            Windows("Trade Counter.xlsm").Activate
            Sheets("Portfolio Trading").Select
            Cells(1, 1).Select
            NextRow = Range("A1000").End(xlUp).Row + 2
            Range("A" & NextRow).Select
            ActiveSheet.Paste
            wb.Close
        End With
    Next wSheet
     
    Application.CutCopyMode = True
    filename = Dir
    Loop
  
End Sub




Sub test2()


Path = "Z:\Middle Office\OMS Trade Count\"
filename = Dir(Path & "*.xls")


Workbooks.Open filename:=Path & filename, ReadOnly:=True
ActiveWindow.ActivateNext




End Sub

 

[leroyvdlinden]

Hello all,

I'm very new to Excel macro's, but I would like to use this macro. If I create a new module in a empty excel file to start the process of running all excels in a folder I receive the following error while running the module.

Compile error:
Sub or Function not defined

Is someone able to help me with this issue.

Thanks in advance.

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

 

[valleyboy]

Hi,

I'm fairly new to coding, and was wondering if I could possibly have some help.

I have a workbook with several sheets, some are hidden - one of the locked hidden sheets needs to be updated with a new sheet, and saved. I have over 700 files that I need to this with.

Is there some code that I can use to do this for me?

Thanks in advance