vba help - pass variable into a Query

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
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"")"

VBA Code:
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
StateCityWeeklyMonthly
MaharashtraPune100200
MaharashtraPune200400
MaharashtraNagpur300600
GujaratAhmedabad400800
GujaratSurat5001000
KarnatakaBangalore6001200
DelhiNew Delhi8001600



Thanks
mg
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

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

VBA Code:
'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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,567
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
Hi FormR.

Amazing ! it worked ! 🕺 (y)


Thanks
mg
 

Forum statistics

Threads
1,141,715
Messages
5,708,046
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top