Home > Forum Home > Automating Data Analysis Tasks > Send variables to MSQuery via entry in Excel cell range? Share

Send variables to MSQuery via entry in Excel cell range?

Excel Help for Send Variables To Msquery Via Entry In Excel Cell Range? in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Send Variables To Msquery Via Entry In Excel Cell Range?

Rate this:
(3/5 from 1 vote)
ShockedI do a lot of data query with Excel, and am running into projects where it is impractical to continue because of query variables that change.  Currently this requires that I open the query, manually chance the variable/criteria, refresh, return to Excel, print, then start over again!

Specifically I'm pulling information for sales usage by a specific salesperson and date range.  If there are 40 salespersons, this requires editing the MSQuery 40 times to get the data!

What I'd like to do is build the Excel report to include an input cell or two (today's date (via formula) and sales ID), have that data pass directly to MSQuery and return the updated data - without having to open MSQuery.

Possible?

Thanks!
 Posted by on
 
Replies - Displaying 11 to 12 of 12Order Replies By: Most recent | Chronological | Highest Rated
Surprised
Please help!
Rate this:
(3.5/5 from 2 votes)
I get the following error message when using the code above as a template.

     
Run-time error '1004'
General ODBC Error

With the following line highlighted

.Refresh BackgroundQuery:=False

From google, looks like everyone is looking for a solution to this problem.

Can someone please help!?

Thanks!


 Posted by on
Happy
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:
 ABCD
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.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 2 of 2 

Find relevant Excel templates and add-ins for Send variables to MSQuery via entry in Excel cell range? in the

© Business Spreadsheets   Legal   Follow us: