Excel VBA: Adding a Query Table Removes Formatting

Brucmack
Brucmack used Ask the Experts™
on
Here at my workplace there are a large number of sheets that contain links to database files via the "VLOOKUP" command.  I have been assigned to create a method to convert these lookups to SQL queries into our SQL server.

I have the code mostly finished and working pretty well.  One problem I am having, however, is that the formatting on the first cell in the range is getting wiped every time I create a query table.  The cells with the existing lookups have custom number formats, so it is important to keep them.  In some cases, borders get removed as well, which really screws up the look of the sheet.

Here is how I am configuring the query table just after it has been added.  The variables SQLStatement and QueryName are set above, and don't really matter to this example.

With ActiveQueryTable
    .CommandText = SQLStatement
    .Name = QueryName
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh False
End With

To get around this problem currently, I am refreshing each query in the foreground when it is created, then running a routine that resets the formatting on the result range.  I would like to figure out if there is another way around this that would allow me to refresh with background queries, as it cuts about half the time off the macros.  Unfortunately it means the queries are refreshed after my macros have finished running, so I can't fix the formatting.  I have found no way to pause my macros such that excel will refresh the query tables...  The wait function pauses refreshes, and a loop with DoEvents doesn't seem to work either.  If someone could provide a method to pause the macro to allow queries to refresh, that would solve my problem as well.

Thanks!

*EDIT* The version of Excel I am using is 2000, if it matters :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hello Brucmack,

If you want to run code after your queries have finished refreshing them you'd probably be better off running non background queries. It only speeds thing up because it doesn't wait for the queries to finish but if you want to do something after that you're gonna have to wait anyway right? ;)

Apart from that you could try set the HasAutoFormat Property to False. I'm not sure if it works in all situations but it prevents the querytable from overriding cell formats.

Hope this helps

Author

Commented:
Thanks for the suggestions...

Unfortunately, the HasAutoFormat property only applies to PivotTables, not QueryTables (at least in Excel 2000).  I double checked the HasAutoFormat property on one of my query tables in the immediate window anyway, and it defaults to False.

The weird thing is that it doesn't overwrite the formatting over the whole table, it only does so in the first cell (or the first row if the query returns multiple columns).  I suppose this has something to do with how Excel handles field names, but I have them turned off.

Regarding the background queries issue...  It does in fact speed up my operations by a significant amount, because I am creating many query tables (like 50+) that reference the same database.  When Excel refreshes them in the background, the entire refresh process takes much less time then refreshing each one separately.  So yes, I still have to wait, but not for nearly as long.  Plus, it would help me in some other areas if I could refresh all the queries on my sheet in the background and then have some other code run when the refresh completes.
>>the HasAutoFormat property only applies to PivotTables, not QueryTables
If so how come you checked its value? :)

OK, Excel is probably reusing the connection when you run the queries in the background... so it's quite possible it actually does things faster.

As to waiting for the queries to finish refreshing you could use the Refreshing property to check that out. However I've tried it myself using a DoEvents inside a loop to no avail... the property only seems to change when I pause the application to Debug it... an alternative (yet untested since I was going to try that today afternoon) is to time a routine that checks if the queries are all refreshed... You'd set it in your code, after refreshing the queries:

Application.OnTime Now + TimeValue("00:00:10"), "doSomething"

And on the doSomething Procedure you'd check if the Refreshing property was false for all queries. If so you'd do the formatting or whatever. If not just schedulle the procedure to run again in 10 sec or whatever time you want...

Like I said. I'm going to try it this in a few hours... I can tell you how it went by then...
but if you want to try it first and then tell me... :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

>>The weird thing is that it doesn't overwrite the formatting over the whole table, it only does so in the first cell

Maybe there's some conditional formating in this cell? Check that out by using Edit->Goto->Special->Conditional Formats
I use XL97 and haven't had any problem with format overriding when HasAutoFormat is set to False so far.

Author

Commented:
Nothing on the conditional formats check.

For the scheduled routine, I am going to try it after lunch, my stomach is complaining :)  I'll post back on my progress then, if I make any.

Author

Commented:
It works!  Thanks for the tip, I didn't know about the OnTime function before now...  But then again my only resource before I found this was the help, so maybe that's not so surprising, hehe.

It is possible that this formatting issue only occurs with Excel 2000.  I've found it rather quirky sometimes with deleting query tables as well.  I don't have access to XP or 97 to test this out though.

Thanks for the help!
My only resource is also the Excel Help... :)
I found that one quite by accident...

Thanks for testing this one out for me. In theory it should work but now I know it does and won't give up on the first few problems

Ah, and thanks for the points :)

Author

Commented:
You're welcome... Certainly earned them, this has been bugging me for weeks, wish I'd found this site earlier.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial