1.设置引用

2.连接模块代码编写

Option Explicit
'IP
Public Const strIP       As String = "172.20.2.49"
'用户名
Public Const strUser      As String = "admin"
'密码
Public Const strPasswd    As String = "TREchina0"
'数据库名称
Public Const strDB        As String = "StorePL"
Public strSQL           As String
Public rst              As New ADODB.Recordset
Public Conn             As New ADODB.Connection
Public Sub cnOpen()
On Error GoTo ERR_ConDwh
   If Conn Is Nothing Then
        Set Conn = New ADODB.Connection
   End If
    If Conn.State = 0 Then
        With Conn
            .Provider = "SQLOLEDB.1"
            .ConnectionString = "Persist Security Info=True;" & _
                                "Data Source=" & strIP & ";" & _
                                "Initial Catalog=" & strDB & ";" & _
                                "User ID=" & strUser & ";" & _
                                "Password=" & strPasswd
            .ConnectionTimeout = 10
            .CommandTimeout = 0
            .CursorLocation = adUseClient
            .Open
         End With
    End If
    Exit Sub
ERR_ConDwh:
    MsgBox "无法接入数据库"
    End
End Sub
Public Sub cnClose()
Conn.Close
End Sub

3.使用

                Private Sub UpdateButton_Click()
        ModDBConnect.cnOpen

        strSQL = "exec F_STORE_DataImport '" & Range("C" & r).Value & "','" & FilePath & "'"

        rst.Open strSQL, Conn

        strSQL = "exec F_STORE_DataDistribute '" & Range("C" & r).Value & "'"

        rst.Open strSQL, Conn

        ModDBConnect.cnClose
        End Sub

BCP storepl.dbo.BizDocDetail_T in ‘+ ‘\172.20.1.43\青島共通\002.業務関係\PL_TEST’ + ‘\bizdata\’ + ‘BizDocData‘+CONVERT(varchar(10),@CmdDate,112)+’.tsv’ + ‘ -S172.20.2.49 -Uadmin -PTREchina0 -t \t -c -F2’

Range(“A2”).CopyFromRecordset rst