[發問] VBA Application.FileDialog 應用

john2006168

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

test.zip (29.43 KB)

下載次數: 4

2021-5-3 19:30

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

回復 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

回復 2# samwang

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