This post will introduce you to the concept of QueryTables in Excel VBA. QueryTables is an object that allow you to import data from external sources, such as databases, web pages, and text files, directly into your Excel workbook.
In this post, we will discuss how to create, refresh, and delete QueryTables using VBA code, and we’ll provide examples of how to use QueryTables with different types of data sources.
Creating a New QueryTable
If you want to create a new QueryTable, you can use the Add method of the QueryTables collection of a Worksheet object.
The Add method has several parameters that you can use to customize the QueryTable, such as the Connection parameter, which specifies the data source, and the Destination parameter, which specifies the range of cells where the data should be imported.
Here’s an example of how to create a new QueryTable in VBA:
Sub ImportData_excelgeek()
Dim myquerytable As QueryTable
Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
End Sub
In this example, the Connection parameter specifies an ODBC connection to a database, and the Destination parameter specifies that the imported data should be placed in cell A1 of the current worksheet.
Specifying Data Source
The Connection parameter is used to specify the data source for the QueryTable. The syntax of the Connection parameter depends on the type of data source you are using.
If you want to import data from a web page, you can use the following connection parameter:
Connection:="URL;http://www.excelgeek.net/mycontent.html"
If you want to import data from a text file, you can use the following Connection syntax:
Connection:="TEXT;C:\Folder\MyTextFile.txt"
To import data from a database, you can use the following syntax:
Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password"
In this example, the ODBC connection uses the Data Source Name (DSN) ” DatabaseName “, the username ” Username “, and the password ” Password “.
Refreshing the QueryTable
Once you have created a QueryTable, you can refresh it by calling the Refresh method of the QueryTable object.
Here’s an example of how to refresh a QueryTable:
Sub RefreshData_excelgeek()
Dim myquerytable As QueryTable
Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
myquerytable.Refresh
End Sub
In this example, the RefreshData_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Refresh method of the QueryTable to refresh the data.
Cleaning up QueryTable
After you have finished using a QueryTable, you should clean it up by calling the Delete method of the QueryTable object.
Here’s an example of how to delete a QueryTable:
Sub Cleanup_Data_excelgeek()
Dim myquerytable As QueryTable
Set myquerytable = ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DatabaseName;UID=Username;PWD=Password", Destination:=Range("A1"))
myquerytable.Refresh
myquerytable.Delete
End Sub
In this example, the Cleanup_Data_excelgeek subroutine gets a reference to the first QueryTable in the active sheet’s QueryTables collection, and then calls the Delete method of the QueryTable to delete it.
