標題: [發問] VBA Application.FileDialog 應用 [打印本頁]

---

作者: john2006168    時間: 2021-5-3 19:31     標題: VBA Application.FileDialog 應用

今日看到一些 .使用 VBA 的 Application.FileDialog 建立檔案或目錄選擇對話框，讓使用者以滑鼠選擇檔案或目錄。
請問是否可以將指定選出來workbooks( Data)  copy 第一個工作表 整頁到 到現在有vba workbook指定的sheets  "TEST BOOK"?
要怎麼改,盼大大賜教

[attach]33271[/attach][attach]33271[/attach]


Sub test()
Dim FName As String, FPath As String
Dim sheet As Worksheet
Dim FDialog As FileDialog

Application.ScreenUpdating = False
Application.DisplayAlerts = False ' 關閉警告訊息
Set FDialog = Application.FileDialog(msoFileDialogFilePicker)
FDialog.Show

For x = 1 To FDialog.SelectedItems.Count
    FPath = FDialog.SelectedItems(x)
    Set WB = Workbooks.Open(FPath)
    For Each sheet In ActiveWorkbook.Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next sheet
    WB.Close
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

---

作者: samwang    時間: 2021-5-4 08:26

回復 1# john2006168

請測試看看，謝謝。

Sub test1()
Dim FName As String, FPath As String
Dim sheet As Worksheet
Dim FDialog As FileDialog

Application.ScreenUpdating = False
Application.DisplayAlerts = False ' 關閉警告訊息
fileorg = ActiveWorkbook.Name
Set FDialog = Application.FileDialog(msoFileDialogFilePicker)
FDialog.Show

For x = 1 To FDialog.SelectedItems.Count
    FPath = FDialog.SelectedItems(x)
    Set WB = Workbooks.Open(FPath)
    With Sheets(1).UsedRange
        .Copy Workbooks(fileorg).Sheets(1).Range("a1")
    End With
    WB.Close
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

---

作者: john2006168    時間: 2021-5-4 09:20

回復 2# samwang

謝謝老師幫忙,可以運行.發現不斷去做練習,再加各位大大的指導,這方面的知識會慢慢增加了很多.

---

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