Query run automatic update

[Kanmi]

Please can anybody help me with scripts that i can use to run query update
from another workbook or worksheet that is when i right click and click on
REFRESH DATA then it will automatically run update from another workbook.
Please if you know any scripts that can do this function please advice.
Thanks and appreciate your time.

 

[Patrick Molloy]

in the dev environment (ALT+F11), from the Tools/References menu, choose
Microsoft ActiveX Data Object Library

then paste this code into a module (Insert/Module) - change as required

Public Sub FetchData()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim filename As String
Dim sql As String

filename = "c:\databases\test_Database.xls"
sql = "SELECT * FROM Blah"

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filename & ";" & _
"Extended Properties=""Excel 8.0;"""

Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic

Range("A1").CopyFromRecordset rs

'cleanup
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub