Excel 2010 vs Excel 2013 VBA Stored Procedure

Question

I have a spreadsheet whereby it calls a SQL Server Stored procedure and returns data. Works 100% in Excel 2010 and has done for months. However, for some users on Excel 2013, it does not return all the data - it just brings through 0's. Here is my code:

Sub Button10_Click()
    Set con = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.RecordSet

    Sheets("Customer").Visible = True

    'Unprotect worksheet
    Worksheets("Customer").Unprotect Password:="*******"

    ' remove and re-add auto filter
    Sheets("Customer").AutoFilterMode = False
    Sheets("Customer").Range("A4:AO4").AutoFilter

    Application.DisplayStatusBar = True
    Application.StatusBar = "Contacting SQL Server..."

    ' Remove any values in the cells where we want to put our Stored Procedure's results.
    With Sheets("Customer")
        .Rows(5 & ":" & .Rows.Count).Delete
    End With

    ' Log into our SQL Server, and run the Stored Procedure
    con.Open "Provider=SQLOLEDB;Data Source=*********;Initial Catalog=**********;Integrated Security=SSPI;Trusted_Connection=Yes;"
    cmd.ActiveConnection = con

    Application.StatusBar = "Running stored procedure..."

    '===== Check if the user has ticked the 'Include Current Month Totals checkbox ====
    If Range("$M$1") = True Then
        'include current month in totals
        cmd.CommandText = "usp_Customer_Lvl1_Current_Month_INC"    
    Else
        'do not include current month in totals
        cmd.CommandText = "usp_Customer_Lvl1_Previous_Month_INC"
    End If

    Set rs = cmd.Execute(, , adCmdStoredProc)

    '==========
    Sheets("Customer").Range("A5").CopyFromRecordset rs
    Application.Goto Reference:=Worksheets("Customer").Range("A5")

    'protect worksheet
    Worksheets("Customer").Protect Password:="***********", AllowFormattingCells:=True, AllowFiltering:=True

    '=============

    rs.Close
    Set rs = Nothing
    Set cmd = Nothing

    con.Close
    Set con = Nothing

    Application.StatusBar = "Data successfully updated."
End Sub

It has to be something in the VBA. The data is there in SQL and Excel 2010 - any ideas?

no - none - it just doesn't return all the data - very bizarre — Michael, Jun 13 '17 at 14:52
Check my [past answer](https://stackoverflow.com/questions/28677262/recordset-closed-after-stored-procedure-execution). There you may find very useful information about SP. — Maciej Los, Jun 13 '17 at 14:59
SET NO COUNT wasn't present in the SP - I added this and it made no difference. What do you mean by "you're working on variable of type: table"? — Michael, Jun 13 '17 at 15:20
Ignore this part of my past answer. If you've added `SET NOCOUNT ON` and this changed nothing, i've no idea what can be wrong. — Maciej Los, Jun 13 '17 at 15:23
Please post a sample of return data of stored proc. How many rows? Columns? Also, describe the schema of data types: does proc use `TEXT` types? `DOUBLE` with max digits? — Parfait, Jun 13 '17 at 15:32
Another idea is to replace `ADODB.Command` with `ADODB.Recordset` by using [Open method](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-recordset). Do not forget to pass `adOpenStatic` as `CursorType` parameter and `adLockReadOnly` as `LockType` parameter. Please, let me know if it helps. Use my login (in convention: `@MyLogin`) on the beginning of message to be sure that i'll be notified about your reply. — Maciej Los, Jun 13 '17 at 15:55

score 0 · Answer 1 · answered Jun 13 '17 at 16:00

I worked it out. I take a value from a textbox. In Excel 2010 I just use the below:

If Range("$M$1") = True Then

However, in Excel 2013 it appears you also need to reference the worksheet as well:

If Sheets("Home").Range("$M$1") = True Then

Anyway, including the sheet name now means it works.

Excel 2010 vs Excel 2013 VBA Stored Procedure

1 Answers1