도도의 초록누리

Sub UploadFromExcelToSQL()

Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long

sConnString = "Provider=sqloledb;Server=servername;Database=NORTHWIND;User Id=xx;Password=password"

Set adoCN = CreateObject("ADODB.Connection")

'adoCN.Open sConnString

'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.


For lRow = 2 To 11

    sSQL = "INSERT INTO YOUR_TABLE (FIELD1, FIELD2, FIELD3) " & _
            " VALUES (" & _
            "'" & Sheet1.Cells(lRow, 1) & "', " & _
            "'" & Sheet1.Cells(lRow, 2) & "', " & _
            "'" & Sheet1.Cells(lRow, 3) & "')"
       
    adoCN.Execute sSQL
   
       
Next lRow

adoCN.Close

Set adoCN = Nothing
 
End Sub