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

 

[Joe4]

Welcome to the Board!

This line does not look right:

[COLOR=#333333]sSQL = "Select * from T count sheet output" 'This has to be the name of the query your report is using to display data[/COLOR]

It looks like you accidentally placed the name of your sheet from the line below in place of a query name, as that is not a valid query name reference.
Is your Query name really "T count sheet output"? If so, I think you will need to enclose it in square brackets, i.e.:

[COLOR=#333333]sSQL = "Select * from [T count sheet output]"[/COLOR]

And note that you should really use spaces in the name of objects, variables, or fields. Most programmers will use underscores in place of spaces.

 

[steve_sr2]

Okay thanks for the feedback on spaces & underscores. I'm fairly new to access programming.

sSQL = "Select * from T count sheet output" refers to a table that I'm trying to pull the data from. Should it be in brackets?

 

[Joe4]

Yes, if there are spaces in the names of your table/queries, I believe you will need to enclose all references to them in square brackets, like I showed you (doesn't matter if it is a table or query, the same rule applies).

If you didn't have spaces in the name, you wouldn't have to worry about that.

 

[steve_sr2]

I went back thru my db and removed all spaces. Reset my queries/forms, etc. I still receive the same msg when trying to run the code?

 

[Joe4]

That is very odd, because I don't even see any reference to "mycodename" in your code.

 

[steve_sr2]

Sorry... the exact message is

"The object doesn't contain the Automation object 'CreateExcelInfo.'

 

[Joe4]

Did you select the two library references that it mentions in the comments section of the code? You need to do that in order for the code to work.

[COLOR=#333333]'Set reference to Microsoft Excel Object library[/COLOR]
[COLOR=#333333]'Set reference to Microsoft ActiveX DataObject 2.x[/COLOR]

 

[steve_sr2]

Yes, I selected both.

 

[Joe4]

I set-up a database to test this out, and it worked for me.
Can you post the current version of your code, after the edits you made?