Instantiated Excel applications fails alternately

[Rod]

This is driving me crazy!

I am instantiating an Excel application from an Access project. All works well until the following code is executed

Private Sub SlantColumnHeadings()
'    MxlsWorksheet.Range(Cells(1, 3), Cells(1, 8)).Select
'    MxlsApp.Goto "Activities"
'    With Range("Activities")
    MxlsWorksheet.Range("$C$1:$I$1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 60
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Now, this code works on the first, third, fifth, ... executions but fails on the second, fourth, ... executions. If it's relevant, the error code is 1044 if I'm using the Range object/method or otherwise that the Selection is Nothing. You can see the various different ways I have tried from the commented-out statements. All have the same effect. The code resides in an Access module.



Since the code works 50% of the time, I conclude it's OK; there's something else amiss. I cannot believe there is a hidden toggle in the underlying software, so what is different about the even numbered executions? Here's a summary of my actions:

1. Software execution OK
2. Software fails - reset Access VBA code
3. Software execution OK
4. Software fails - reset Access VBA code
5. ...

So is it the VBA Reset that's clearing whatever prevents the code executing successfully a second time? Has anyone encountered this or similar before and, more important, what was the solution? My Excel Application is Set .. New .. each time and all other Excel objects are set based on the new application so I can't believe it's a carry-over instance that's causing this.

Set MxlsApp = New Excel.Application
    Set MxlsWorkbook = MxlsApp.Workbooks.Add
    Set MxlsWorksheet = MxlsApp.Worksheets(1)

Ideas anyone?

[Rod]

Well, I have solved my problem. I can't say exactly what was happening but it is caused by having multiple copies of Excel simultaneously. I changed the instatiating code to be as follows (yes, I know, I won't leave it like this ) and it all works!

  On Error Resume Next
    Set MxlsApp = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        Debug.Print "creating new app"
        Set MxlsApp = CreateObject("Excel.Application")
    End If

The odd thing is that the createobject branch is taken only on the first execution. The second and subsequent executions obviously find an existing Excel application even though: Excel was closed; the taskbar shows no Excel icon; the task manager shows no Excel application.

[Dunro]

The odd thing is that the createobject branch is taken only on the first execution. The second and subsequent executions obviously find an existing Excel application even though: Excel was closed; the taskbar shows no Excel icon; the task manager shows no Excel application.

Do you mean the task manager lists no EXCEL.EXE under processes, or that task manager shows no Excel under Application? New instances are usually Invisible, and wouldn't show under Application.

[Rod]

Do you mean the task manager lists no EXCEL.EXE under processes, or that task manager shows no Excel under Application? New instances are usually Invisible, and wouldn't show under Application.

Gosh, that was a year ago! If memory serves me correctly then there was no Excel application in Task Manager. I'm not sure if I looked under the processes.

[Dunro]

Oops, didn't mean to resurrect an old thread... Clearly got my 2012s and 2013s mixed up. :-)

[Rod]

Don't worry about that; this is far more interesting than the usual run-of-the-mill, "How do I do cascading combo boxes?" It's obvious that, true to the concepts of OOP, once Access has created a reference to an Excel object then that Excel object is not destroyed as long as the Access reference is itself current. I wish this was true across all Ofice Automation products but I've experienced Access references 'left high and dry' after the target Office Automation application is closed. The most secure way that I have found is to test each time I use a cross-application reference to see whether that reference is still valid.

[Dal Jeanis]

Wow - thanks for resurrecting this - I'm doing Access-to-Excel design at the moment, and this is another item to keep in mind...

[Rod]

Dal,

Even if you follow something akin to post #2 (remember to reset error handling) there are certain Excel methods that I cannot get to work when issued from Access but that work fautlessly when issued from an Excel session. Sorry to be somewhat vague but I can't remember offhand what they are except that they were multisheet operations.

Rod