彰化一整天的論壇

 找回密碼
 立即註冊
查看: 966|回復: 7

excel vba 可否用 insert插入資料

[複製鏈接]
發表於 2017-3-13 22:34:42 | 顯示全部樓層 |閱讀模式
本帖最後由 goodnight 於 2017-3-13 23:05 編輯

請問老師

我試著以下列vba想要新增一筆資料到 excel 2007 工作表, 但卻在紅字處指令(rs.Open sqlcmd, cn)出現下列錯誤訊息
執行階段錯誤'-2147467259 *80004005)'
運作必須使用更新查詢

我不確定 INSERT INTO 的語法是正確的,或許是語法錯誤的原故
可否請老師幫忙除錯一下?

VBA
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String

    strFile = Excel.ActiveWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   cn.Open strCon
    sqlcmd = "insert into [工作表1$] (工號, 姓名) VALUES ('0145', '李四')"
    rs.Open sqlcmd, cn
    Set cn = Nothing
    Set rs = Nothing

活頁簿2.xlsm (17.54 KB, 下載次數: 140)
回復

使用道具 舉報

發表於 2017-3-13 22:57:56 | 顯示全部樓層
您好,
     是否可以上傳範例excel檔案,只有看語法我不知道您的問題在那?
回復 支持 反對

使用道具 舉報

 樓主| 發表於 2017-3-13 23:07:48 | 顯示全部樓層
imingho 發表於 2017-3-13 22:57
您好,
     是否可以上傳範例excel檔案,只有看語法我不知道您的問題在那?

老師我上傳了

另外我把 rs.open 改成

    cn.Execute sqlcmd

我的參考資料: //stackoverflow.com/questions/32079587/adodb-excel-sql-statement-to-write-to-next-available-row
回復 支持 反對

使用道具 舉報

發表於 2017-3-14 06:57:15 | 顯示全部樓層
goodnight 發表於 2017-3-13 23:07
老師我上傳了

另外我把 rs.open 改成

您好,
     我試了.語法沒有錯,目前還找不到是什麼問題造成會出現更新查詢.
回復 支持 反對

使用道具 舉報

 樓主| 發表於 2017-3-14 10:18:24 | 顯示全部樓層
本帖最後由 goodnight 於 2017-3-14 10:33 編輯

老師, 先請教一下, 不同版本的 excel 在連接字串裡的版本是否也要隨著版本的不同而修改??
例如 exce 2007 是 12.0
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;"
excel 2010 是 14.0
strCon = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & strFile & ";Extended Properties=""Excel 14.0;HDR=Yes;"
回復 支持 反對

使用道具 舉報

 樓主| 發表於 2017-3-14 11:04:35 | 顯示全部樓層
報告老師, 我找到原因了

這個語法有問題, 不知道是不是因為是要新增, 所以正確的語法如下 (要寫入Excel, 不可以加HDR=Yes;IMEX=1)
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=Excel 12.0;"

原來的語法有錯誤, 我把錯的地方標紅色
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

另外因為 IMEX=1  變成唯讀了, 但也不能 IMEX=0 或 2, 而HDR 就更奇怪了, HDR=YES 表示有標題, 但奇怪的是 INSERT INTO 竟然也是不能出現 HDR 參數
只要出現 IMEX 或 HDR 其中一個參數, 就會出現 "找不到可安裝的 ISAM" 的錯誤訊息

我查了一下網路上 IMEX 的說明如下, 但若不設定 IMEX 的話, IMEX 會有什麼預設值呢??
IMEX=0 時為“匯出模式”,這個模式開啟的Excel 檔案只能用來做“寫入”用途。IMEX=1 時為“匯入模式”,這個模式開啟的Excel 檔案只能用來做“讀取”用途。
IMEX=2 時為“連結模式”,這個模式開啟的Excel 檔案可同時支援“讀取”與“寫入”用途。

以上向老師報告, 如果老師能有更好的說明, 就更好了


回復 支持 反對

使用道具 舉報

發表於 2017-3-14 11:12:05 | 顯示全部樓層
goodnight 發表於 2017-3-14 10:18
老師, 先請教一下, 不同版本的 excel 在連接字串裡的版本是否也要隨著版本的不同而修改??
例如 exce 2007  ...

您好,
     excel2003以後的檔案有xlsx,所以您可以用excel的版本來判斷要用那一個連接字串.
  1.     If Int(Excel.Application.Version) <= 11 Then 'excel 2003
  2.         strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  3.     Else
  4.         strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  5.     End If
複製代碼
回復 支持 反對

使用道具 舉報

發表於 2017-3-14 11:13:20 | 顯示全部樓層
goodnight 發表於 2017-3-14 11:04
報告老師, 我找到原因了

這個語法有問題, 不知道是不是因為是要新增, 所以正確的語法如下 (要寫入Excel,  ...

謝謝您,我研究看看,若有好的說明,再跟您說.
回復 支持 反對

使用道具 舉報

您需要登錄後才可以回帖 登錄 | 立即註冊

本版積分規則

 ㄚ母滴雞湯
 員林香純滴雞精

Archiver|手機版|小黑屋|彰化一整天的論壇(Excel,Office)  |网站地图

GMT+8, 2019-9-17 10:30 , Processed in 0.146231 second(s), 23 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表