0

I have a workbook named Run All Weekly Reports.xlsm where I list reports that I update each Monday. The workbook/report names to be updated are in column A, the workbook paths in column B and the macro names in column C.

The macro (that I found somewhere online) works perfectly, looping through all of the files and refreshing the data, but I have recently added some files that refresh the data and then call another macro (within the other workbook) to email the workbooks to my colleagues. Once it sends the email, this macro stops and will not continue looping through the rest of the other workbooks. I think it has something to do with Setting the object back to Excel. I have Google searched and tried Set xlApp = CreateObject("Excel.Application"), but it will not continue the loop.

Any help would be greatly appreciated. Here is my macro:

Sub Run()
    'PURPOSE: To loop through all Excel files listed in Worksheet and run macro listed in column C

    Dim wb As Workbook
    Dim myPath As String

    Dim fn As String
    Dim MacroName As String
    Dim x As Integer

    NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
    For x = 1 To NumRows

        Workbooks("Run All Weekly Reports.xlsm").Sheets("List").Activate
        ActiveCell.Offset(1, 0).Select

        fn = ActiveCell.Offset(0, 0).Value
        myPath = ActiveCell.Offset(0, 1).Value
        MacroName = ActiveCell.Offset(0, 2).Value
        ActiveCell.Offset(0, 3) = "Done"

        If myPath = "" Then GoTo ResetSettings
        Set wb = Workbooks.Open(Filename:=myPath & fn)
        Application.Run "'" & fn & "'!" & MacroName

        wb.Close SaveChanges:=True
        Workbooks("RUN ALL WEEKLY REPORTS.xlsm").Save

        'Ensure Workbook has closed before moving on to next line of code
        DoEvents

ResetSettings:
        'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    Next
    MsgBox ("Finished")
End Sub

My macro experience is limited to a bit of trial and error, so please excuse my crude descriptions.

The macro in my original message is one that I copied from somewhere online and just modified it a little to suit.

I copied it into my macro enabled workbook and so to launch it, I have to select the macro called 'Run' from my macro list and 'Run' it.

I think that means that it is a public sub?
I have a list of workbooks in a file eg. Backorder Reports that it opens and Refreshes the data from an ODBC query, then calls the email to send to my colleague, but after the Sub SEND_Mail_Outlook_With_Signature_Html(), the loop macro just stops.

If I don't call the SEND macro, it loops through to the next file listed in my workbook without a problem.
I hope that I am making sense.

Below is and example of the macro's that are in each of my sheets that it should open and execute.

Sub Refresh()

    ' Refreshes the data and the dates in the pivot tables

    ActiveWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    ActiveWorkbook.Save
    Call SEND_Mail_Outlook_With_Signature_Html
    ActiveWorkbook.Close

End Sub

Sub SEND_Mail_Outlook_With_Signature_Html()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    StrBody = "Today's report attached."

    On Error Resume Next

    With OutMail
        .Display
        .To = "mycolleague@live.com.au"
        .CC =
        .BCC =
        .Subject = "Backorder Report"
        .HTMLBody = StrBody & "<br>" & .HTMLBody
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Mesut Akcan
  • 899
  • 7
  • 19
SuziRocks
  • 3
  • 2
  • What is the value of `fn` and the value of `MacroName` before you execute the `Run`? Is that sub public? What does that sub look like? You may want to include it in your question. Also, place a `Stop` inside of that routine just to make sure it's actually running. There is nowhere near enough information in your question to give you any kind of definitive answer. – braX Feb 07 '20 at 22:25
  • I am sorry, I am a bit of a hack with Macros'. – SuziRocks Feb 07 '20 at 23:05
  • It is a public sub. – SuziRocks Feb 07 '20 at 23:05
  • I am just running out the door, but I will see if I can answer this when I get back. Thanks for replying so quickly though :) – SuziRocks Feb 07 '20 at 23:07
  • I added additional info into my post. Thank you for your help. – SuziRocks Feb 08 '20 at 21:47
  • Remove the `On Error Resume Next` as it's just hiding whatever error you are getting. What error does it present to you on on which line? You also may want to look into how to get the running instance of Outlook if it is already running, and only use `CreateObject` when it isnt already running. – braX Feb 08 '20 at 22:32
  • I removed it but it will only open the first workbook, refresh it and email it, then it stops. No error message at all. – SuziRocks Feb 09 '20 at 00:59
  • SO it doesnt close the workbook? That's supposed to happen after it returns from the sub, right? Try putting a `Stop` after it calls the macro so you can step through it with F8 after it sends the email. – braX Feb 09 '20 at 01:18
  • Also, it looks like you are closing it, and then on the next line, you are trying to save it after it's closed, which seems odd to me. – braX Feb 09 '20 at 01:19
  • If I step through, it works without a problem... Weird. It closes the file it has opened and emailed and then opens the next file. – SuziRocks Feb 09 '20 at 01:37
  • Well, as my last suggestion, I would look into adding a delay where you think the problem is... it is a last resort, and will require some tinkering... but here's a link that shows how to do that - https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba – braX Feb 09 '20 at 03:20
  • 1
    I tried tinkering with delays without success, so I ended up getting someone at work to look at it. It was that I was closing the file in the second workbook and then this workbook was also trying to close it. Like you had said earlier. I had modified my macros that many times that I was totally confused. Thank you for your help, I greatly appreciate it. – SuziRocks Feb 13 '20 at 22:51

1 Answers1

0

It looks like you are trying to close the workbook after it is already closed. Use Stop in strategic locations to debug your code. Obviously you cannot close it again if it is already closed.

braX
  • 11,506
  • 5
  • 20
  • 33