Amelie has attended:
Excel VBA Advanced course
PowerPoint Intermediate Advanced course
Refresh External Data using VBA
Hello,
I would like to write some code which would allow me to refresh all external data queries in all open workbooks. Could you please help?
Many thanks,
Amelie
RE: Refresh External Data using VBA
Hi Ameli
Thank you for your question
Could you refresh my recollection for me and advise which tool you are using to build your queries in the first place. Am I right in thinking that you are using the new database query tool in the data menu?
Thanks
Stephen
RE: Refresh External Data using VBA
Hi Stephen,
I indeed use the Database query tool.Data-Import External Data -Import Data and define some text files as my source files.
Thanks,
Amelie
RE: Refresh External Data using VBA
Hi Amelie
Thank you for your question
First let me apologise for the delay in response; I have been away from the office lately, and your question has taken a little bit of research.
The tool you refer to uses the querytables method of the worksheet object. It has three arguments, a connection string which locates the database, a destination which specifies where the data will go on the worksheet and an sql string to specify the table or query
The following is a simple example
Dim qtTable As QueryTable
sqlstring = "select FirstName from Employees"
cstring = ODBC;Database=Northwind"
With Sheets("Target").QueryTables.Add(Connection:=cstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End WithHope this is useful
Regards
Stephen
