473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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 'CreateExcelInfo'.

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateExcelInfo()
  2. 'Set reference to Microsoft Excel Object library
  3. 'Set reference to Microsoft ActiveX DataObject 2.x
  4. Const sFileNameTemplate As String = "C:\download\count template.xlsx"
  5. Dim oExcel As New Excel.Application
  6. Dim WB As New Excel.Workbook
  7. Dim WS As Excel.Worksheet
  8. Dim rng As Excel.Range
  9. Dim objConn As New ADODB.Connection
  10. Dim objRs As New ADODB.Recordset
  11. Dim vData As Variant
  12. Dim sSQL As String
  13. Set objConn = CurrentProject.Connection
  14. sSQL = "Select * from T count sheet output" 'This has to be the name of the query your report is using to display data
  15. With objRs
  16. .Open sSQL, objConn, adOpenStatic, adLockReadOnly
  17. vData = .GetRows()
  18. .Close
  19. End With
  20. With oExcel
  21. .Visible = True
  22. 'Create new workbook from the template file
  23. Set WB = .Workbooks.Add(sFileNameTemplate)
  24. With WB
  25. Set WS = WB.Worksheets("T count sheet output") 'Replace with the name of actual sheet
  26. With WS
  27. Set rng = .Range("A2") 'Starting point of the data range
  28. rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData)
  29. End With
  30.  
  31. End With
  32.  
  33. .Quit
  34. End With
  35.  
  36. 'clean up
  37. Set oExcel = Nothing
  38. Set objRs = Nothing
  39. Set objConn = Nothing
  40. Set vData = Nothing
  41. End Sub
Apr 29 '16 #1
1 1786
zmbd
5,501 Expert Mod 4TB
+ Open VBA editor <alt><F11>

++ Have you done the Debug>Compile

++ You are using "early binding," Do you have the Microsoft Excel 15.0 Object Library reference set (14.0 in Access 2010)

Tools>References
In the popup find the above listed reference.
Check to see if this is Selected (it will be in the top part of the listing if so, if not then scroll down to find it, alphabetical listing).
[OK]

Now select your Sub()
[F8] to "step into" the code,
slowly [F8] to step thru each line
Take a note where the error if any occurs
Once you have that line let us know which one - I suspect line 5. I personally dislike that construct, preferring instead an explicit "set" statement.

let us know how you've progressed... from there we can better assist.
Apr 29 '16 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Gigi | last post by:
Hi, I need access to a function object that corresponds to a frame object in a certain case from inside the function. I can get the frame object using: f = sys._getframe(0) But the...
6
by: Martin | last post by:
I'd like to be able to get the name of an object instance from within a call to a method of that same object. Is this at all possible? The example below works by passing in the name of the object...
10
by: Steven Blair | last post by:
Hi, Quick overview of the problem: public bool Something( out DataSet ds ) { bool ret=false; try {
0
by: a | last post by:
I have a custom object that inherits from CollectionBase and it does not successfully bind to GridViews or DropdownLists. My understanding is that that is because GridViews and Dropdownlists...
13
by: Fredrik Strandberg | last post by:
Hi! I receive an object as a System.Object argument to a method. I need to check if the object is a Type object or not (that is, not a specific type, but if the object is a type object in...
1
by: acog1 | last post by:
Hi everyone, I know that there is a question similar to this already posted but the answer to it does not really seem to relate to my problem! So here goes... I have a main form which has a tab...
10
by: Jess | last post by:
Hello, If I create a temporary object using a dynamically created object's pointer, then when the temporary object is destroyed, will the dynamically created object be destroyed too? My guess...
3
by: callre | last post by:
when i used javascript onchange() the error is coming "object doesnt support this property" my code is- <script type='text/javascript' language="javascript"> function change() { ...
3
by: =?Utf-8?B?S2F5xLFoYW4=?= | last post by:
In my project,i added datagridview to my form , i transfered my table to datagridview and added multiple rows and when i called dataadapther.update ,,result is ok. But when i tried it for the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.