Intermittent 429 ActiveX Component Error - machine-specific issue?

[mulch17]

Hello everyone,

I have a very weird situation to deal with, and I'm absolutely stumped here. Didn't find anything through searching either.

My team has been tasked with testing some code that exports a query to Excel, and then creates a chart and does some more formatting.

On my machine, the code works perfectly. On another machine, there's an error that we can't seem to get past. It's a 429 run-time error - ActiveX component can't create object.

We've verified that we both have the same set of references enabled (many times). Neither of us have an ActiveX Data Objects Library or Microsoft Excel Library enabled. I'm stumped as to why one machine would give this 429 error, and the other wouldn't, since the code and references are identical.

Here's the code:

Dim xl, xlsheet1 As Object

DoCmd.OutputTo acOutputQuery, QueryNameInput, acFormatXLSX, , True

Set xl = GetObject(, "Excel.Application") 'Error occurs here for other machine, but not on mine
Set xlsheet1 = xl.Worksheets(1)

With xlsheet1
   .Rows("1:1").Font.Bold = True
   .Columns.AutoFit

'etc etc
End With

With xl
   .Application.Sheets("SheetName").Select
   .Application.Charts.Add
   .Application.ActiveChart.ChartType = 51 'xlColumnClustered

'etc etc
End With

On the other machine, the error points to the GetObject line. On my machine, it executes that line without any issues, and all of the remaining code executes just fine.

This makes me very nervous, because I've used similar code in several projects, and I'm nervous that this error may rear its ugly head unexpectedly in customer environments, and cause all kinds of headaches.

Any thoughts on what might be different about the two machines to test? Thanks for reading!

 

[Ranman256]

Each machine may be a little different.
on that pc, open the db, then press ALT-11 (to VBE)
on the menu, TOOLS, REFERENCES.
if you see a checkmarked item that says, MISSING ,this is the problem
Its the wrong excel. uncheck it and scroll down and checkmark the Excel they DO have.

 

[mulch17]

Each machine may be a little different.
on that pc, open the db, then press ALT-11 (to VBE)
on the menu, TOOLS, REFERENCES.
if you see a checkmarked item that says, MISSING ,this is the problem
Its the wrong excel. uncheck it and scroll down and checkmark the Excel they DO have.

Thanks for your reply. However, we have already tried this.

Neither of us have a MISSING reference - and neither of us have any kind of reference to Excel, for that matter. This is intentional, our code uses late binding, because we don't always know a priori what Excel library version our customer's machines will have.

 

[MarkK]

GetObject() fails if there is not a currently running instance. GetObject() only gets an object, it does not create one. You need a fall-back to CreateObject() if GetObject() fails, like . . .

Sub Test193740129843()
On Error Resume Next
    Dim tmp As Object
    Set tmp = GetObject(, "Excel.Application")
    If Err = 429 Then Set tmp = CreateObject("Excel.Application")
    If Not tmp Is Nothing Then tmp.Visible = True
End Sub

 

[Cronk]

Another thing could be different a different version of Excel on the other PC, in which case, use Late Binding

 

[mulch17]

GetObject() fails if there is not a currently running instance. GetObject() only gets an object, it does not create one. You need a fall-back to CreateObject() if GetObject() fails, like . . .

Sub Test193740129843()
On Error Resume Next
    Dim tmp As Object
    Set tmp = GetObject(, "Excel.Application")
    If Err = 429 Then Set tmp = CreateObject("Excel.Application")
    If Not tmp Is Nothing Then tmp.Visible = True
End Sub

Thanks for replying, Mark. I won't be able to test this out on the other machine until tomorrow afternoon, but I'll let you know if this does it.

I'm skeptical though, because I've been using that code block before without any issues. In another database, there's an export button that starts with the same code, and that works on both of our machines without any issue.

I understand completely that GetObject requires a running instance of that object, but isn't that handled by the previous DoCmd.OutputTo line? By setting the AutoStart parameter to True, that opens the instance of Excel.Application, doesn't it? The reason it's done that way, is so that the user can choose any file name/path they want.

If this was the issue, I would have expected it to occur on both of our machines a lot earlier. I also would have expected the error message to say something like "can't find object", instead of "can't create object". And I would have expected some kind of Excel error, rather than an ActiveX error. I'm not sure how that line even involves ActiveX.

I don't mean to say that I don't believe you, and I do appreciate your response. I hope it works, but I want to make sure I fully understand the underlying cause of what's happening here, rather than just fix a bug.

Thanks Mark!

 

[mulch17]

Another thing could be different a different version of Excel on the other PC, in which case, use Late Binding

That's a great point, Cronk. We knew we were going to eventually run into this.

I was hoping this code would already take care of that, but it looks like it might not. Is there anything about the code in my original post that you think I would need to change? I had thought that the original code was already using late binding.

 

[MarkK]

I would not rely on this code:

Dim xl, xlsheet1 As Object

DoCmd.OutputTo acOutputQuery, QueryNameInput, acFormatXLSX, , True

Set xl = GetObject(, "Excel.Application")
Set xlsheet1 = xl.Worksheets(1)

You are crossing your fingers and hoping for the best when you . . .
1) trust DoCmd.OutputTo to provide you with a usable instance of a remote application.
2) trust GetObject() to provide you with an instance with a particular file loaded, when there might easily be multiple instances running.

I would create a filename and use DoCmd.OutputTo to create a file with that name. Then I would explicitly create an instance of the remote application. Then I would explicitly load the file into that application. You can take shortcuts if you want, but you are currently chasing a bug that results from just such a shortcut.

Hope this helps,

 

[MarkK]

Consider this code by way of contrast. It does almost the same job, but is more explicit in respect to creating the file, creating the remote application, and loading the file.

Private Sub Test180347()
    Dim sFile As String
    Dim xl As Object
    Dim wb As Object
    
[COLOR="Green"]    'construct a filename[/COLOR]
    sFile = CurrentProject.Path & "\2016ProductByOrder.xlsx"
    
[COLOR="Green"]    'output the query to the file[/COLOR]
    DoCmd.OutputTo acOutputQuery, "2016ProductByOrder", acFormatXLSX, sFile, False
    
[COLOR="Green"]    'create the remote application[/COLOR]
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True

[COLOR="Green"]    'load the file[/COLOR]
    Set wb = xl.workbooks.Open(sFile)
    
[COLOR="Green"]    'use the file[/COLOR]
    With wb.sheets(1)
[COLOR="Green"]        'etc etc[/COLOR]
    End With
    
End Sub

Hope this helps,

 

[mulch17]

Consider this code by way of contrast. It does almost the same job, but is more explicit in respect to creating the file, creating the remote application, and loading the file.

Private Sub Test180347()
    Dim sFile As String
    Dim xl As Object
    Dim wb As Object
    
[COLOR=Green]    'construct a filename[/COLOR]
    sFile = CurrentProject.Path & "\2016ProductByOrder.xlsx"
    
[COLOR=Green]    'output the query to the file[/COLOR]
    DoCmd.OutputTo acOutputQuery, "2016ProductByOrder", acFormatXLSX, sFile, False
    
[COLOR=Green]    'create the remote application[/COLOR]
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True

[COLOR=Green]    'load the file[/COLOR]
    Set wb = xl.workbooks.Open(sFile)
    
[COLOR=Green]    'use the file[/COLOR]
    With wb.sheets(1)
[COLOR=Green]        'etc etc[/COLOR]
    End With
    
End Sub

Hope this helps,

Thanks for your detailed replies (especially at this hour!) I was actually in the middle of replying to your first comment, but this one answers most of the questions I had.

The only question I have left is - is there a way to let the user select the file name, rather than making it hard-coded? Or would that just open a new can of worms, and take us right back to where we started? This was the reasoning behind why I was relying on the GetObject(, "Excel.Application") line in my original OP - not cutting corners. In fact, I believe I got that code a long time ago, by searching through this forum for that exact question.

I ask because my concern is that the previous versions of the 2016ProductByOrder.xlsx file would be overwritten each time this code is run. Is that correct or no? It appears that code also wouldn't allow the user to choose which file to save the spreadsheet in, but that's a much smaller concern.

Thanks again Mark, I'll let you know if this ends up solving it!

 

[MarkK]

is there a way to let the user select the file name, rather than making it hard-coded?

Yes, check out the Office.FileDialog object, which can easily be configured to prompt the user for a "save file name" just like you'd expect in Windows. But that is a time consuming feature too, for a user, to select a location and type in a name. Even if you go that route, consider constructing a file path in advance so the user can just consent to the generated default. Decide this in advance, and then program that intelligence into your system, which saves the user time.

I ask because my concern is that the previous versions of the 2016ProductByOrder.xlsx file would be overwritten each time this code is run.

Yes, it would be overwritten, but you control the filename, so you could do . . .

   [COLOR="Green"] 'construct a filename[/COLOR]
    sFile = CurrentProject.Path & "\2016ProductByOrder_" & Format(Now(), "yymmdd_hhnnss") & ".xlsx"

. . . which provides a unique filespec every second.

But also, if you open Excel, load a file, and then programmatically change it, Excel will prompt your user to save the file also, so you have a few choices about when, where and how that file save/rename occurs.

 

[mulch17]

I would not rely on this code:

Dim xl, xlsheet1 As Object

DoCmd.OutputTo acOutputQuery, QueryNameInput, acFormatXLSX, , True

Set xl = GetObject(, "Excel.Application")
Set xlsheet1 = xl.Worksheets(1)

You are crossing your fingers and hoping for the best when you . . .
1) trust DoCmd.OutputTo to provide you with a usable instance of a remote application.
2) trust GetObject() to provide you with an instance with a particular file loaded, when there might easily be multiple instances running.

I would create a filename and use DoCmd.OutputTo to create a file with that name. Then I would explicitly create an instance of the remote application. Then I would explicitly load the file into that application. You can take shortcuts if you want, but you are currently chasing a bug that results from just such a shortcut.

Hope this helps,

Hey Mark, sorry for the late reply on this. It did take a while, but I wanted to come back and say THANK YOU!!!

I finally found another machine that re-created this error, and you were exactly right about the issue. Your idea did fix the error, and it also appeared to improve the performance as well.

I'm still a little puzzled that this error occurred so inconsistently, even with environments that were nearly identical. So I'm good to go, and I will mark this as SOLVED, but for learning reasons, I still welcome any replies if anyone knows more about what's happening "under the hood" here.

And for any future readers, here's the new code I used (including the workaround for choosing file paths dynamically):

Private Sub ButtonName_Click()

Dim xl, xlsheet1, wb As Object
Dim FilePath As String

On Error GoTo ErrorHandler

With Application.FileDialog(2) '2 for late binding, msoFileDialogSaveAs for early binding
   .AllowMultiSelect = False
   .Title = "Save File"
   .InitialFileName = "Report Name " & Format(Now(), "mm-dd-yyyy")

   If .Show Then
      If InStr(1, .SelectedItems(1), ".") > 0 Then 'Avoids errors if user enters periods by mistake or tries to change the file extension
         FilePath = Left(.SelectedItems(1), InStr(1, .SelectedItems(1), ".") - 1)  & ".xls"
      Else
         FilePath = .SelectedItems(1) & ".xls" 'Save As dialog box doesn't allow filters, and requires manually entering the extension at the end. This if statement handles that for the user programmatically instead.
      End If

      DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLSX, FilePath, False
   Else
      Exit Sub 'User clicked cancel on dialog box
   End If
End With

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Open(FilePath)

Set xlsheet1 = xl.Worksheets(1)

With xlsheet1
'etc etc
End With

wb.Save

ExitHandler:
   Exit Sub

Error Handler:
   Select Case Err
      Case 2302
         MsgBox "There is already an Excel file open with the same name and location you selected. Please re-name your file path, or close that Excel file before exporting."
         Resume ExitHandler
      Case Else
         MsgBox Err.Description
         Resume ExitHandler
   End Select

End Sub

 

[MarkK]

You're very welcome, thanks for posting back with your success!
Cheers,