Use Excel button to refresh external data

[aarondewberry]

All

I am trying to put a button on my worksheet to refresh external data.

Can you give me some code that will do this?

many thanks

 

[PHV]

One way:
ActiveWorkbook.RefreshAll

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aarondewberry]

Thanks for the reply PHV

This is the code I originally wrote but it doesn't refresh the data or it refreshes data outside the range. Very bizarre... Am I doing something wrong??

Private Sub CommandButton1_Click()
On Error GoTo Err_CommandButton1_Click
Application.ActiveWorkbook.RefreshAll
Application.Cells.Range("J3") = Environ("UserName")
Application.Cells.Range("J4") = Date
MsgBox "The ASU report has now been refreshed!!!", , "Refreshed!!!"

Exit_CommandButton1_Click:
Exit Sub

Err_CommandButton1_Click:
MsgBox Err.Description
Resume Exit_CommandButton1_Click

End Sub

 

[PHV]

Which data you want to refresh ? A peculiar QueryTable ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aarondewberry]

I am refreshing an access 2000 query. But I have put parameters (between [date1] and [date2]) on the 'SopsDate' field in microsoft query. It works fine if i just push the refresh data button. But not using the code!!

 

[PHV]

it refreshes data outside the range
Any chance you could elaborate ?

 

[aarondewberry]

If for instance I want to see all data for dates between 01/04/05 and 31/05/06, i would refresh the data using the button and enter the dates in the parameter pop ups. More often than not it does not refresh at all or, on some occasions today, the query will give some data outside the date range. To me, it makes no sense it doing this. If i refresh using data/refresh data it works fine.
Is there any other code that can be used other than ActiveWorkbook.RefreshAll?

 

[combo]

Assuming that you have only the two parameters:

Dim QT As QueryTable
Set QT = ThisWorkbook.Worksheets(1).QueryTables(1)
With QT
    .Parameters(1).SetParam Type:=xlConstant, Value:=DateSerial(2006, 1, 1)
    .Parameters(2).SetParam Type:=xlConstant, Value:=DateSerial(2006, 12, 31)
    .Refresh
End With

combo

 

[PHV]

For me, the query should get its parameters from Cells ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886