返回列表 上一主題 發帖

[發問] VBA Application.FileDialog 應用

[發問] VBA Application.FileDialog 應用

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

test.zip (29.43 KB)


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

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

TOP

回復 2# samwang

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

TOP

        靜思自在 : 甘願做、歡喜受。
返回列表 上一主題