Apply ListObject.TableStyle after first clearing any default/pre-existing styles

[AOB]

Bit of a weird one this...

I'm trying to generate an Excel table from an Access recordset and it's painfully slow for something that should, to my mind, be relatively quick.

I'm trying to sequence the order of operations to make it as efficient as possible, i.e. :

1. Create the table (ListObject)
2. Add the data (ListObject.InsertRowRange.CopyFromRecordset)
3. Format the columns (ListColumn.DataBodyRange.NumberFormat based on the data type of each respective field in the recordset)

One of the things I thought was worth a bash was removing the table styling before adding the data and formats (as, to my mind, Excel is having to do a lot more work on each iteration with styling in place, even with .ScreenUpdating disabled) and then applying the style at the very end.

In other words, make the sequence as follows :

1. Create the table (ListObject)
2. Remove any default/pre-existing styling (make it plain)
3. Add the data (ListObject.InsertRowRange.CopyFromRecordset)
4. Format the columns (ListColumn.DataBodyRange.NumberFormat based on the data type of each respective field in the recordset)
5. Apply a TableStyle with the relevant formatting, now that all the data and cell formatting is in place

What's weird is, if I create the table and then immediately remove the styling :

With lso
    .Name = strTableName
    .TableStyle = ""
End With

This works fine and you have a table structure with no styling/formatting. But when you then try to add styling at the end :

With lso
    .TableStyle = "TableStyleMedium16"
End With

This throws an error :

Error 438 : Object doesn't support this property or method

On debugging, it seems that when the table is created, the ListObject.TableStyle property is actually a layered object in and of itself, with several sub-properties (e.g. TableStyle.BuiltIn, TableStyle.Creator, TableStyle.Name, TableStyle.Parent etc.)

As soon as you clear the styling via ListObject.TableStyle = "" it flattens to just a simple Variant/String - it is no longer an object.

So when you then go to apply styling later on, it no longer recognises it as an object, and bugs out.

Which contradicts MS's documentation on the method as they imply you can just set the name the way I've described above - rather than documenting the actual object underlying it.

Seems weird behaviour and only thing I can think of is that you have to create a whole new TableStyle object and set it - but if all you have to go off is the name, and none of the other properties, that's going to be messy.

From what I can see here, once you purge the ListObject of any TableStyle, it's lost to the ether and can't be recovered again - so how do you make a table "plain" while you work on it and then apply styles after the fact?

 

[RoryA]

I wouldn't do it in that order. I'd put the data in a worksheet and then turn it into a table (I'm assuming you have a reason for not just using a querytable).

FWIW, I have no problem clearing the style and then reapplying it by name.

 

[AOB]

I wouldn't do it in that order. I'd put the data in a worksheet and then turn it into a table (I'm assuming you have a reason for not just using a querytable).

Yup, agreed, and that's precisely what I was about to test next myself - I was just curious to see if the table formatting might give me a bump in performance first.

Would it be better to format the columns while they are still ranges, before converting the range to a table, or convert the range to a table first and then work through the number formats?

(It's so much easier to do it with a ListObject as you can simply refer to the column names rather than having to determine the position of each column each time - but I suspect that is partly what is making it slower)

I'm not using a QueryTable because the underlying data is only temporary in nature. I'm using Access to perform a set of complex SQL queries across multiple data sources, then dump the final refined data into Excel, and purge the data from the DB - it is only intended to be a snapshot of the current dataset and is not retained. The SQL is just a much, much faster way of combining all the data together.)

Although - I suppose I could use a QueryTable just to get the data into the spreadsheet, then break the data connection and leave it as raw, unconnected data? Is that feasible / is that what you would recommend? Am I going to get much of a performance boost from approaching it that way?

(Doesn't get away from the weird behaviour of the TableStyle object - am I right in saying that, as soon as you "clear" the existing TableStyle, you can't go back and apply a new one?)

 

[RoryA]

If it's just a temp recordset I'd stick to what you are doing now. The formatting shouldn't be an issue in the table. As I mentioned in my edit, I can reset the table style no problem.

 

[AOB]

Ah, just discovered another issue with using a QueryTable - I'm using a parameterised query in Access to read the data into a recordset before then passing it across to Excel. If I create the QueryTable in Excel, I've no means of providing those parameters. (I'm running the code from Access, not from Excel, so effectively doing all this "remotely")

I suspect the fact that the code resides in Access may explain why the TableStyle behaviour is as it is. I'm referencing the Excel object model from Access and manipulating the spreadsheet from there. That's a late-bound reference to the Excel application object. That may be why the parent object "loses" the structure of the ListObject.TableStyle child object when the style is cleared.

When I clear the style, and debug the object, the ListObject.TableStyle changes from an TableStyle/Object to a Variant/String. Maybe if it was being done within Excel itself, that wouldn't happen?

I'll try copying the recordset to a range instead of a table and then convert it afterwards and see if that makes a difference.

Thanks!