Executing Excel macro on/from specific open file

Question

I've got a need to open some Excel files and "pause" then close them. In this process I run one macro on opening, and another on closing. The opening one works fine because it is done as each file is opened. But the closing part of the code I can't get it to run the correct macro. They have the same names, but the file contests are different, and what the macro does per file is different.

This is the gist of what I'm doing now

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

path = "\\Gaalpa1cdfile19\north_sa_staff\Reports\Rpt-ProductionCurves\"

filename2018P1 = "2018 P1.xlsm"
Set xlbook2018P1 = xlApp.WorkBooks.Open(path & filename2018P1)
' Run Macro
xlApp.Run "AutoRefresh"

filename2018P3 = "P3 2018 HRR.xlsm"
Set xlbook2018P3 = xlApp.WorkBooks.Open(path & filename2018P3)
'Run Macro
xlApp.Run "AutoRefresh"

'My "pause"
WScript.Echo ("All Files were" & Chr(013) & _
              "opened and refreshed, update ppt before OK" & Chr(013) & _
              " DO NOT CLICK OK" & Chr(013))

'==========================
'Below is the trouble spot.
'==========================
xlapp.Run "'" & filename2018P1 & "'" & "!AutoPublish"
xlbook2018P1.Close False
Set xlbook2018P1 = Nothing

xlapp.run "'" & filename2018P3 & "'" & "!AutoPublish"
xlbook2018P3.Close False
Set xlbook2018P3 = Nothing

The first part works fine, but trying to run the file's respective AutoPublish macro does not. The code works fine if I leave out that Run line. (The real file names have spaces and I had to add the single quotes to get it to accept the filename.)

What it appears to be doing is using the macros from the last file opened, not the one it's directed to use it the run line. I think I need a way to "select" the correct file, or give it focus so the macro could run without an explicit filename argument, which it appears to be ignoring anyway.

EDIT:

Solution was:

xlbook2018P1.Activate ' This fixed it, I think
xlapp.Run "'" & filename2018P1 & "'" & "!AutoPublish"
xlbook2018P1.Close False
Set xlbook2018P1 = Nothing

xlbook2018P3.Activate
xlapp.run "'" & filename2018P3 & "'" & "!AutoPublish"
xlbook2018P3.Close False
Set xlbook2018P3 = Nothing

I think you need to show us the AutoPublish macro. Is it possible it's operating on ActiveWorkbook and not ThisWorkbook ? — Tim Williams
– Tim Williams, Commented Jun 7, 2018 at 0:52
@Tim Williams Thank you. I believe this was the problem. I did have some ActiveWorkbook references in that macro as well as some variable assignments from range names that were not explicitly set to ThisWorkbook. I changed those but still had a VBA error with an explicit (valid) tab selection: ThisWorkbook.Sheets("SER").Select I took a stab at getting the called macro's workbook focus with: ThisWorkbook.Activate Right at the beginning of the code. That seems to have fixed it. If you post your reply as an answer I'll happily accept it. Thanks for the help. — shorton
– shorton, Commented Jun 7, 2018 at 13:28

EarlyBird2 · Accepted Answer · 2018-06-07 09:30:42Z

1

When tackling similar tasks, I usually work around by implementing a master Excel file first, and call a sub in this master file via VBS. The advantage to me seems it is way easier to fullfill all tasks in the VBA of the master file rather than having to code all that in VBS.

Create a master file, e.g. "Master.xlsm", list all your files you need to open on a sheet named "Files" in column A, starting in row 1. Insert a module and place the following sub in this module:

Sub Main()

    Dim strPath As String
    Dim strFile As String
    Dim lRow As Long
    Dim i As Long
    Dim k As Integer
    Dim n As Long
    Dim wb(1 To 3) As Workbook
    Dim wbTest As Workbook

    Set wbMaster = ThisWorkbook
    strPath = "\\Gaalpa1cdfile19\north_sa_staff\Reports\Rpt-ProductionCurves\"

    'Check how many files you need to open
    With Sheets("Files")
        lRow = Sheets("Files").Range("A" & .Rows.Count).End(xlUp).Row
    End With

    'open all available files
    For i = 1 To lRow
        Workbooks.Open (wbMaster.Sheets("Files").Range("A" & i).Value)
    Next

    'now run the two macros in each open file
    For k = 2 To Workbooks.Count 'this will work only if your master file is the only one open when starting the sub!
        Workbooks(k).Run "'" & Workbooks(k).Name & "'!AutoRefresh"
        DoEvents
        Workbooks(k).Run "'" & Workbooks(k).Name & "'!AutoPublish"
        DoEvents
    Next

    'and close all files previously opened except for the master file
    For n = Workbooks.Count To 2 Step -1
        Workbooks(n).Close False
    Next

End Sub

answered Jun 7, 2018 at 9:30

EarlyBird2

3063 silver badges14 bronze badges

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

2 Comments

shorton Over a year ago

EarlyBird2, thanks for this suggestion and your method.

EarlyBird2 Over a year ago

No problem, @shorton.

Tim Williams · Accepted Answer · 2018-06-07 15:01:32Z

0

It seems like a possible explanation for what you're seeing is that your AutoPublish macro refers to ActiveWorkbook and not the safer ThisWorkbook. If another workbook is active when it's called that could lead to unexpected results.

answered Jun 7, 2018 at 15:01

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

shorton Over a year ago

Well, my ThisWorkbook.Activate in the macro didn't work after all. But that lead me to try the solution posted in the original Q which did work.

Collectives™ on Stack Overflow

Executing Excel macro on/from specific open file

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related