vba help - sql query not working for headers which begin with numbers.

[Mallesh23]

Hi Team,

SQL Query not working for headers which starts with Numbers and backward slashes.
arr = Array("Weekly", "Monthly", "1Yr", "O/W")

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 ShtName As String
   
    Dim sh As Worksheet
    Set sht = ThisWorkbook.Worksheets("Sheet1")
   
    ShtName = sht.Name
   
    Dim arr As Variant
    arr = Array("Weekly", "Monthly", "1Yr", "O/W")
    st = "Maharashtra"
    ct = "Pune"
   
    For I = LBound(arr) To UBound(arr)
        hdr = arr(I)
        MsgBox hdr
  
   Sql = "Select Sum(" & hdr & ") from [" & sht.Name & "$] Where (State = """ & st & """ and City = """ & ct & """)"
          
    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

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

Thanks
mg

 

[HaHoBe]

Hi Mallesh23,

maybe this codeline will work for you

   SQL = "Select Sum([" & hdr & "]) from [" & sht.Name & "$] Where (State = """ & st & """ and City = """ & ct & """)"

(additional square brackets around header).

Ciao,
Holger

 

[Mallesh23]

Hi, HaHoBe,

it worked ! millions of thanks. ?

Can I add Range also after sht.Name . I tried below code still its working.

Sql = "Select Sum([" & hdr & "]) from [" & sht.Name & "$A1:F8] Where (State = """ & st & """ and City = """ & ct & """)"



Thanks
mg

 

[HaHoBe]

Hi Mallesh23,

it works, I think we got that sorted out in another thread.

I just got dizzy with clicking on all the MsgBoxes popping up. Maybe consider to change the code a bit and look at the results in the Immediate Window

    Dim strMsg As String
    For i = LBound(arr) To UBound(arr)
      hdr = arr(i)
      strMsg = "State:  " & st & vbCrLf & "City:   " & ct & vbCrLf & "Header: " & hdr
      SQL = "Select Sum([" & hdr & "]) from [" & sht.Name & "$A1:F9] Where (State = """ & st & """ and City = """ & ct & """)"
      Set Rst = New ADODB.Recordset
      Rst.Open SQL, Conn
      cnt = Rst.Fields(0).Value
      Debug.Print strMsg & vbCrLf & "Sum:    " & Format(CDbl(cnt), "#,#00")
      Debug.Print vbNullString
         
      On Error Resume Next
      Rst.Close
      Set Rst = Nothing
      On Error GoTo 0
    Next i

Ciao,
Holger

 

[Mallesh23]

HaHoBe,

Excellent ! thanks once again understood. ?


Thanks
mg