VBA 合并Excel 文件夹下的所有CSV文件 | 您所在的位置:网站首页 › vba新建工作簿命名重复 › VBA 合并Excel 文件夹下的所有CSV文件 |
把文件夹下的多个CSV文件合并到一一起 使用步骤: 打开一个新的Excel文档按下Alt + F11打开Visual Basic Editor插入新的模块将上面的代码复制粘贴到模块中按下F5执行代码该代码将提示您选择要合并的文件夹,并将所有CSV文件合并到一个名为"MergedData.csv"的新文件中。如果有任何错误或警告,代码将停止并显示相应的消息框。 Sub MergeCSVFiles() Dim folderPath As String, selectedFolderPath As String Dim fileName As String, mergedFileName As String Dim wb As Workbook, combinedData As Workbook Dim ws As Worksheet 'Get the folder path With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select a folder" .AllowMultiSelect = False If .Show = -1 Then folderPath = .SelectedItems(1) Else Exit Sub End If End With 'Create a new workbook to combine the data Set combinedData = Workbooks.Add Set ws = combinedData.Sheets(1) 'Loop through each file in the folder fileName = Dir(folderPath & "\*.csv") Do While fileName "" 'Open the file and copy the data to the combined workbook Set wb = Workbooks.Open(folderPath & "\" & fileName) wb.Sheets(1).UsedRange.Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1) wb.Close SaveChanges:=False 'Get the next file name fileName = Dir Loop 'Save the combined data to a new file mergedFileName = folderPath & "\MergedData.csv" combinedData.SaveAs Filename:=mergedFileName, FileFormat:=xlCSV, CreateBackup:=False combinedData.Close SaveChanges:=False 'Notify the user that the files have been merged MsgBox "CSV files in " & folderPath & " have been merged into a single file named " & mergedFileName, vbInformation, "Merge Complete" End Sub |
CopyRight 2018-2019 实验室设备网 版权所有 |