VBA - 'Sorry, we couldn't find'

[stuartmacdonald]

I need to run a macro on a workbook that cannot be saved as a macro-enabled workbook, so I have set up a second workbook (Refresh.xlsm) and added the code below to open the workbook if want to run the macro in (Project check.xlsx) and then run a macro from Refresh.xlsm.

This code opens the workbook, but fails to run the macro. I get an error message 'Sorry, we couldn't find Refresh.xlsm'. Is it possible it was moved, renamed or deleted?'

Given both files are in the same folder, and the first one opens fine, something must be amiss, any ideas?

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

 

[cooper645]

Not sure about the error but;

Can you not store the macro in the PERSONAL.XLSB workbook and then even create your own ribbon?
Seems far easier than opening multiple workbooks.

 

[shg]

How about ...

Application.Run "Refresh.xlsm'!Refresh"

 

[stuartmacdonald]

Same error message

 

[shg]

Please confirm:

o The procedure doesn't take any arguments

o It's in a standard code module (not a sheet module or ThisWorkbook)

 

[stuartmacdonald]

Dear shg


Im not sure what you mean by the procedure doesn't take any arguments

This macro is in module 1

[/COLOR]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=#333333]

While the Refresh macro is in module 3

[/COLOR]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=#333333]

 

[stuartmacdonald]

I need these macros to be used by anyone using the workbook, so dont think storing in the PERSONAL.XLSB workbook is the way I need to go...

 

[BarryL]

if Refresh.xlsm is already open it should be

Sub openBk()


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




End Sub

 

[stuartmacdonald]

There was an erroneous ' in my code referencing the location... took it out, and fixed... but its still not working, but different issue now...

https://www.mrexcel.com/forum/excel...a-run-macro-another-workbook.html#post5180041