PDA

View Full Version : Solved: backgroundquery:=false seems to fail



cactus
04-21-2009, 06:43 AM
Hi,

I'm refreshing several queries from an ODBC connection (if the query already exists, I refresh it, other way, I created it) in the same worksheet, and the Backgroundquery property seems to fail in only in one of them!!!
Is there any problem with the code?

I need the refreshed data in a pivot table, so i have to be sure if the query is already updated and the backgroundquery=false apparently is not guaranteeing this...when the code update the Pivot Table, the old (and now inexisting) values continue there.
I suppose that a possibility is to use the AfterRefresh event but I'm just a newbee in vba :( ...

here is my code:

Application.ScreenUpdating = False
Sheets("data").Activate

On Error GoTo BB
Sheets("data").Range("a12").Activate
ActiveCell.QueryTable.Refresh BackgroundQuery = False

Sheets("data").Range("j12").Activate
ActiveCell.QueryTable.Refresh BackgroundQuery = False

Sheets("data").Range("r12").Activate
ActiveCell.QueryTable.Refresh BackgroundQuery = False

Sheets("data").Range("aa12").Activate
ActiveCell.QueryTable.Refresh BackgroundQuery = False
Exit Sub
BB:
If ActiveCell = Range("a12") Then
Range("a12:f200000").ClearContents
ejecuta_a
Else
If ActiveCell = Range("j12") Then
Range("j12:n200000").ClearContents
ejecuta_b
Else
If ActiveCell = Range("r12") Then
Range("r12:w200000").ClearContents
ejecuta_c
Else
If ActiveCell = Range("aa12") Then
Range("aa12:ag200000").ClearContents
ejecuta_d
Else
End If
End If
End If
End If
Resume Next


and this is the code if the query does not exists:


Sub ejecuta_a()

SFORCE = "ODBC;DSN=***;UID=***"
With Sheets("DATA").QueryTables.Add(Connection:=***, Destination:=Range("a12"), Sql:=***)
.FieldNames = False
.BackgroundQuery = False
.Refresh
End With
End Sub


Someone can help me?

Thanks in advance and sorry for my english.
</IMG>

cactus
04-21-2009, 11:01 AM
It was a sintax error

"Backgroundquery:=False" instead of "Backgroundquery=False"

Anyway, if someone wold like to help me in a Event way to wait for the refresh in BackgroundQuery=True mode, i'll be thankfully.

Thanks