- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2024-10-30
|
6#
發表於 2021-12-13 10:02
| 只看該作者
回復 samwang
您好，因為我的資料夾裡面不只一個excel檔，可以用指定的方式嗎？
Workbooks("1. ...
蘿蔔泥 發表於 2021-12-13 09:29 
改用選擇檔案方式，請測試看看，謝謝
Sub test()
Dim FileOrg$, fc%, wb, a, Dr, Sr, j%
Application.ScreenUpdating = False: Application.DisplayAlerts = False
FileOrg = ActiveWorkbook.Name
Sheets("連結總表").[a1].CurrentRegion.Offset(1) = ""
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "D:\"
.Title = "===========選擇 ""年度訓練總表"" 檔案==========="
.AllowMultiSelect = False: .Show
fc = .SelectedItems.Count: If fc = 0 Then Exit Sub
a = .SelectedItems(1)
Set wb = Workbooks.Open(a)
With Sheets("總表")
If .FilterMode Then .ShowAllData
Set xA = .[a1]
R = xA(Rows.Count, 1).End(xlUp).Row
Dr = Array(1, 5, 6, 10, 9, 4, 12, 13, 17, 20, 28, 25, 26, 27) '資料來源
Sr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) '順序
For j = 0 To UBound(Dr)
Workbooks(FileOrg).Sheets("連結總表").Cells(2, Sr(j)).Resize(R).Value = xA(2, Dr(j)).Resize(R).Value
Next
End With
End With
wb.Close
Set wb = Nothing: Set xA = Nothing
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub |
|
