Object Doesn't Support Property or Method...

[r24igh]

Hi All,

New to VBA and managed to get this far with some code...

 Option Compare Database
Public Sub RunExcelMacro()
 Dim wb1 As Object
 Set wb1 = CreateObject("Excel.Application")
 With wb1
    .Workbooks.Open ("XLS name")
    .Visible = True
    .DoCmd.RunMacro (refreshallconnections)
    .activeworkbook.Save
    .activeworkbook.Close (True)
 End With
 Set wb1 = Nothing
 End Sub

It used to work perfectly, open the xls and then run the macro inside the xls, save it and close it however I now get this error.

Can anybody help?

 

[MarkK]

DoCmd is not a member of an Excel.Application object.

Also, you don't need parenthesis around your parameters...

 With wb1
    .Workbooks.Open "XLS name"
    .Visible = True
[COLOR="Green"]'    .DoCmd.RunMacro refreshallconnections[/COLOR]
    .activeworkbook.Save
    .activeworkbook.Close True
 End With

 

[r24igh]

Thanks Mark, much appreciated.

The DoCmd.RunMacro refers to a macro I have in the xls worksheet to refresh connections and save, then close the xls... In any case, should I be doing something else to make it work?