帖子: 115
主題: 24
精華: 0
積分: 178
點名: 2
作業系統: WIN10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2011-1-12
最後登錄: 2024-11-15

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2018-9-2 18:13 | 只看該作者

[發問] 使用VBA跨檔案抓取多個工作表裡的資料

本帖最後由 ABK 於 2018-9-2 18:16 編輯

請教各位大大:

我要在第一個Excel的工作表上抓取第二個Excel檔案裡多個不同工作表裡面的資料，
第一個檔"三廠生產日報" 放在C:/Users/Golden/Desktop/
第二個檔"三廠各機生產紀錄" 放在D:/生產日報/

生產代碼是唯一的，不會重複。
想用VBA讓第一個檔"三廠生產日報"以生產代碼搜尋第二個檔"三廠各機生產紀錄" A1、A2、A3、B1、B2、C1、C2 工作表裡相同生產代碼的投產數量紀錄到第一個檔"三廠生產日報"裡。

跨檔抓資料紀錄.jpg (716.07 KB)

生產日報-跨檔抓資料.rar (25.77 KB)

樓主熱帖

faye59

　暱稱: 温奇
　頭銜: 議論者

中學生 Rank: 2

帖子: 104
主題: 5
精華: 0
積分: 110
點名: 0
作業系統: win7
軟體版本: Office2007
閱讀權限: 20
性別: 男
註冊時間: 2017-11-14
最後登錄: 2019-4-30

論壇榮譽勳章論壇贊助勳章

2^#

發表於 2018-9-2 22:14 | 只看該作者

回復 1# ABK

相信以你的程度應該這樣回就看得懂了。
論壇內有相關範例可以用。
Open Excel Workbook，

Set Wb = Workbooks.Open("\\Test\B.xlsx")

複製代碼

開啟第二個Excel方式範例
CreateObject("Scripting.Dictionary")建立字典比對回傳結果。
類似範例可以參考#3
先試著寫寫看，有問題再幫忙修改。

然而我覺得用Access建立索引更快，用Excel巨集稍慢些。

一分努力，一分收穫。
發問題前可以先搜索內文是否有相關範例。

TOP

n7822123

　暱稱: 阿龍

中學生 Rank: 2

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

3^#

發表於 2018-9-3 02:00 | 只看該作者

本帖最後由 n7822123 於 2018-9-3 02:14 編輯

回復 1# ABK

你上傳的檔案壞了，打不開!

這不難，如上一篇所說，創造字典物件

把其它工作表的編號&數量輸入進字典

再用字典查詢另一個檔案的編號，輸入字典中的數量(item)即可

先重上傳附件吧，應該不會有人那麼勤勞幫你打一大串資料的.....(程式是需要檔案測試的)

或者你先合併數個工作表再用 VLookup 函數即可!

話說你應該是生管吧!!

:D

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

TOP

ABK

中學生 Rank: 2

帖子: 115
主題: 24
精華: 0
積分: 178
點名: 2
作業系統: WIN10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2011-1-12
最後登錄: 2024-11-15

4^#

發表於 2018-9-3 21:44 | 只看該作者

回復 2# faye59

謝謝faye59大!
我研究一下您提供的方法!

TOP

ABK

中學生 Rank: 2

帖子: 115
主題: 24
精華: 0
積分: 178
點名: 2
作業系統: WIN10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2011-1-12
最後登錄: 2024-11-15

5^#

發表於 2018-9-3 21:45 | 只看該作者

本帖最後由 ABK 於 2018-9-3 21:52 編輯

回復 3# n7822123

謝謝 n7822123大
我把檔案分開壓縮試試看 !
我是在產線工作，產線產出後各機台共用一張Excel表，由各機台負責人Key入產出資料，主管要看的是部分的匯總資料。

三廠生產日報.rar (8.78 KB)

三廠各機生產紀錄.rar (17.05 KB)

TOP

n7822123

　暱稱: 阿龍

中學生 Rank: 2

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

6^#

發表於 2018-9-3 22:01 | 只看該作者

回復 5# ABK

我不確定是否是我RAR版本太舊無法打開

我可以隨便打個範例，讓你套用看看

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

TOP

n7822123

　暱稱: 阿龍

中學生 Rank: 2

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

7^#

發表於 2018-9-3 23:36 | 只看該作者

本帖最後由 n7822123 於 2018-9-3 23:50 編輯

回復 6# n7822123

打好了，打你的表格花了不少時間(與我想要隨便打打的初衷有點不符......)
檔案&程式碼如下，請自行套用，既然都花時間了，註解也幫你寫好(好人做到底?)
表格位置與工作表名稱已經盡量與你相同，
但是可能還是有不同的地方，請自行更改套用。
附件2個檔案放在同一路徑下執行即可(不可有其他excel檔案)
或者輸入資料的路徑&檔名讓程式去抓!

生產管理範例.rar (30.28 KB)

Sub 查詢投產數量()
Dim 檔名$, 路徑檔名$, tt$, R& '宣告變數
Application.ScreenUpdating = False '螢幕即時更新關閉
Set Dy = CreateObject("scripting.dictionary") '設Dy為字典物件
Path = ThisWorkbook.Path '抓取本檔案路徑
Set 要填的表 = ThisWorkbook.Sheets("2018三廠機台生產追蹤") '命名此工作表為 "要填的表"
'如果[G5]有資料就依[G5]路徑的檔案，如果沒就找到就找同路徑下的另一個excel檔
If [G5] <> "" Then
路徑檔名 = [G5]
檔名 = Right(路徑檔名, Len(路徑檔名) - InStrRev(路徑檔名, "\"))
If Dir(路徑檔名) = "" Then MsgBox "依[G5]輸入的路徑與檔名找不到檔案，請檢查有無錯誤": Exit Sub
Else
檔名 = Dir(Path & "\*.xls*")
If 檔名 = ThisWorkbook.Name Then 檔名 = Dir
路徑檔名 = Path & "\" & 檔名
End If
'檢查資料檔案是否已開啟
For Each wb In Workbooks
If wb.Name = 檔名 Then MsgBox "資料檔案開啟中，請關閉": Exit Sub
Next
Set 資料檔 = Workbooks.Open(路徑檔名) '打開資料檔案，並且命名為"資料檔"
'逐一把工作表的生產代碼與頭產數量輸入到字典物件Dy裡面
For Each ws In 資料檔.Sheets
ws.Activate
If ws.[D1] <> "投產數量" Then GoTo 跳過 '檢查是否為要的工作表
For R = 2 To ws.[A1].End(xlDown).Row
tt = Cells(R, 3): Dy(tt) = Cells(R, 4)
Next R
跳過:
Next
要填的表.Activate '啟用要填的表
'逐一把字典物件Dy裡面的值輸入到此工作表(要填的表)
For R = 2 To [A1].End(xlDown).Row
tt = Cells(R, 4)
Cells(R, 5) = Dy(tt)
Next R
Application.DisplayAlerts = False '不跳出確認訊息
'存檔關閉+釋放記憶體
資料檔.Close True: Set 資料檔 = Nothing
Set Dy = Nothing
Application.ScreenUpdating = True '螢幕即時更新打開
End Sub

複製代碼

程式是依需求寫的，需求表達不清楚
或者沒有上傳附件，愛莫能助

TOP

ABK

中學生 Rank: 2

帖子: 115
主題: 24
精華: 0
積分: 178
點名: 2
作業系統: WIN10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2011-1-12
最後登錄: 2024-11-15

8^#

發表於 2018-9-4 02:25 | 只看該作者

本帖最後由 ABK 於 2018-9-4 02:27 編輯

回復 7# n7822123

感謝阿龍大的熱心回復! 讓你費這麼多時間 ! 真是抱歉!
我來拜讀研究一下 ! 感謝! 感謝!

TOP

ABK

中學生 Rank: 2

帖子: 115
主題: 24
精華: 0
積分: 178
點名: 2
作業系統: WIN10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2011-1-12
最後登錄: 2024-11-15

9^#

發表於 2018-9-10 23:58 | 只看該作者

回復 8# ABK

再請教一下各位前輩!
當我使用阿龍大的範例抓取資料時， Excel 會要求我關閉被抓取資料那個檔才能抓取資料，
有辦法做到不關閉資料檔也可以抓取資料嗎?

資料檔案開啟中-請關閉.jpg (172.67 KB)

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

10^#

發表於 2018-9-11 09:49 | 只看該作者

'檢查資料檔案是否已開啟, 若未開啟則以[唯讀]開啟, 並以uChk標示為1
On Error Resume Next
uChk = 0: Set 資料檔 = Workbooks(檔名)
On Error GoTo 0
If 資料檔 Is Nothing Then uChk = 1: Set 資料檔 = Workbooks.Open(路徑檔名, ReadOnly:=True)

'關閉檔案_不存檔 (若資料檔不是程式所開啟, 則不關閉)
If uChk = 1 Then 資料檔.Close 0

TOP