Use column heading and row label as sql query parameters for multiple cells

[TheCyrusVirus]

I currently have a worksheet (attached) where I need to pass some parameters to a SQL query.

For each cell where I have to retrieve a value I need to pass the date range and row label to a sql query.

So for example for C3:

SELECT totalsales From tblSales WHERE SeasonCode = "text value from A3" AND Date BETWEEN "value from C2"

The result of this query needs to then be inserted into C3

Ideally I need to then loop through each cell in this row up until the last column of data (in this case column Q) but number of columns will vary.

I then need to do the same for each row where there is a season code

I cant for the life of me figure out a neat way of doing this and would really appreciate any help that anyone can offer.

[TheCyrusVirus]

Anyone got any ideas on this one, pretty please!

[PingPing]

You could use a nested loop, ie. one For...Next loop inside another For...Next loop.
For example the inner loop would move across the columns running the SQL query for each BETWEEN condition, while the outer loop would move down the rows for each season code.

To get your upper bounds for each loop, you could use the Count property of the Range object like so:

For the # of columns: Range("C2", Range("C2").End(xlToRight)).Count
For the # of rows: Range("B3", Range("B3").End(xlDown)).Count

For the SQL code you could use the CopyFromRecordset method of the Range object. For example, if your SQL query was returned to a recordset called rstSQL then you would have:

Range("C3").CopyFromRecordset rstSQL

Of course you'd replace the "C3" with your loop variables using the Cells object:

Range(Cells(lngRowCount, lngColCount), Cells(lngRowCount, lngColCount)).CopyFromRecordset rstSQL

where lngRowCount and lngColCount are the row/column loop values (note that I've used longs instead of integers as Excel 2003+ has rows > 32767.

The above code is quite simple/rough but it should be enough to get you started.

[TheCyrusVirus]

Thanks very much PingPing, will have a play around with that today and see how far I get.

Will post back here if I get stuck again.

[TheCyrusVirus]

How do I reference the date range found in the relevant column header and the season code (from the start of the row) for the cell that is currently being processed in the loop as I need to pass these variables to the query.

How do I then insert the value returned by the query into the cell.

Below is the code I have so far which may be completely wrong.

Sub MultiSQL()

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rstSQL As ADODB.Recordset
    Dim sqlQRY As String
    Dim sqlCN As String
    Dim sCusName As String
    Dim sDateRange As String
    Dim sSCode As String
    Dim i As Long
        
    Dim colCount As Long
    colCount = Range("C3", Range("C3").End(xlToRight)).Count

    For i = 1 To colCount
    Set cnn = New ADODB.Connection
        sqlCN = "Provider=SQLOLEDB; Server=BBB-SQL; Database=BBB-LIVE; Trusted_Connection=yes;"
        cnn.Open sqlCN
           sqlQRY = "SELECT SUM of [salesamount] As 'Summed Amount' " _
            & "FROM [Value Entry] " _
            & "WHERE [Customer] = '" & sCusName & "' AND [Date] BETWEEN '" & sDateRng & "' " _
            & "AND [Season Code] = '" & sSCode & "' ;"
            
            Set rstSQL = New ADODB.Recordset
            rstSQL.Open sqlCN, cnn, adOpenDynamic
    Next i

[PingPing]

How do I reference the date range found in the relevant column header and the season code (from the start of the row) for the cell that is currently being processed in the loop

Use the Value property of the Cells object, eg.

sDateRng = Cells(2, lngColCount).Value
sSCode = Cells(lngRowCount,1).Value

How do I then insert the value returned by the query into the cell

Use the CopyFromRecordset method of the Range object:

Range(Cells(lngRowCount, lngColCount), Cells(lngRowCount, lngColCount)).CopyFromRecordset rstSQL

[TheCyrusVirus]

Thanks for all of your help on this pingping, I have made some progress with this but need to change the approach slightly so am going to start a new post.

Thanks again