Board logo

標題: [發問] VBA 用SQL 一次新增多筆資料至EXCEL的方法 [打印本頁]
作者: PKKO    時間: 2017-2-13 02:23     標題: VBA 用SQL 一次新增多筆資料至EXCEL的方法

小弟會用VBA+SQL連線至MSSQL 並且一次新增多筆資料,用;連接insert字串即可
但不曉得要如何連線至EXCEL之後一次新增多筆資料?

方法一:無效
strSQL = "INSERT INTO " & Table_name & " VALUES (" & Columns_name & ")"
strSQL2=strSQL & ";" & strSQL

方法二:無效
strSQL = "INSERT INTO " & Table_name & " VALUES (" & Columns_name & "),(" & Columns_name & ");"

求大大們協助 = =
作者: lpk187    時間: 2017-2-17 23:56

回復 1# PKKO


    剛好有看過一篇，但未曾實踐過，你可以試試
https://support.microsoft.com/en ... nto-microsoft-excel
作者: PKKO    時間: 2017-2-18 10:38

回復 2# lpk187


    可惜此篇尚未提到,感謝您!
作者: michael101772    時間: 2017-2-18 13:07

不太懂你的意思，你是要從Excel撈sql裡面的資料嗎？
這個用select就可以了。
作者: baomin    時間: 2017-2-18 13:40

回復 1# PKKO

你好
        strSQL = "UPDATE PURTD1008 set TD012='" & Range("E" & I) & "' where TD001='" & Range("A" & I) & "' and TD002='" & Range("B" & I) & "' and TD003='" & Range("C" & I) & "'"
用UPDATE，你再試試看
作者: PKKO    時間: 2017-2-19 19:14

回復 5# baomin


    您好,update 是修改資料
小弟想要的是當你有一萬筆資料要輸入至EXCEL
但想要用SQL連線的方式做輸入
要如何一次輸入多筆資料
而並非每一筆資料執行一次
作者: lpk187    時間: 2017-2-19 20:16

本帖最後由 lpk187 於 2017-2-19 20:20 編輯

用這個例子吧！1萬筆資料約3秒。這個insert into 的用法
匯入資料，不一定得用SQL語句
  1. Public Sub ex()
  2.     sql = "SELECT * FROM TblStudent ;"
  3.     strSqlInstance = "伺服器名稱"
  4.     strSqlDB = "資料庫名稱"
  5.     strSqlUser = "sa"
  6.     strSqlPWD = "a123456"
  7.     sConnect = "Provider=SQLOLEDB"
  8.     sConnect = sConnect & ";Data Source=" & strSqlInstance & ";Initial Catalog=" & strSqlDB
  9.     sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
  10.     Set myCon = CreateObject("ADODB.Connection")
  11.     myCon.Open sConnect
  12.     Set rng = Range("A2")
  13.     With New ADODB.Recordset
  14.         .Open sql, myCon, , adLockOptimistic
  15.         Do Until rng = ""
  16.             .AddNew
  17.             For i = 1 To .Fields.Count - 1
  18.                 .Fields(i) = rng.Offset(, i - 1)
  19.             Next
  20.             Set rng = rng.Offset(1, 0)
  21.         Loop
  22.         .Update
  23.     End With
  24.     Set myRs = Nothing
  25.     myCon.Close
  26.     Set myCon = Nothing
  27. End Sub
複製代碼
回復 6# PKKO
作者: PKKO    時間: 2017-2-20 00:06

回復 7# lpk187


lpk大大您好,
首先非常感謝您,另外想請教一下:
您是否會用SQL連線至有設定開啟密碼的EXCEL檔案呢?
以下是我的語法(可連線至EXCEL檔案,可是一但EXCEL有設定開啟密碼就無法連線了,不知道要如何輸入開啟密碼
可否幫小弟看一下...
  1. Public Conn As ADODB.Connection
  2. '保存和更新基礎資訊
  3. Public Sub Conn_conection()
  4. address1="EXCEL檔案位置"
  5. If Conn Is Nothing Then Set Conn = New ADODB.Connection
  6. '以下建立數據庫連接
  7. With Conn
  8. strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;" & _
  9.           "Data Source=" & address1 & ";Extended Properties='Excel 12.0;IMEX=0'"
  10. .Open strConn
  11. End With
  12. End Sub
複製代碼
作者: lpk187    時間: 2017-2-20 20:02

回復 8# PKKO


    在microsoft的文檔中有提到，在 [連線] 索引標籤上，瀏覽至活頁簿檔案。請忽略「使用者 ID」和「密碼」項目，因為它們不會套用至 Excel 連線 (您無法開啟具有密碼保護的 Excel 檔案做為資料來源。
https://support.microsoft.com/zh ... visual-basic-or-vba

所以只有無密碼保護的execl檔才能做為資料來源！
作者: PKKO    時間: 2017-2-21 14:35

回復 9# lpk187


    lpk 大大非常感謝您
我想也是這樣,因為我已經嘗試將password 放到任何位置,都無法進行連線
但是Access卻輕鬆達成

再次感謝您的回覆!



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)