I would like to take inputs from a table in Excel to perform SQL query and output it to a location in the Excel sheet.
Macro:
Sub SQL(Dim strSQL as String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
rs.Open strSQL, cn
Debug.Print rs.GetString
End Sub
SQL Query:
SELECT
e.`isin`,
b.*
FROM
`risk_reference`.`basket_debt` AS b
JOIN `risk_reference`.`etp` AS e
ON IF(
e.`parentid_override` > 0,
e.`parentid_override`,
e.`parentid`
) = b.`parentid`
AND e.`isin` = ?
AND e.`date_out` IS NULL
WHERE b.`processing_date` IN
(SELECT
MAX(processing_date)
FROM
`risk_reference`.`basket_debt`)
GROUP BY e.`isin`, b.`pk_id`
The ? in the SQL query is where I need a list of items to go in based on a table in Excel.
Should i do SQL query item by item or can i do it in one shot? If so, how do i do it? If I do line by line, how do I get to output one location in the excel?
Need some advise.