Build Database Query With Parameters Using Excel Vba
Rate this:
(3/5 from 6 votes)
You can dynamically build the query used by MSQuery using a VBA macro. If, for example, you had an Excel spreadsheet with the following information entered. Excel Spreadsheet:
A
B
C
D
1
2
Sales Rep:
3
3
Sales Date:
01/01/2006
4
5
You can then create a VBA macro which pulls the values in cells C2 and C3 to return sales starting in cell B5. The following example uses the Northwind DNS to get sales from the Order table, so you will have to change the connection, query and other options to suit your own purposes.
VBA Code:
Sub Sales_Query() Dim salesrep As Variant Dim salesdate As Date salesrep = Range("C2").Text salesdate = Range("C3").Value With ActiveSheet.QueryTables.Add(Connection:=Array( _ "ODBC;DSN=Northwind;Description=Northwind;APP=Microsoft Office XP;DATABASE=Northwind;Trusted_Connection=YES"), Destination:=Range("B5")) .CommandText = Array("SELECT * FROM Orders WHERE EmployeeID=" & salesrep & " AND OrderDate > '" & salesdate & "' ORDER BY OrderDate") .Name = "Sales Query from Northwind" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub
To insert the macro, go to Tools >> Macro >> Visual Basic Editor and Insert Module. Once the code has been copied and modified, you can add a button or a graphic to assign the macro to.
