1. 先開一個新xlsm file
2. 將所有filename (包括.xlsx) 放在 Column A
3. copy 以下sub 去VBA 及run
4. 會自動copy 去Column B ( 每個file 的Cell F3)
注意:
1. 記得所有file 在同一folder, 並將個full path 改返去 wb_path
2. 記得改返for loop 個counter, 比如你有100 個file 就 For i = 1 To 100
3. 假設copy每個excel file 的第一個worksheet
Code 如下:
Sub copyallfromfiles()
Dim wb_cur As String
Dim wb_open As String
Dim wb_path As String
wb_path = "C:\aaaa\"
wb_cur = ActiveWorkbook.Name
Dim i As Integer
For i = 1 To 3
wb_open = Workbooks(wb_cur).Worksheets(1).Range("A1").Offset(i - 1, 0)
Workbooks.Open wb_path & wb_open
'MsgBox wb_open
Workbooks(wb_cur).Worksheets(1).Range("B1").Offset(i - 1, 0) = Workbooks(wb_open).Worksheets(1).Range("F3")
Workbooks(wb_open).Close
Next i
End Sub
