Query run automatic update

K

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top