98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Refresh External Data using VBA
Refresh External Data using VBA
Resolved · Low Priority · Version 2003
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
Thu 7 May 2009, 13:44Trainer Stephen said...
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
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
Mon 18 May 2009, 12:07Trainer Stephen said...
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
Tue 26 May 2009: Automatically marked as resolved.
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. |
Excel tip:Convert Text to Columns in Excel 2010If you have a cell in your Excel spreadsheet that contains a lot of text and you want to divide it into separate columns, this can only be done if there is a logical character which separates the text, for example, a comma. |
