Using an Excel cell in my SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Using an Excel cell in my SQL query

Author

Topic

donthaveascooby
Starting Member

1 Post

Posted - 2011-08-04 : 06:59:54

Hello,

Now this isnt strictly an SQL query, but im hoping someone has some ideas on how it can interact with Excel...I have a macro in Excel which connects up to sql server and pulls of a query, which is placed in the macro. However, id ideally like to be able to write in one of the cells, and use this cell in the where clause. I currently have the below, but im fairly new to all this and have had no luck finding a solution online! Any help appreciated!

-------------------
Sub ApplicantType()

Sheets("Sheet1").Select
Range("A4:Z62177").Select
Range("A1").Select
Dim connCCDW As ADODB.Connection
Set connCCDW = New ADODB.Connection

Dim strConn As String

strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=UKDCSQL02V1;UID=vision2user;PWD=muff1ns;DATABASE=Vision2899"

connCCDW.Open strConn

Dim rsCCDW As ADODB.Recordset
Set rsCCDW = New ADODB.Recordset

With rsCCDW

.ActiveConnection = connCCDW

.Open "SELECT count(*) AS [Count] ,ass.applicanttype " _
& "FROM assignments AS [ass] LEFT OUTER JOIN agencystaff ags ON ags.agencystaffid = ass.applicantid AND ass.applicanttype = 'A' " _
& "LEFT OUTER JOIN applicants ap ON ap.applicantid = ass.applicantid AND ass.applicanttype = 'M' " _
& "WHERE ass.startdate <= GETDATE() AND ass.enddate >= GETDATE() " _
& "AND ass.applicanttype = (cell name here somehow?!) " _
& "GROUP BY ass.applicanttype ORDER BY ass.applicanttype"

Sheet1.Range("C22").CopyFromRecordset rsCCDW

.Close

End With

connCCDW.Close
Set rsCCDW = Nothing
Set connCCDW = Nothing
End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources