HI all,

I have a external data query, which is refreshed by the user via a button on
the sheet.
on a different sheet is the pivot table if the data which datasource is the
excel sheet that has the query. Pretty normal stuff
In the code linked to a button on the sheet I refresh the data from the
mssql server. Once that is done I refresh the pivot table. However the pivot
table is not refreshed, it would still contain the old data.
I have seen that by adding a msgbox before the pivot refresh that the new
data is not updated in the spreedseat until the sub is finned and exited.
Which makes perfect sens as to why the pivot table is not being refreshed
with the new data, because at the time the refresh code is run, nothing is
returned via the query to exccel.

How can I achieve this programatically. I want to to have one sub, to
refresh the excel query from MSSQL, and when that is done, refresh the pivot
table

The code used is

Sub RefreshAllData()
Application.DisplayAlerts = False
If Len(Login.tbUsername.Text) = 0 Or Len(Login.tbPassword.Text) = 0 Then
Login.Show
End If
sPassword = Login.tbPassword.Text
sUser = Login.tbUsername.Text
Excel.Application.ScreenUpdating = True
Updating.ProgressBar1.Value = 0
Updating.Repaint
Updating.Show (Modal)
Application.PivotTableSelection = True

If Login.btnCancel.cancel = False Then
If Len(sPassword) = 0 Or Len(sUser) = 0 Then
Conn = ""
MsgBox ("No Login info")
Else
Set wbA = ActiveWorkbook
sPassword = Login.tbPassword.Text
sUser = Login.tbUsername.Text
Updating.ProgressBar1.Max = wbA.Worksheets.Count + 1
Updating.ProgressBar1.Value = 1
Updating.Repaint
For Each ws In wbA.Worksheets
For Each qt In ws.QueryTables
Conn = "ODBC;DRIVER=SQL Server;SERVER=192.168.1.197;UID=" +
sUser + ";PWD=" + sPassword + ";APP=Microsoft Office
2003;WSID=GABRIEL;DATABASE=WOWDynamicList"
qt.Connection = Conn
Application.StatusBar = "Refreshing Data from server " &
qt.Name
qt.Refresh
Login.Repaint
Updating.ProgressBar1.Value = Updating.ProgressBar1.Value + 1
Updating.Repaint
Next

Next
End If
End If
Updating.Hide
updatepivot
Application.DisplayAlerts = True
Application.StatusBar = False
End Sub


Sub updatepivot()
Set wbA = ActiveWorkbook
For Each ws In wbA.Worksheets
''ws.Activate
For Each pvtTable In ws.PivotTables
Application.DisplayAlerts = False
Application.StatusBar = "Updating PivotTable " & pvtTable.Name
pvtTable.RefreshTable


Next
Next
Application.DisplayAlerts = True
Application.StatusBar = False
End Sub


Thanks
Robert