Excel Database Query - Cell Reference

[pramsam1]

Hello:
I need to access SQL server data via database query from Excel. I would like to use some values in a cell (eg. a date) in the excel file. Can I write a query and pass the data in a cell in Excel as a parameter to the query ?

Much appreciated.

 

[SkipVought]

Hi,

Assuming that you already have created your QueryTable in a Sheet, turn on your macro recorder and record editing the query - ie open the QBE Editor and Return data to Excel.

You will have something that looks like...

Sub Macro1()
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = Array( _
        "SELECT AM_CC.CC, AM_CC.CC_DESCR, AM_CC.MANAGER, AM_CC.SUPERVISOR, AM_CC.COE" & Chr(13) & "" & Chr(10) & "FROM FPRPTSAR.AM_CC AM_CC" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub

If cell [A1] contains a parameter...

Sub Macro1()
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;"))
        .CommandText =  _
        "SELECT AM_CC.CC, AM_CC.CC_DESCR, AM_CC.MANAGER, AM_CC.SUPERVISOR, AM_CC.COE" & Chr(13) & "" & Chr(10) & "FROM FPRPTSAR.AM_CC AM_CC" & _ 
         [b]"Where AM_CC.CC='" & [A1] & "' "[/b]
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

_[red][/red]