Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub testexportsql()
- Dim Cn As ADODB.Connection
- Dim ServerName As String
- Dim DatabaseName As String
- Dim TableName As String
- Dim UserID As String
- Dim Password As String
- Dim rs As ADODB.Recordset
- Dim RowCounter As Long
- Dim NoOfFields As Integer
- Dim StartRow As Long
- Dim EndRow As Long
- Dim ColCounter As Integer
- Set rs = New ADODB.Recordset
- ServerName = "server_name" ' Enter your server name here
- DatabaseName = "db_name" ' Enter your database name here
- TableName = "customer_master" ' Enter your Table name here
- UserID = "" ' Enter your user ID here
- ' (Leave ID and Password blank if using windows Authentification")
- Password = "" ' Enter your password here
- NoOfFields = 331 ' Enter number of fields to update (eg. columns in your worksheet)
- StartRow = 2 ' Enter row in sheet to start reading records
- EndRow = 106695 ' Enter row of last record in sheet
- ' CHANGES
- Dim shtSheetToWork As Worksheet
- Set shtSheetToWork = ActiveWorkbook.Worksheets("customer_master")
- '********
- Set Cn = New ADODB.Connection
- Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
- ";Uid=" & UserID & ";Pwd=" & Password & ";"
- rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic
- 'EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
- For RowCounter = StartRow To EndRow
- rs.AddNew
- For ColCounter = 1 To NoOfFields
- 'On Error Resume Next
- rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
- ColCounter = ColCounter + 1
- Next ColCounter
- Debug.Print RowCounter
- Next RowCounter
- rs.UpdateBatch
- ' Tidy up
- rs.Close
- Set rs = Nothing
- Cn.Close
- Set Cn = Nothing
- End Sub
Add Comment
Please, Sign In to add comment