BackgroundQuery = False doesn't seem to work 1

[GoatieEddie]

Unexpected behaviour going on here! I have the following macro that updates queries linked to an Access table and then saves the file using a distinct filename. However, even though I am using the BackgroundQuery = false, I am getting a prompt box saying the action (ie the saveas) will cancel a pending refresh and as such the sheet doesn't update before it saves. I have checkd the syntax adn everything and can't see what is wrong. Any clues?

Sub change_queries(New_Entity)
Old_Entity = ActiveWorkbook.Worksheets("Summary Sheet").Range("a1")
For Each ws In ActiveWorkbook.Worksheets
 For Each qt In ws.QueryTables
  
  current_sql = qt.CommandText
  new_sql = Replace(current_sql, Old_Entity, New_Entity)
  qt.CommandText = new_sql
  qt.Refresh BackgroundQuery = False
 Next qt
Next ws
ActiveWorkbook.Worksheets("Summary Sheet").Range("a1") = New_Entity
ActiveWorkbook.SaveAs "W:\Returns_" & New_Entity & "_" & Format(Date, "ddmmyy")
End Sub

many thanks,

GE

 

[combo]

What happens when you use:

qt.Refresh BackgroundQuery:=False

?

combo

 

[GoatieEddie]

It then works perfectly! MANY THANKS!

 

[xlbo]

LOL - I recognise that code !! Apologies if it was my syntactical error in the original thread

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GoatieEddie]

No worries Geoff. I have come on leaps and bounds thanks to you guys!