Runtime error 1004 - running macros on excel file

[Kobe2932]

Hello there

Seems like the original thread I opened was deleted when the database broke down a couple of days ago.

So, my problem is that I have 2 databases, and 1 excel file. The databases are identical in their function and structure - of course, macro names, references, variables have a different name, but the function they do is the same.

I have to copy information from both database to the common excel file. For this, I use the following macro which:
Runs and export results of a query to excel, opens up the report, insert a module and a macro which copies the information with a VLOOKUP.

My problem is that anytime I run the macro from the second database, I got runtime error 1004 message. It does not matter, which database do I pick first, the second will always run to this error.

It seems like, for some reason I'm unable to insert a second Module to the common excel file.

This is the code I'm using

Function Comment1()
       ' Start Excel
       Dim xlapp As Object 'Excel.Application
       Set xlapp = CreateObject("Excel.Application")
       ' Make it visible...
       xlapp.Visible = True
       ' Add a new workbook
       Dim destination2 As String
    
       destination2 = "C:\****\test.xls"
       
       Dim xlbook As Object 'Excel.Workbook
       Set xlbook = xlapp.Workbooks.Open(destination2)
       
       ' Add a module
       Dim xlmodule As Object 'VBComponent
       Set xlmodule = xlbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule
       
       'define code and add a macro to the module...
       
       Dim Code3 As String
        
        Code3 = "" (text of inserted macro comes here)
             
       xlmodule.CodeModule.AddFromString Code3

       ' Run the new macro!
       xlapp.Run "embeddedmacro" (name is defined in Code3 string)

       ' Remember to release module
       Set xlmodule = Nothing
       ' Clean up
       xlbook.Saved = True
       'xlapp.Quit
       
End Function

Running it twice it always fails at this line:

Set xlmodule = xlbook.VBProject.VBComponents.Add(1)

any ideas what should I update ?

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=229008&highlight=orphan

There is a possibility of what version Excel being used since it is at the module level.
There might be a better way to accomplish the same result.

However, the key to your question is that it runs once OK. It is the 2nd time that it failes. This indicates that there is an Orphan class object. It is funny how an open referenced object can sometimes be refered to once with out the proper object reference.
So, there are two things to eyeball closely.
1. Everywhere there is a Set - there should be an ending Set (object) = Nothing (e.g. destroy all object variables)
2. One line of code failed to have the object variable - it sometimes runs OK the first time. The second time the code runs - the error happens when that object is referenced - due to the Orphan left from the last time.

Here is a quick reference to review:
http://www.btabdevelopment.com/ts/default.aspx?PageId=47

Open Task Manager - step through your code. The reference to Excel should appear when line wiht Set xlApp is executed.
Likewise, as the Function ends - it should be destroyed from Task Manager.

Here is a very short example of destroying object variables

Sub CreateExcelObjects()
   Dim xlApp            As Excel.Application
   Dim wkbNewBook       As Excel.Workbook
   Dim wksSheet         As Excel.Worksheet
   Dim strBookName      As String
 
   ' Create new hidden instance of Excel.
   Set xlApp = New Excel.Application
   ' for troubleshooting uncomment this line
       ' xlApp.Visible = True      ' monitor the process and step through code line at a time
   ' Add new workbook to Workbooks collection.
   Set wkbNewBook = xlApp.Workbooks.Add
   ' Specify path to save workbook.
   strBookName = "c:\my documents\xlautomation.xls"
   ' Loop through each worksheet and append " - By Automation" to the
   ' name of each sheet. Close and save workbook to specified path.
   With wkbNewBook
      For Each wksSheet In .Worksheets ' note if this is just Worksheets (instead of .Worksheets) it might run the first time - but when rerunning the subroutine the 2nd time - it could fail with an errir 91 or other object.  It can be very tricky to troubleshoot.
         wksSheet.Name = wksSheet.Name & " - By Automation"
      Next wksSheet
      .Close SaveChanges:=True, FileName:=strBookName
   End With
 
   Set wkbNewBook = Nothing ' destroy variable
   XlApp.Quit
   Set xlApp = Nothing ' destroy variable
End Sub

 

[Kobe2932]

http://www.access-programmers.co.uk/forums/showthread.php?t=229008&highlight=orphan

There is a possibility of what version Excel being used since it is at the module level.
There might be a better way to accomplish the same result.

However, the key to your question is that it runs once OK. It is the 2nd time that it failes. This indicates that there is an Orphan class object. It is funny how an open referenced object can sometimes be refered to once with out the proper object reference.
So, there are two things to eyeball closely.
1. Everywhere there is a Set - there should be an ending Set (object) = Nothing (e.g. destroy all object variables)
2. One line of code failed to have the object variable - it sometimes runs OK the first time. The second time the code runs - the error happens when that object is referenced - due to the Orphan left from the last time.

Here is a quick reference to review:
http://www.btabdevelopment.com/ts/default.aspx?PageId=47

Open Task Manager - step through your code. The reference to Excel should appear when line wiht Set xlApp is executed.
Likewise, as the Function ends - it should be destroyed from Task Manager.

Here is a very short example of destroying object variables

Sub CreateExcelObjects()
   Dim xlApp            As Excel.Application
   Dim wkbNewBook       As Excel.Workbook
   Dim wksSheet         As Excel.Worksheet
   Dim strBookName      As String
 
   ' Create new hidden instance of Excel.
   Set xlApp = New Excel.Application
   ' for troubleshooting uncomment this line
       ' xlApp.Visible = True      ' monitor the process and step through code line at a time
   ' Add new workbook to Workbooks collection.
   Set wkbNewBook = xlApp.Workbooks.Add
   ' Specify path to save workbook.
   strBookName = "c:\my documents\xlautomation.xls"
   ' Loop through each worksheet and append " - By Automation" to the
   ' name of each sheet. Close and save workbook to specified path.
   With wkbNewBook
      For Each wksSheet In .Worksheets ' note if this is just Worksheets (instead of .Worksheets) it might run the first time - but when rerunning the subroutine the 2nd time - it could fail with an errir 91 or other object.  It can be very tricky to troubleshoot.
         wksSheet.Name = wksSheet.Name & " - By Automation"
      Next wksSheet
      .Close SaveChanges:=True, FileName:=strBookName
   End With
 
   Set wkbNewBook = Nothing ' destroy variable
   XlApp.Quit
   Set xlApp = Nothing ' destroy variable
End Sub

I think this is not the source of the problem.If you look at my code, it always stops at adding a new module, however the variable (xlmodule) is properly destroyed at the end of the code

Dim xlmodule As Object 'VBComponent
       Set xlmodule = xlbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule


Set xlmodule = Nothing

any other idea what can cause this ?