Parameters in Excel External Data Union Query

Question

I have two queries that pull data from SQL Server into Excel. Both work perfectly fine. I tried to Union them together and pass in a parameter as a date, and now nothing works. Here's a link to an article that describes how to use Microsoft Query, and pass in a parameter.

http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

All I want to do is get this working with a Union Query. Is that possible? Or, do I need a VBA solution to achieve this. I'm sure it's do-able, I just don't know exactly how to do it. I'd appreciate any suggestions.

Thanks!

Answer 1

Thanks everyone. I ended up doing it in VBA.

Sub ImportFromDB()

' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=SERVER_NAME;INITIAL CATALOG=Data_Base;"

'Use an integrated login.
strConn = strConn & "Trusted_Connection=Yes;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Set sht = Worksheets("Impact Analysis")

    With sht
        .Range("N:U").ClearContents
        .Range("N1").Resize(1, 8).Value = Array("CONTACT_ID", "CATEGORY", "COMPANY_CODE", "CUSTOMER_NO", "SECTOR", "DES", "ASOFDATE", "BALANCE")
        Set rw = .Rows(2)
    End With

    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open "SELECT * FROM MY_TABLE"

        ' Copy the records into cell A1 on Sheet1.
        Worksheets("Impact Analysis").Range("N2").CopyFromRecordset rsPubs

        ' Tidy up
        .Close
    End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub