Open/RunMacro/Save&Close Macro

I am using the following code to open a group of workbooks and run a macro inside and then save and close the workbook:

Sub UpdateAll()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "K:\Location\"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xlsm")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         
Application.Run "'" & filename & "'!UpdateData"
        
   wb.Close SaveChanges:=True
    filename = Dir
 Loop
  Application.ScreenUpdating = True
End Sub

I added the below to the end of each individual macro within each workbook and now UpdateAll() won't go through each workbook in the folder and run the workbooks, it stops after opening one:

    
Dim Output As Workbook
    Dim Current As String
    Dim FileName As String

    Set Output = ThisWorkbook
    Current = ThisWorkbook.FullName

    Application.DisplayAlerts = False

    Dim SH As Worksheet
    For Each SH In Output.Worksheets

        SH.UsedRange.Copy
        SH.UsedRange.PasteSpecial xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Next

    FileName = ThisWorkbook.Path & "S:\Location\" & "165.xlsx"
      Output.SaveAs FileName:="S:\Location\165.xlsx", _
                FileFormat:=xlOpenXMLWorkbook
    Workbooks.Open Current
    Output.Close
    Application.DisplayAlerts = True


End Sub

Hi,

The line in red below is probably the problem. The macro closes the file that is running the code. This terminates the Macro 'daisy chain' and causes all Macro activity to stop.

The solution is to have the father routine close the son file, like you do in the top macro.

Dim Output As Workbook
    Dim Current As String
    Dim FileName As String

    Set Output = ThisWorkbook
    Current = ThisWorkbook.FullName

    Application.DisplayAlerts = False

    Dim SH As Worksheet
    For Each SH In Output.Worksheets

        SH.UsedRange.Copy
        SH.UsedRange.PasteSpecial xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Next

    FileName = ThisWorkbook.Path & "S:\Location\" & "165.xlsx"
      Output.SaveAs FileName:="S:\Location\165.xlsx", _
                FileFormat:=xlOpenXMLWorkbook
    Workbooks.Open Current
    Output.Close
    Application.DisplayAlerts = True
End Sub

Lewis

Thanks Lewis, I commented that out and it turns over to open the file well.
If I comment out:

Workbooks.Open Current

That will close the current book and still go onto the next one in the folder, correct?

If I comment out 'Workbooks.Open Current' that will close the current book and still go onto the next one in the folder, correct?

Unfortunately, No. Workbooks.Open does not close files. All the previous workbooks you had open will still be open. The code has to be structured in such a way that closing a file does not break the daisy chain.

See the following thread which discusses your problem: http://www.excelforum.com/excel-prog...le-macros.html

There are download file associated with post #16 that you might want to try. Norie has a solution (have all the macros in one master workbook) that works in post #17, but Norie's solution is not always possible.

I hope this helps. It is a complicated situation. Please ask if you are still having questions and/or problems.

Lewis

Thanks Lewis!