Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Refresh External Data using VBA
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Refresh External Data using VBA
Resolved · Low Priority · Version 2003
Reply Wed 29 Apr 2009, 16:24Delegate Amelie said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Thu 7 May 2009, 16:49Delegate Amelie said...
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 With
Hope this is useful
Regards
Stephen
Tue 26 May 2009: Automatically marked as resolved.
|