我正在努力撰寫作業代碼，提示用戶使用 FileDialog 選擇檔案夾路徑，但無法將其與另一個將所需檔案匯入到打開的作業簿的例程集成。以下是到目前為止我的代碼，但我不需要預先確定檔案夾路徑，而是需要提示用戶：
Sub MergeCsvFilesToWb()
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Dim fldr As FileDialog
Application.ScreenUpdating = False
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select the Folder where the '.csv' files are located."
.AllowMultiSelect = False
.Show
End With
FolderPath = fldr.SelectedItems(1)
Filename = Dir(FolderPath & "*.csv*")
Do While Filename <> ""
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
任何幫助表示贊賞
uj5u.com熱心網友回復：
FileDialog可以在SelectedItems屬性中找到選擇的檔案/檔案夾（檔案）
因此，要將變數分配給FolderPath所選檔案夾：
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select the Folder where the '.csv' files are located."
.Show
Dim FolderPath As String
FolderPath = .SelectedItems(1)
End With
請注意，您應該處理用戶未Cancel在對話框中選擇任何檔案夾（單擊）的事件，因此更好的版本是：
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select the Folder where the '.csv' files are located."
.Show
Dim FolderPath As String
If .SelectedItems.Count <> 0 Then
FolderPath = .SelectedItems(1)
Else
'Code to handle event that nothing is selected
'e.g.
'Exit Sub
End If
End With
最后，回傳的檔案夾路徑末尾沒有斜杠，因此您需要：
修改
Filename = Dir(FolderPath & "*.csv*")為Filename = Dir(FolderPath & "\*.csv*")修改
FolderPath = .SelectedItems(1)為FolderPath = .SelectedItems(1) & "\"
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/390970.html
上一篇：查找列
下一篇：使用表之間查找的陣列的總和