返回列表 上一主題 發帖

[發問] VBA 用SQL 一次新增多筆資料至EXCEL的方法

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 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 & ");"

求大大們協助 = =
收藏 分享
PKKO

lpk187

帖子
552 
主題
3 
精華
0 
積分
578 
點名
0  
作業系統
win7 
軟體版本
office 2010 
閱讀權限
50 
性別
男 
註冊時間
2015-2-8 
最後登錄
2024-7-9 

2016猴年全勤獎章2015羊年全勤獎章熱烈參與勳章
2#
發表於 2017-2-17 23:56 | 只看該作者
回復 1# PKKO


    剛好有看過一篇，但未曾實踐過，你可以試試
https://support.microsoft.com/en ... nto-microsoft-excel

TOP

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
3#
發表於 2017-2-18 10:38 | 只看該作者
回復 2# lpk187


    可惜此篇尚未提到,感謝您!
PKKO

TOP

michael101772

帖子
13 
主題
3 
精華
0 
積分
22 
點名
0  
作業系統
xp 
軟體版本
office 2007 
閱讀權限
10 
性別
男 
註冊時間
2013-2-23 
最後登錄
2017-11-17 
4#
發表於 2017-2-18 13:07 | 只看該作者
不太懂你的意思，你是要從Excel撈sql裡面的資料嗎？
這個用select就可以了。

TOP

baomin

帖子
26 
主題
3 
精華
0 
積分
57 
點名
0  
作業系統
windowsxp 
軟體版本
office2003 
閱讀權限
20 
性別
男 
來自
彰化 
註冊時間
2010-5-4 
最後登錄
2019-4-25 
5#
發表於 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，你再試試看

TOP

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
6#
發表於 2017-2-19 19:14 | 只看該作者
回復 5# baomin


    您好,update 是修改資料
小弟想要的是當你有一萬筆資料要輸入至EXCEL
但想要用SQL連線的方式做輸入
要如何一次輸入多筆資料
而並非每一筆資料執行一次
PKKO

TOP

lpk187

帖子
552 
主題
3 
精華
0 
積分
578 
點名
0  
作業系統
win7 
軟體版本
office 2010 
閱讀權限
50 
性別
男 
註冊時間
2015-2-8 
最後登錄
2024-7-9 

2016猴年全勤獎章2015羊年全勤獎章熱烈參與勳章
7#
發表於 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

TOP

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
8#
發表於 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
複製代碼
PKKO

TOP

lpk187

帖子
552 
主題
3 
精華
0 
積分
578 
點名
0  
作業系統
win7 
軟體版本
office 2010 
閱讀權限
50 
性別
男 
註冊時間
2015-2-8 
最後登錄
2024-7-9 

2016猴年全勤獎章2015羊年全勤獎章熱烈參與勳章
9#
發表於 2017-2-20 20:02 | 只看該作者
回復 8# PKKO


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

所以只有無密碼保護的execl檔才能做為資料來源！

TOP

PKKO
　暱稱: PKKO

高中生Rank: 3Rank: 3

帖子
549 
主題
152 
精華
0 
積分
691 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE 2010 
閱讀權限
50 
性別
男 
註冊時間
2013-8-10 
最後登錄
2022-9-7 

論壇榮譽勳章論壇贊助勳章
10#
發表於 2017-2-21 14:35 | 只看該作者
回復 9# lpk187


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

再次感謝您的回覆!
PKKO

TOP

        靜思自在 : 成功是優點的發揮，失敗是缺點的累積。
返回列表 上一主題