Return count from SQL to Excel 1

[UnsolvedCoding]

I am stumbling around with this and I think its simply from not having done it for so long.

I have code that runs in SQL and will return the number of new rows that were loaded into the table, however I want to populate the count of new rows into a cell in Excel.

This code is used to show the count in SQL

Set @UPload_Count = @@rowcount

Select @Upload_Count


How do I open the record set in Excel to return the variable?

Would this work?

Range("O34").Value = oRs("@Upload_Count")

 

[PWise]

look @ excell's copyformrecordset method

 

[UnsolvedCoding]

Didn't help

 

[PWise]

how are you trying to import this information to excell?

VB ect

 

[UnsolvedCoding]

This is what I have in VBA

Option Explicit

Sub Update_System()

sSQL = "Execute dbo.Comment_Update"

' Create the connection to SQL
SQL_CONNECTION

If Not oRs.EOF Then

Range("O34").Value = oRs("Upload_Count")

oRs.MoveNext

End If

Set oRs = Nothing

End Sub


This is the SQL

--total # of cmt going to be added
Select Nbr, Agrmnt_Nbr, *
From VI_Comments
Where Switch = 0
Order By Comment_Date

Set @UPload_Count = @@rowcount
Select @Upload_Count

 

[UnsolvedCoding]

I tried .recordcount and that didnt' work

 

[PWise]

how do you execute the stored procedure

where do you open the oRs recordset

 

[UnsolvedCoding]

These are the two subs - the first one calls SQL connection. SQL Connection is where the connection and recordset are established.


Sub Update_System()

' Just in case something goes wrong
On Error GoTo Error_Handler

Dim K As Integer

Application.StatusBar = "Connecting to SQL"

sSQL = "Execute dbo.Comment_Update"

' Create the connection to SQL
SQL_CONNECTION

'Set the starting row
K = 34

Do

' Check if the cell is blank
If Trim(Range("O" & K).Value) = "" Then


'' Want the count to be put in this cell
'' Enter the total comments loaded into the cell
'Range("O" & K)

Exit Do

Else

' Go down one row
K = K + 1

End If

Loop

Set oRs = Nothing
Set oConn = Nothing

Application.StatusBar = False

Exit Sub

Error_Handler:

' Tell the user what went wrong
Application.StatusBar = "Update_System failed. " & Err.Description

MsgBox Err.Description & vbCrLf & "See Status Bar", vbOKOnly + vbCritical, "Error"

End Sub



sConn holds the database path and is declared as a public. I know the path is correct because other subs use the same path to pull different SQL information.


Sub SQL_CONNECTION()

On Error GoTo Error_Handler

''Application.StatusBar = "Connecting to SQL"

' Create the connection to SQL
Set oConn = New ADODB.Connection

With oConn
.ConnectionString = sCONN
.ConnectionTimeout = 0
.CommandTimeout = 0
.CursorLocation = 3
.Open
End With

Set oRs = New ADODB.Recordset

oRs.Open sSQL, oConn, 3, 1, 1

''Application.StatusBar = False

Exit Sub

Error_Handler:

Set oRs = Nothing
Set oConn = Nothing

' Tell the user what went wrong
Application.StatusBar = "SQL_Connection failed. " & Err.Description

MsgBox Err.Description & vbCrLf & "See Status Bar", vbOKOnly + vbCritical, "Error"

End Sub

 

[PWise]

Try
Change Sql to

[b]Set no count on[/b]
Select Nbr, Agrmnt_Nbr, *
From VI_Comments
Where Switch = 0
Order By Comment_Date

Set @UPload_Count = @@rowcount
Select @Upload_Count

[b]set ncount off[/b]

    ' Check if the cell is blank
    If Trim(Range("O" & K).Value) = "" Then
        

        '' Want the count to be put in this cell  
        '' Enter the total comments loaded into the cell
        'Range("O" & K)
    
        Exit Do
        
    Else
    
        ' Go down one row
        K = K + 1
        
    End If

Loop
[B]
Range("O" & K)CopyFromRecordset ors.nextrecordset[/b]
Set oRs = Nothing
Set oConn = Nothing

 

[UnsolvedCoding]

Thanks for the help. I got it to work and now we have to make changes to the stored procedure...