VBA to run macro in another workbook

[stuartmacdonald]

I'm trying to use VBA to open another workbook, run a macro in it and then close the workbook. Should be simple.

This workbook where I want to run the macro cannot be a macro-enabled workbook because I'm using it with PowerApps and will also be used by other people, so I need to store the macro in a separate workbook.

So far I have the following code:

I open Refresh.xlsm and run this code to open Project check.xlsx and then run the macro

[/COLOR][COLOR=#333333]Sub openBk()

Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Project check.xlsx"
Application.Run "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Refresh.xlsm!Refresh"


End Sub[/COLOR]
[COLOR=#333333]

Macro

[/COLOR][COLOR=#333333]Sub Refresh()'
' Refresh Macro
'


'
Windows("Project check.xlsx").Activate
Cells.Select
Range("CLES_project_check[[#Headers],[Contract_No&Desc]]").Activate
Selection.ClearContents
ActiveWorkbook.Connections("Query - CLES_project_check").Refresh
With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
Connections("Query - CLES_project_check"), Destination:=Range("$A$8")). _
TableObject
.RowNumbers = False
.PreserveFormatting = True
.RefreshStyle = 1
.AdjustColumnWidth = True
.ListObject.DisplayName = "CLES_project_check"
.Refresh
End With
ActiveWorkbook.Save
ActiveWindow.Close
End Sub[/COLOR]
[COLOR=#333333]

It opens the workbook fine, but then will not run the macro. Excel returns an error 'Cannot run the macro... the macro may not be available in this workbook or all macros may be disabled'

I have checked the macro security settings in Project check.xlsx and they are set to 'enable all' and trust access to VBA....

Any ideas why it won't run?

 

[Kamolga]

Sub Refresh()'' Refresh Macro
Windows("Project check.xlsx").Activate
Cells.Select

Maybe because you try to run it on .xlsx and file might need to be macro-enabled .xlsm

 

[stuartmacdonald]

Dont think that could be the reason as when I have the two workbooks open, I can run the macro from Refresh.xlsm no problem and Project Check.xlsx is updated. I just want the macro to automate it for me...

 

[BarryL]

now youve take the apostrophe out, it should be

application.run "Refresh.xlsm!Refresh"

If the file is open and you provide the full link it won't work.

 

[rlv01]

Since according to your description, both listed macros (openBk & Refresh) are in the same Refresh.xlsm workbook, what happens if you dispense with the application.run statement and instead call Refresh directly?

Sub openBk()

Workbooks.Open "C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Project check.xlsx"
Refresh

End Sub

 

[Kamolga]

I had same kind of isssue and my dirty workaround had been to have the "refresh" macro launched on an event, a hidden sheet that I would activate, which would call the macro and then hide the sheet again