excel vaa sql 下面的代码我不知道错了哪里

孤梅独傲

Sub ceshi()
Set conn = CreateObject("adodb.connection") '创建ado对象
Set rst = CreateObject("ADODB.recordset") '创建记录集
Dim sql As String
conn.Open "provider=microsoft.ACE.Oledb.12.0;extended properties=excel 12.0;data source=C:\Users\Administrator\Desktop\Book2.xls"

sql = "insert into [sheet1$](商品编码) values('ceshi')"
conn.Execute (sql)


sql = "select * form [sheet1$]"
Set rst = conn.Execute(sql)
a = rst.getrows
conn.Close
End Sub

上面的语句能正常运行，断点处就出错了，再帮忙把断点处语句修改为只提取字段商品编码，excel版本是2003

爱疯

Option Explicit

Sub ceshi()
    Dim conn
    Dim rst
    Dim sql
    Dim i

    Set conn = CreateObject("adodb.connection")    '创建ado对象
    Set rst = CreateObject("ADODB.recordset")    '创建记录集

    conn.Open "provider=microsoft.ACE.Oledb.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.Path & "\Book2.xls"


    sql = "Select * from [Sheet1$]"
    '    sql = "insert into [sheet1$](商品编码) values('ceshi')"


    Set rst = conn.Execute(sql)
    Sheets(1).Select
    Cells.Clear
    For i = 0 To rst.Fields.Count - 1
        Cells(1, i + 1) = rst.Fields(i).Name
    Next i

    Range("A2").CopyFromRecordset rst
    'a = rst.getrows
    rst.Close
    conn.Close
End Sub





我很少用sql，这是照以前记的改的。
楼主自己看看

孤梅独傲

爱疯 发表于 2019-4-27 12:04
Option Explicit

Sub ceshi()

好吧我，from写错了