Access error: The object doesn't contain the Automation object....

[steve_sr2]

I'm running a macro that calls 2 queries. After the 2nd query, I'm trying to run code that copies table created from the 2nd query to an excel template. I keep receiving the msg "The object doesn't contain the Automation object "mycodename".

I've tried to research why I'm getting this but haven't found anything useful

Here's the code I'm using which I copied from somewhere else

Public Sub CreateExcelInfo()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "C:\download\count template.xlsx"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from T count sheet output" 'This has to be the name of the query your report is using to display data
With objRs
.Open sSQL, objConn, adOpenStatic, adLockReadOnly
vData = .GetRows()
.Close
End With
With oExcel
.Visible = True
'Create new workbook from the template file
Set WB = .Workbooks.Add(sFileNameTemplate)
With WB
Set WS = WB.Worksheets("T count sheet output") 'Replace with the name of actual sheet
With WS
Set rng = .Range("A2") 'Starting point of the data range
rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
End With

End With

.Quit
End With

'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

 

[steve_sr2]

Public Sub CreateExcelInfo()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "C:\download\count_template.xlsx"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from [T_CountSheetOutput]" 'This has to be the name of the table your report is using to display data
With objRs
.Open sSQL, objConn, adOpenStatic, adLockReadOnly
vData = .GetRows()
.Close
End With
With oExcel
.Visible = True
'Create new workbook from the template file
Set WB = .Workbooks.Add(sFileNameTemplate)
With WB
Set WS = WB.Worksheets("Count_Sheet") 'Replace with the name of actual sheet
With WS
Set rng = .Range("A2") 'Starting point of the data range
rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
End With

End With

.Quit
End With

'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

 

[steve_sr2]

If it matters, I'm using Office365

 

[Joe4]

If it matters, I'm using Office365

That may be a possibility. I have never used it, so I cannot say for sure.

There are just three things you should check/confirm:
1. Do you have an existing template file out there named: "C:\download\count_template.xlsx"?
2. Does this existing template file have a sheet named: "Count_Sheet"?
3. Do you have a Table or Query in Access with the exact name "T_CountSheetOutput"?

Those would be the three most likely culprits to cause issues (along with not having all the correct references selected, but you already confirmed that).

Two other things to check:
1. In the VB Editor, if you go to the "Debug" menu, and click on the first option that starts "Compile...", does that return any errors? That should highlight any syntax errors.
2. How exactly is this procedure being run/called?

 

[steve_sr2]

Verified options 1-3.

Next #1 When I compile, it doesn't return any errors.
#2 I'm calling the code from a 'RunCode' action in a macro, with Function Name = CreateExcelInfo

 

[Joe4]

Eureka! We found the problem!

You cannot use RunCode to run Sub Procedures, you use them to run Functions.
Note that if you create a new macro and add the RunCode Action, the note it gives you:

Runs a Visual Basic Function procedure. To run a Sub procedure or event procedure, create a Function procedure that calls the Sub procedure or event procedure. Press F1 for help on this action.

So, first make sure that your sub procedure is in a Standard Module (and not under one of the Object modules).
Then, right after that code, create a simple one line function that call it, like this:

Public Function RunCreateExcelInfo()
    Call CreateExcelInfo
End Function

Then in your RunCode Macro action, enter "RunCreateExcelInfo()" in for the function name.
(Note: if you have done this correctly, when you try to browse your current database for the available functions, you should see this one from the list).

 

[steve_sr2]

Thanks for all your help. That worked perfectly.

 

[Joe4]

You are welcome.