1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
Module Module1
Sub Main()
Dim Cn As ADODB.Connection 'Declaration de la variable pour la connection a la BD
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 'permet de realiser les requetes SQL
Dim RowCounter As Long
Dim ColCounter As Integer
Dim NoOfFields As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim ws As Worksheet 'recuperer fichier Excel
rs = New ADODB.Recordset
ServerName = "sql"
DatabaseName = "KP"
TableName = "table_test"
UserID = "admin"
Password = ""
NoOfFields = 1
StartRow = 1
EndRow = 1
ws = Sheets.Worksheets("sheet1")
Cn = New ADODB.Connection
Cn.Open("Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";") 'ouvre la connection avec SQL-server
rs.Open(TableName, Cn)
For RowCounter = StartRow To EndRow 'pour le nombre de ligne definie
rs.AddNew()
For ColCounter = 1 To NoOfFields 'pour le nombre de colonne definie
rs(ColCounter) = ws.Cells(RowCounter, ColCounter)
Next ColCounter
Next RowCounter
rs.UpdateBatch()
rs.Close()
rs = Nothing
Cn.Close()
Cn = Nothing
End Sub
End Module |
