vba help - take count from Recordset into variable

[Mallesh23]

Hi Team,

I am tyring to Filter the data via sql Query , and take the count and store into variable.
Below is the code I am trying which works but store output into Range("a2")

Sub CopyData()
    Dim Conn As New ADODB.Connection
    Dim Rst As New 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  Cnt as long
    
   ' Sql = "Select * from [Sheet1$] order by State Desc"   
   'Sql = "Select State,city from [Sheet1$] "   
  Sql = "SELECT COUNT(State)FROM [Sheet1$] where State = ""Maharashtra"""

    
    Rst.Open Sql, Conn
    
    MsgBox Rst.RecordCount   'Getting -1 as recordset
    
    Sheets.Add
    
    Range("A2").CopyFromRecordset Rst   'Getting 3 as Correct output in Range "A2" 
           

End Sub

Below is the table

State	City
Maharashtra	Pune
Maharashtra	Mumbai
Maharashtra	Nagpur
Gujarat	Ahmedabad
Gujarat	Surat
Karnataka	Bangalore

Thanks
mg

 

[Norie]

Try this.

lngCount = Rst.Fields(0).Value

 

[Mallesh23]

Hi Norie,

it worked ! thanks a lot!

one more query...... Can I replace Fields(0) with Actual header name.?. to get same output.


lngCount = Rst.Fields(0).Value


Thanks
mg

 

[Norie]

What would you change it to? You aren't specifying an alias for COUNT(State) in the query.