vba help - pass variable into a Query

[Mallesh23]

Hi Team,

How to pass variable into a SQL Query Dynamically. Below code works trying to make it dynamic.
Below are Variable list needs to pass into a bolded value.

ShtName
hdr
st
ct

Sql = "Select Sum(Weekly) from [Sheet1$] Where (State = ""Maharashtra"" and City = ""Pune"")"

Sub CopyData()
    Dim Conn As New ADODB.Connection
    Dim Rst As ADODB.Recordset
    
    FilePath = ThisWorkbook.FullName
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & _
                    ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
    
    Conn.Open connstr
    Dim hdr As String
    'hdr = "State"
    Dim cnt As Long
    Dim st As String
    Dim ct As String
    
    Dim sh As Worksheet
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    
    
    Dim arr As Variant
    arr = Array("Weekly", "Monthly")
    st = "Maharashtra"
    ct = "Pune"
    
     'ShtName = "Sheet1"
    'Rst.Open "[" & ShtName & "$]", Conn
    
    
    For I = LBound(arr) To UBound(arr)
        hdr = arr(I)
        MsgBox hdr
        
        'I get correct output with this Query.
    Sql = "Select Sum(Weekly) from [Sheet1$] Where (State = ""Maharashtra"" and City = ""Pune"")"
    
    Set Rst = New ADODB.Recordset
      Rst.Open Sql, Conn
        cnt = Rst.Fields(0).Value
        MsgBox cnt
        
        On Error Resume Next
            Rst.Close
            Set Rst = Nothing
        On Error GoTo 0
            
   
    Next I
    

End Sub

Actual Data

State	City	Weekly	Monthly
Maharashtra	Pune	100	200
Maharashtra	Pune	200	400
Maharashtra	Nagpur	300	600
Gujarat	Ahmedabad	400	800
Gujarat	Surat	500	1000
Karnataka	Bangalore	600	1200
Delhi	New Delhi	800	1600

Thanks
mg

 

[Mallesh23]

Hi Team,

Variable in where clause working, But its not working for header name and Sheet Name.

'This is working
   'Sql = "Select Sum(Weekly) from [Sheet1$] Where (State = '" & st & "' and City = '" & ct & "')"  
        
    'this is not working
    Sql = "Select Sum([B]""" & hdr & """[/B]) from [Sheet1$] Where (State = '" & st & "' and City = '" & ct & "')"
        
    'This is not working
    'Sql = "Select Sum(Weekly) from [[B]""" & ShtName & """[/B]] Where (State = '" & st & "' and City = '" & ct & "')"

Thanks
mg

 

[FormR]

Hi, this sets the "sql" variable to the same string as the working example.

Sql = "Select Sum(" & hdr & ") from [Sheet1$] Where (State = """ & st & """ and City = """ & ct & """)"

 

[Mallesh23]

Hi FormR.

Amazing ! it worked ! ?


Thanks
mg