CODEWITHSUNDEEP
vbaexcel
Scott H
Scott H

Reputation: 69

Nested macros not looping

I have a project that keeps growing and growing and growing. I'm at the last bit and it isn't looping. I'm not seeing why. I have the elements in their own macros and they perfectly until I try to combine them into a super macro that runs them all. The idea is that the "master" workbook (XLSM) will run its own update, THEN Open all other XLSM files in the same folder and run THEIR Updates which pushes data to other XLSX files. Right now each XLSM file runs a macro that works perfectly by Sub EndofDay () Call step 1 Call Step 2 End Sub I used the same "Do while" structure for the SuperMacro that opens the other books and calls their "EndofDay". it gets through a couple of the XLSM files then stops with an error on MyFiles = Dir. the following syntax SHOULD run the masters updates and one other code string, THEN open all other XLSM files in the Folder. Why does it suddenly not work at this level when It works on the level under this.

Sub SuperMacroEOD_Trans()

    Dim MyFiles As String
        Call EndofDayTransfer 'Do this Workbook Transfer first then:
'Step 2: Specify a target folder/directory.
    MyFiles = Dir("C:\Users\ME\Desktop\QA VBA Project\*.xlsm")

'Dont try to open this workbook and do anything.
    Do While MyFiles <> "" And MyFiles <> "C:\Users\ME\Desktop\QA VBA Project\Update_Master.xlsm"
'Step 3: Open Workbooks one by one
    Workbooks.Open "C:\Users\ME\Desktop\QA VBA Project\" & MyFiles

    Call EndofDayTransfer 'Call same macro you ran and run in the other workbooks (they contain it).

    ActiveWorkbook.Close SaveChanges:=True

'Step 4: Next File in the folder/Directory
      Loop
    MyFiles = Dir <------------Gets stuck here or editor turns it yellow.


End Sub

Upvotes: 2

Views: 77

Answers (1)

A.S.H
A.S.H

Reputation: 29332

There are a few logic mistakes in your code.

1- Here you're telling the code to stop when reached the master file, but what you actually want is to skip the master file. also you are comparing a simple filename to a fullpath name

Do While MyFiles <> "" And MyFiles <> "C:\Users\ME\Desktop\QA VBA Project\Update_Master.xlsm"

2- This should be put inside the loop, that is, before the line Loop

MyFiles = Dir <------------Gets stuck here or editor turns it yellow.

3- you are using ActiveWorkbook which is pretty hazardous. you should use explicit references. Moreover since all the workbooks have the procedure "EndofDayTransfer", including the master WB, it is mandatory to specify explicitly the "scope" so that the appropriate procedure is run (see Application.Run in the code below)

Sub SuperMacroEOD_Trans()
    Dim MyFiles As String, wb As Workbook
    Call EndofDayTransfer ' Master Workbook Transfer first

    MyFiles = Dir("C:\Users\ME\Desktop\QA VBA Project\*.xlsm")
    Do While MyFiles <> ""
        If MyFiles <> ThisWorkbook.Name Then ' skip the master
            ' Always get an explicit reference to any file you open and use it 
            Set wb = Workbooks.Open("C:\Users\ME\Desktop\QA VBA Project\" & MyFiles)
            Application.Run "'" & wb.name & "'!EndofDayTransfer" ' <-- specify scope explicitly to disambiguate
            wb.Close SaveChanges:=True
        End If

        MyFiles = Dir ' <-- inside the loop
    Loop
End Sub

Upvotes: 1

Related Questions