Nested macros not looping

Question

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

A.S.H · Accepted Answer · 2017-03-16 18:43:22Z

1

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

edited Mar 16, 2017 at 18:43

answered Mar 16, 2017 at 17:30

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Scott H Over a year ago

Moving Loop under "MyFiles" creates a compile error and dosen't run anything.

A.S.H Over a year ago

There must be an error somewhere else. Check the file names and paths again plz.

A.S.H Over a year ago

Did you test my code as is? Did you replace "ME" by your actual home folder?

Scott H Over a year ago

The edited version above works PERFECTLY. tested several times. phew! This test was for thee XLSM to update to 5 XLSX. Now to make it for real... 7 XLSM updating to 12 XLSX files....tomorrow.

Scott H Over a year ago

complete test is running on the shared drive. 7 xlsm workbooks find and open 12 XLSX workbooks and push data to them based on tab names.

|

Collectives™ on Stack Overflow

Nested macros not looping

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related