none
SQL Connection thr VBA RRS feed

  • Question

  • Hi,

     The below code works fine, but it execute without column heading and also the revenue is not summed.

    Could you please help me to modify the below code so that i will get column heading and sum the revenue (total is not sumedup)

    Sub iid()
        
        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim stSQL As String
        Dim wbBook As Workbook
        Dim wsSheet As Worksheet
        Dim rnStart As Range
        Dim iorderid As String
       
        iorderid = Sheets("Sheet1").Range("B3").Value
       
        Const stADO As String = "Provider=SQLOLEDB;Integrated Security=ssis;" & _
        "Persist Security Info=False;" & _
        "Initial Catalog=disks;" & _
        "Data Source= ServerName"
        
        
        Set wbBook = ActiveWorkbook
        Set wsSheet = wbBook.Worksheets(2)
        
        With wsSheet
            Set rnStart = .Range("A1")
        End With
        
        stSQL = "select id,date,sum(total)Revenue from tabel1 where id in (" & iorderid & ") group by id,date"
        
        Set cnt = New ADODB.Connection
        
        With cnt
            .CursorLocation = adUseClient
            .Open stADO
            .CommandTimeout = 0
            Set rst = .Execute(stSQL)
        End With
        
         'Here we add the Recordset to the sheet from A1
        rnStart.CopyFromRecordset rst
        
         'Cleaning up.
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
    End Sub

    Tuesday, October 2, 2012 5:53 PM

Answers

  • That's because I used rs insted of rst, sorry. Just change the part you get the compilation error for this:

        For lCol = 0 To rst.Fields.Count - 1
            rnStart.Offset(-1, lCol) = rst.Fields(lCol).Name
        Next lCol


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by 5Ant Wednesday, October 3, 2012 9:56 AM
    Wednesday, October 3, 2012 9:03 AM

All replies

  • What's the name of the heading that has the totals? Just put it in the sum(TotalFieldHere)

    As for your headings, try (I changed/added bold parts):

    Sub iid()
         
        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim stSQL As String
        Dim wbBook As Workbook
        Dim wsSheet As Worksheet
        Dim rnStart As Range
        Dim iorderid As String
        Dim lCol As Long
        
        iorderid = Sheets("Sheet1").Range("B3").Value
        
        Const stADO As String = "Provider=SQLOLEDB;Integrated Security=ssis;" & _
          "Persist Security Info=False;" & _
          "Initial Catalog=disks;" & _
          "Data Source= ServerName"
         
        Set wbBook = ActiveWorkbook
        Set wsSheet = wbBook.Worksheets(2)
         
        With wsSheet
            Set rnStart = .Range("A2")
        End With
         
        stSQL = "select id,date,sum(total)Revenue from tabel1 where id in (" & iorderid & ") group by id,date"
         
        Set cnt = New ADODB.Connection
         
        With cnt
            .CursorLocation = adUseClient
            .Open stADO
            .CommandTimeout = 0
            Set rst = .Execute(stSQL)
        End With
         
         'Here we add the Recordset to the A2 cell in the sheet
        rnStart.CopyFromRecordset rst
         
        For lCol = 0 To rs.Fields.Count - 1
            rnStart.Offset(-1, lCol) = rs.Fields(lCol).Name
        Next lCol
         
         'Cleaning up.
        rst.Close
        cnt.Close
         
    End Sub


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, October 2, 2012 9:13 PM
  • i got an error stating object required

    Please advise

    Wednesday, October 3, 2012 5:19 AM
  • That's because I used rs insted of rst, sorry. Just change the part you get the compilation error for this:

        For lCol = 0 To rst.Fields.Count - 1
            rnStart.Offset(-1, lCol) = rst.Fields(lCol).Name
        Next lCol


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marked as answer by 5Ant Wednesday, October 3, 2012 9:56 AM
    Wednesday, October 3, 2012 9:03 AM
  • thanks its working fine :)
    Wednesday, October 3, 2012 9:56 AM