VBA Dynamic WHERE clause in a SQL statement

[Shadow777]

Hi,

I'm trying to connect to an SQLite database that contains financial information about various companies. The code will then put the information into a worksheet for me. Currently I am having to hard code the symbol of the company that I am wanting to search in the database for. This works, and returns the headers and correct data (although it doesn't return the actual symbol for some reason).

I want to make it so that I can filter a dropdown and the code will reference the particular symbol that I have selected and search for that. I have attempted this and have managed to get it so that no errors are produced, however it will only get the headers but fails to get any data.

I have set the named cell as M1 on Sheet2.

Sub GetData()

Dim conn As ADODB.Connection
Dim TestData As ADODB.Recordset
Dim icols As Integer

Dim code As Range

Set code = Worksheets("Sheet2").Range("M1")
Dim r As Variant
r = code.Value

Set conn = New ADODB.Connection
Set TestData = New ADODB.Recordset

conn.Open "DRIVER=SQLITE3 ODBC DRIVER;DATABASE=C:\Users\shadow\financials.db;"

strSQL = "SELECT * FROM financials WHERE Ticker= 'r'"

TestData.Open strSQL, conn

For icols = 0 To TestData.Fields.Count - 1
    Worksheets("Sheet2").Cells(1, icols + 1).Value = TestData.Fields(icols).Name
Next icols

Worksheets("Sheet2").Range("A2").CopyFromRecordset TestData
TestData.Close

Set TestData = Nothing
Set conn = Nothing

End Sub

 

[mole999]

what sort of things is r supposed to hold

 

[Shadow777]

what sort of things is r supposed to hold

Hi, I'm not sure how to close the thread, but I figured it out. I just needed to use single quotes around the ""

 

[mole999]

threads all stay live, glad you worked it out