getting runtime error on VBA when creating totals on a table listobject because it hasnt completed making the table

[bobbydaonlybuilder]

HI All,

So ive a query that runs

//runqry bring back data
//refresh existing activeworkbook.connection (which always uses the same table name "MYTABLE"

then im trying to run the code.. oops sorry missed a bit, I do the following command before the query refresh
Set tbl = Sheets(1).ListObjects("MYTABLE")
tbl.TotalsRowRange.Delete

to remove existing table totals

back to after connection refreshed i try this
Sheets(1).ListObjects("MYTABLE").ShowTotals = True

With Sheets(1).ListObjects("MYTABLE")
' Ive even tried this to no avail....Application.Wait Now() + TimeValue("00:00:03")
.ListColumns("Amount").TotalsCalculation = xlTotalsCalculationSum

It fails everytime with runtime error.. if i hit debug i can then step through it no problem and it works.. it just doesnt exist when the whole thing runs in real time

ANy ideas?

Regards
James

 

[NdNoviceHlp]

Welcome to the Board bobbydaonlybuilder! Maybe trial...

 Do Events

HTH. Dave

 

[bobbydaonlybuilder]

Welcome to the Board bobbydaonlybuilder! Maybe trial...

 Do Events

HTH. Dave

Hi Dave,
sorry, I should have said I tried that too before the wait. Tried putting it in multiple place as well and no difference

 

[NdNoviceHlp]

James if that doesn't work U can trial this code from Jon Peltier...

Dim t As Double
t = Timer
Do Until Timer - t > 1
DoEvents
Loop

Dave

 

[bobbydaonlybuilder]

James if that doesn't work U can trial this code from Jon Peltier...

Dim t As Double
t = Timer
Do Until Timer - t > 1
DoEvents
Loop

Dave

just tried that Dave, still no difference, still runtime error but after i debug i can run through no probs

 

[bobbydaonlybuilder]

very frustrating this.. I know i can get past it if i read all the data in an array, loop through the array doing the totalling and spit it back out again, but it seems to be a whole lot of work for nothing.. (plus i lose all the handiness of the table object unless i do even more work converting it to a table object after all the above).Cant understand why this is taking so long to register/aknowledge the listobject column

 

[bobbydaonlybuilder]

just for anyone that happens across this post with the same issue, i found a solution online (and would have posted up the link only i was using a different computer at the time, sorry i cant credit the person with the answer)
Its because the query is still running in background (even though it may look like it has finished)
so after your:
With ActiveWorkbook.Connections("BOCBACS").ODBCConnection
use the following

brRefresh = .BackgroundQuery
.BackgroundQuery = False
.Refresh
.BackgroundQuery = brRefresh
end with

it solved my issue

Thanks for your help Dave

 

[NdNoviceHlp]

Thanks for posting your outcome James. Glad U got it sorted out. Have a nice day. Dave