Use .txt file instead of SQL Query

I've created a basic macro that pulls data from SQL sever into excel.

The connection string and and query work fine but I was wondering if it is possible to replace the query embedded within the macro to call a .txt or .sql file and use the text within the file as the query?

so instead of .Open "SELECT * FROM Authors" I would have a link to the text file and use the text contained within it as the query

Any help or pointers in the right direction will be much appreciated.

Look at doing a Data > Import External Data > Import Data and set the popup dialog to Text Files. Then continue with the wizard. Be sure to use the Properties at the end to set the refresh options if you want the text file data to update itself from time to time.

This process can be recorded into a macro to examine later for code you can tweak.

I have done this in the past using a form with a couple of textboxes for the connection and SQL strings, then I have Load and Save buttons to read write the SQL strings to text files like so:

Please Login or Register  to view this content.