Excel开发VBA学习

1.在工具栏上显示开发选项

2.新建个Module就可以在几个sheet里共享变量

Public cn As New ADODB.Connection
Public strCn As String
Public strCom As String
Public rs As New ADODB.Recordset
Public Sub Init()
  Dim rs As New ADODB.Recordset
  strCn = "Provider=sqloledb;Database=testdb;Uid=sa;Pwd=password;data source=dbname"
  Set cn = Nothing
  cn = New ADODB.Connection
  cn.Open strCn
End Sub

Private Sub CommandButton1_Click()
 'o = MsgBox("B1:" & Range("b1").Text & Range("b1").Value, vbOKOnly, "test")
  Init
  strCom = "exec testsp 2"
  rs.Open strCom, cn, adOpenDynamic, adLockBatchOptimistic
  Sheet1.Cells(1, 1).CopyFromRecordset rs
cn.Close
End Sub

3.添加下拉列表选项

ComboBox1.Clear
For Each testList In Sheet2.Rows 'Sheet2.Range("A1", "A6")
    With ComboBox1
      .AddItem testList.Value
    End With
Next testList

4.在另一台电脑上，发现上面的连接数据库会发生错误

在open语句时总是提示说连接已经结束，后来将连接字符串改了下又可以

Public strConn As String, strSQL As String
Public conn As ADODB.Connection
Public Sub Init()
    Set conn = New ADODB.Connection
    '连接数据库的字符串
    strConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=pwd;Initial Catalog=DBname;Data Source=DBServer;Connect Timeout=720; "
    '打开数据库连接
    conn.Open strConn
     '该句和数据库连接字符串处的Connect Timeout=720，表示说如果语句运行时间很长，这两句可以延长vba的等待时间，没有这两句，vba往往会报查询超时。
    conn.CommandTimeout = 720
End Sub

普通查询语句没问题，可是执行存储过程，稍微复杂一点的就会报错，应用程序定义或对象定义错误，后来才发现是存储过程必须加上这么一句

感谢万能的百度！
SET NOCOUNT ON