ListObject.ListColumns.Add fails if Excel application is not visible and ScreenUpdating not enabled

[AOB]

Just discovered this oddity and wondering if anybody knows why it's happening / how to work around it?

I have some VBA in Access that generates an Excel workbook, creates some tables (ListObject) and manipulates them from some queries / recordsets. (The pertinent point being, this code is running "remotely" from Access, using the Excel Object Model, late-bound, rather than within Excel natively... Which I do all the time, I'm well used to it at this stage...)

Code is pretty straightforward; if it's the first column in the table, it already exists (from when the table is created) so just set the ListColumn object to that first ListColumn and modify as necessary. If it's not the first column in the table, add a new ListColumn and modify that, etc.

(I've reduced the code down to the bare essentials here but suffice to say, the code works perfectly fine outside of what I've posted below)

What's weird is, while developing/debugging, for obvious reasons, I make the Excel application visible and enable ScreenUpdating. Now that I have everything working as expected, I keep the Excel instance hidden and disable ScreenUpdating while it's doing all the work. Now, it bugs out when it hits the line as commented :

Dim sht As Object                   ' Worksheet object
Dim lso As Object                   ' Table object
Dim lsc As Object                   ' ListColumn object

With sht
    Set lso = .ListObjects.Add
    With lso
        ' Loop through the report columns for the report and add them from the raw data table
        For j = 0 To lngColumnCount - 1
            Select Case j
                Case 0
                    Set lsc = .ListColumns(1)
                Case Else
                    Set lsc = .ListColumns.Add    ' This is where the error occurs
            End Select
            ' Work with the list column...
        Next j
    End With
End With

This throws a very unexpected (to me?) error :

Error 1004 : Application-defined or object-defined error

But it's only an issue if Excel is hidden and the ScreenUpdating is disabled- if I set the application visibility to True, and enable ScreenUpdating, it all works fine?

(For clarity, I have to do both - making the application visible but keeping ScreenUpdating disabled, still throws the error; keeping the application hidden but enabling ScreenUpdating disabled, also throws the error)

Can't get my head around why this would be an issue, any ideas?

 

[Domenic]

I don't know why you're getting that error. I tested it with Excel hidden and ScreenUpdating set to False, and it seems to work fine.

Sub test()

Dim xl As Object
Dim wb As Object
Dim sht As Object                   ' Worksheet object
Dim lso As Object                   ' Table object
Dim lsc As Object                   ' ListColumn object

Set xl = CreateObject("Excel.Application")

'xl.Visible = False

xl.ScreenUpdating = False

Set wb = xl.Workbooks.Add

Set sht = wb.sheets(1)

With sht
    Set lso = .ListObjects.Add
    With lso
        Set lsc = .ListColumns.Add
    End With
End With

xl.ScreenUpdating = True

xl.Visible = True

'xl.Quit

End Sub

 

[AOB]

Yup, that's pretty much exactly the same as what I'm doing every time. Throws a 1004 error as soon as it tries to add a ListColumn to the table.

Interestingly, I now suspect it is neither the visibility nor the screen updating causing the problem, but actually the WindowState (?)

The problem goes away if I force the WindowState to be Normal (xlNormal) before the first ListColumn addition :

Select Case j
    Case 0
        Set lsc = .ListColumns(1)
    Case 1
        appExcel.WindowState = -4143
        Set lsc = .ListColumns.Add
    Case Else
        Set lsc = .ListColumns.Add
End Select

Looks like the Excel instance is switching to Minimised (xlMinimized) before this point in the code (not by me, from what I can tell?) but switching it back to Normal first allows the code to run without error (?)

Further digging required tomorrow...

 

[Domenic]

I can confirm that setting the WindowState to xlMinimized gives me the same error . . .