Copy data after external data refresh

[csmikle]

Hi All
I hope you can help as my VBA skills are very limited (newbie)

I am trying to get excel to perform a refresh to an external database then copy from the result to another cell

basically what the code does is prompt the user for an order-load number in that format "Order-Load" then I use formulas to separate the Order and the Load. This then becomes the parameters for my query, next excel refreshes the query and should copy one cell from the results to another cell outside the query area. this is my problem excel copies the contents of the cell before the results of the query is returned. I tried wait/ sleep commands but that just pauses the entire application including the return of the refreshed data.

Here is my code, Please Help

Private Sub CommandButton1_Click()
Dim OrderLoad As String
OrderLoad = InputBox(Prompt:="Scan Work Order", Title:="Work Order", Default:="WO #")
Sheets("Sheet4").Range("G2") = OrderLoad
Sheets("Sheet4").Range("C2") = Sheets("Sheet4").Range("K2").Value
Sheets("Sheet4").Range("C3") = Sheets("Sheet4").Range("L2").Value
Range("B5:D6").QueryTable.Refresh
Sheets("Sheet4").Range("D8") = Sheets("Sheet4").Range("D6").Value

End Sub

Here are my formulas in K2 and L2
=RIGHT(G2,LEN(G2)-FIND("-",G2,1))
=LEFT(G2,FIND("-",G2,1)-1)

C2 and C3 have the parameters for the query that returns data to range B5 to D6

Also I found AfterRefresh event on Microsoft website http://support.microsoft.com/kb/213187 but could not get it to work. Itkept giving me an error "User defined Type not defined"

Here is my after refresh code

Dim X As New Class1

Sub Initialize_It()
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Set X.qt = ThisWorkbook.Sheets(Sheet4).QueryTables(1)
End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
Sheets("Sheet4").Range("D8") = Sheets("Sheet4").Range("D6").Value

End Sub

I admit I don't understand what I am doing with the after refresh so if there is a simpler solution I would appreciate it.



Thanks for any help.

 

[csmikle]

I was able to get what I needed done with the following code


Dim X As New Class1

Sub Initialize_It()
Set X.qt = ThisWorkbook.Sheets(1).QueryTables(1)
End Sub

**************

Public WithEvents qt As QueryTable

Private Sub qt_BeforeRefresh(Cancel As Boolean)

' Declare variables.
Dim OrderLoad As String

'Prompt for Order-Item input
OrderLoad = InputBox(Prompt:="Scan Work Order", Title:="Work Order", Default:="WO #")

'Place order item into update cells
Sheets("Sheet1").Range("D2") = OrderLoad


End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

'Copy wanted data
Sheets("Sheet1").Range("H6") = Sheets("Sheet1").Range("E6")

End Sub

 

[John_w]

Try changing:

Range("B5:D6").QueryTable.Refresh

to:

Range("B5:D6").QueryTable.Refresh BackgroundQuery:=False

and there should be no need for the BeforeRefresh and AfterRefresh class event handler.

 

[fasberg]

I have just found an article on this topic:

http://vbaproexcel.blogspot.com/2011/07/query-after-refresh-method.html

it may be helpful

Regards!