Calling Excel macro from Access errors

[Guest]

Hi

I'm calling an Excel macro from Access and get an error

440 Method 'Run' of object '_Application' failed

I've cut and pasted the offending piece of code into a separate procedure:

Sub Test()

ActiveWorkbook.SaveAs Filename:= _
"C:\Apps\Reward and Recognition\Quarterly reports\" _
& "Quarterly KPIs.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=False

End Sub

Help appreciated.

 

[NickHK]

How are you calling this from Access ?

NickHK

 

[Guest]

Dim objExcel As Excel.Application

Set objExcel = Excel.Application
With objExcel
..Visible = True
Workbooks.Open("C:\Apps\Reward and Recognition\" _
& "Quarterly reports\Template.xls").Activate

..Run "Test"

End With
---------------------------

Access opens Template.xls OK and runs other code in the Test macro but it
fails when it tries to save Template.xls under another name.