problem refreshing listobjects from VBA

[charlie1105]

Hi All, I'm having problems refreshing listobjects. I'm using this code:

Sub Starter1()
'Application.OnTime TimeValue("07:00:00"), "Starter1" 'set the next instance to run tomorrow
   
Worksheets("Blocked").ListObjects(1).Refresh 'update blocked list
Worksheets("Users_DB").ListObjects(1).Refresh 'update users list

Application.CalculateFull

ActiveWorkbook.Save

Call ImpCAATs
ActiveWorkbook.Save
Application.Wait Time + TimeSerial(0, 45, 0)

Call FllwUp
ActiveWorkbook.Save

End Sub

The problem that I'm having is that when it gets to the first ActiveWorkbook.save I get a message box saying "This action will cancel a pending Refresh Data command. Continue?".

Also, the data doesn't refresh until after all the code has finished running. I.e. the routines ImpCAATs and FllwUp ignore any new data that comes from the refreshes.

The code does however all work correctly if I step through it (or step through until after the refreshes and then let the rest run).

I've tried pausing, calculating, selecting cells in the listobject, but can't seem to get it to work. Anyone have any ideas?

Cheers

 

[Trevor G]

Hi All, I'm having problems refreshing listobjects. I'm using this code:

Sub Starter1()
'Application.OnTime TimeValue("07:00:00"), "Starter1" 'set the next instance to run tomorrow
 
Worksheets("Blocked").ListObjects(1).Refresh 'update blocked list
Worksheets("Users_DB").ListObjects(1).Refresh 'update users list
 
Application.CalculateFull
 
ActiveWorkbook.Save
 
Call ImpCAATs
ActiveWorkbook.Save
Application.Wait Time + TimeSerial(0, 45, 0)
 
Call FllwUp
ActiveWorkbook.Save
 
End Sub

The problem that I'm having is that when it gets to the first ActiveWorkbook.save I get a message box saying "This action will cancel a pending Refresh Data command. Continue?".

Also, the data doesn't refresh until after all the code has finished running. I.e. the routines ImpCAATs and FllwUp ignore any new data that comes from the refreshes.

The code does however all work correctly if I step through it (or step through until after the refreshes and then let the rest run).

I've tried pausing, calculating, selecting cells in the listobject, but can't seem to get it to work. Anyone have any ideas?

Cheers

Would it not be easier to store the entries in a sheet first and once it has processed the other workbooks then refresh the listobject.

 

[charlie1105]

Hi Trevor, I'm not sure I follow what you are suggesting.

The sheets "Users_DB" and "Blocked" contain listobjects (tables) that are pulled from a database.

Each day I need the code to refresh these tables and then run the routines ImpCAATs and FllwUp - which act on data in these tables.

There is only one workbook involved.

Cheers

 

[Trevor G]

Hi Trevor, I'm not sure I follow what you are suggesting.

The sheets "Users_DB" and "Blocked" contain listobjects (tables) that are pulled from a database.

Each day I need the code to refresh these tables and then run the routines ImpCAATs and FllwUp - which act on data in these tables.

There is only one workbook involved.

Cheers

Hi Charlie,

If the data is coming from a database have you used the Get External Database Query. Which version of Excel are you using?

 

[charlie1105]

Hi Trevor

I'm using Excel 2007 and am open to any suggestions to get these tables to refresh. The Users_DB table is a straightforward pull from a database, the Blocked table has one additional column added (which is just a concatenation of 2 columns).

I have no problems refreshing manually, or when stepping through the code - it's just when the code is running the refresh seems to "pend" until all the code has run and then execute (and the save command interupts this).

If I omit the saving of the workbook, then the code appears to run, and at the end the tables have been refreshed, but the other routines do not act on any new data that has been pulled in.

Cheers

 

[charlie1105]

Solved!

Managed to solve this by editing the properties for each table and unticking "enable background refresh".

Couldnt work out the vba to turn off background refresh for list objects - this doesnt work...

Worksheets("Blocked").ListObjects(1).Refresh BackgroundQuery:= False

Oh well, solved this problem!