VBA excel使用SQL插入数据

qiao1019

Sub 插入()
Set con = CreateObject("adodb.connection")
Set wbsht = ThisWorkbook.Sheets(1)
con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;" _
& "data source=\\10.150.231.100\05_Share Folder\TCM_record.xlsx"

Sql = "insert into [Sheet1$a1:o] values (" & wbsht.Cells(2, 2) & ",2,3,4,5,6,7,8,9,10,11,12,13,14,15)"  '如果wbsht.Cells(2, 2)这个单元格里的是数字就不会报错正常添加进去了，但是文本的话就报错“至少一个参数没有被指定值”，加上单引号后又报“标准表达式中数据类型不匹配”
con.Execute Sql
End Sub

大佬们，求解！！！

Jday1218

Sub InsertData()
    Dim con As Object
    Dim wbsht As Worksheet
    Dim Sql As String
    Dim i As Integer
   
    Set con = CreateObject("adodb.connection")
    Set wbsht = ThisWorkbook.Sheets(1)
   
    con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;" _
    & "data source=\\10.150.231.100\05_Share Folder\TCM_record.xlsx"
   
    Sql = "INSERT INTO [Sheet1$a1:o] VALUES ("
   
    For i = 1 To 15
        If i > 1 Then
            Sql = Sql & ","
        End If
        If IsNumeric(wbsht.Cells(2, i).Value) Then
            Sql = Sql & wbsht.Cells(2, i).Value
        Else
            Sql = Sql & "'" & wbsht.Cells(2, i).Value & "'"
        End If
    Next i
   
    Sql = Sql & ")"
   
    con.Execute Sql
    con.Close
    Set con = Nothing
    Set wbsht = Nothing
End Sub

mmwwdd

附件发上来看一下

zjlive

Set Rst = CreateObject("ADODB.Recordset")
Set Rst = Conn.Execute(Sql)
.range("A1").CopyFromRecordset Rst