Can't add/delete ListColumns from Table unless Worksheet is visible

[AOB]

I have some code that generates a table in Excel (ListObject) from a query. As part of the process, I need to delete a couple of redundant columns.

Dim objWorkbook As Object
Dim objWorksheet As Object
Dim objTable As Object

Set objWorksheet = objWorkbook.Sheets("MyWorksheet")
Set objTable= objWorksheet.ListObjects("tblMyTable")
objTable.ListColumns("ColumnNameA").Delete
objTable.ListColumns("ColumnNameB").Delete

But when I run the script (the script is running "remotely" from Access, not in Excel directly) the .Delete lines throw 1004 errors :

Error 1004: Application-defined or object-defined error

If, however, I add a breakpoint to the code just before I attempt to delete the columns, switch focus to the Excel workbook (i.e. make it visible), then go back and resume the code, it works fine?

The same thing happens if I try to add a new ListColumn to a table :

Dim objListColumn1 As Object
Dim objListColumn2 As Object

Set objListColumn1= objTable.ListColumns("SomeColumn")
Set objListColumn2= objTable.ListColumns.Add(objListColumn1.Index + 1)

Again, the .Add command only works if I pause the code, make the Excel worksheet visible, then resume the code.

How can I manipulate the table(s) without having to interrupt? The whole point of the code is to automate the generation of the Excel workbook "in the background" and just present the final result.

All suggestions welcome! Thanks!

 

[AOB]

Figured it out...

I had a ghost line from a previous iteration of the code that was setting the .WindowState of the Excel.Application object before all of the tidy-up work was completed. So effectively I was telling Access to make Excel invisible (i.e. appExcel.Visible = False) but then telling it to minimise the Excel window (i.e. appExcel.WindowState = -4137) Conflicting instructions there so Excel basically makes itself "half-visible" (it appears in the taskbar but the application itself is not fully refreshing)

Seems like this has a knock-on effect when you then go to manipulate ListObject / ListColumn objects as Excel is in a quasi-fuzzy state. By maximising the window, you force Excel to refresh itself, which restores the integrity of the objects and they can be manipulated again. This is why the code works when you pause, display Excel and then resume.

I removed the .WindowState line and moved it to a generic function for terminating the Excel object (which obviously doesn't get called until after all the work has been completed). Working like a charm now!

Hope this helps anybody else scratching their heads with anything similar; if anybody can / wants to provide a better explanation than my garble above, please feel free!