Excel 2010 vs Excel 2013 VBA存储过程
问题描述:
我有一个电子表格来调用SQL Server存储过程并返回数据。在Excel 2010中工作100%,并且已经完成数月。但是,对于Excel 2013中的某些用户,它不会返回所有数据 - 它只会通过0来返回。这里是我的代码:Excel 2010 vs Excel 2013 VBA存储过程
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
它必须是在VBA中的东西。数据存在于SQL和Excel 2010中 - 任何想法?
答
我解决了它。我从文本框中获取一个值。在Excel 2010中我只是用下面的:
如果范围( “$ M $ 1”)= TRUE然后
然而,在Excel 2013会出现它,你还需要引用工作表,以及:
如果表格(“Home”)。Range(“$ M $ 1”)= True然后
无论如何,包括表格名称现在意味着它的工作原理。
是否有任何错误信息? –
不 - 无 - 它只是不返回所有的数据 - 非常奇怪 – Michael
检查我[过去的回答](https://*.com/questions/28677262/recordset-closed-after-stored-procedure-execution)。您可能会发现有关SP的非常有用的信息。 –