Kết nối Excel với SQL Server

• Hàm SubString trong VBA

* Thêm thư viện vào file excel

 

* Ví dụ kết nối in ra 1 cột

Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT2\SQLEXPRESS;DATABASE=nhanvien;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandTimeout = 60
        .CommandText = "Select * From dbo.tbNhanVien"
     End With
     Dim prm As Object
     
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
    Set rcs = cmd.Execute
    
    Dim pos_row As Integer
    pos_row = 1
    
    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF        
           Cells(pos_row, 1) = rcs!HoTen
            rcs.MoveNext
            pos_row = pos_row + 1
        Wend
        Set rcs = rcs.NextRecordset
    Loop

* Kết nối in tất cả các cột file Excel từ dữ liệu SQL Server

 Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT2\SQLEXPRESS;DATABASE=nhanvien;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandTimeout = 60
        .CommandText = "Select * From dbo.tbNhanVien"
     End With
     Dim prm As Object
     
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
    Set rcs = cmd.Execute
    
    Dim pos_row As Integer
    pos_row = 1 ' vi tri dong bat dau in
    pos_col = 1 ' vi tri cot bat dau in
    
    ' In tieu de
    For i = 0 To rcs.Fields.Count - 1
        Cells(pos_row, i + pos_col) = rcs(i).Name
    Next i
    
    pos_row = pos_row + 1
    
    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF
            For i = 0 To rcs.Fields.Count - 1
                 Cells(pos_row, i + pos_col) = rcs(i)
            Next i
            rcs.MoveNext
            pos_row = pos_row + 1
        Wend
        Set rcs = rcs.NextRecordset
    Loop

Mời bạn xem video về lập trình VBA kết nối từ Excel đến SQL Server