我使用一个宏,将不同的工作表合并到一个excel文件中。它100%有效。
我在问是否有人可以给一个帮助来改善这个宏。
我需要的是每个表获得文件名,而不是随机名称。
不幸的是,我不能给出这段代码的署名,因为我不知道作者的名字。
的代码Sub mergeFiles()
'Merges all files in a folder to a main file.
'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count
'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
'Close the source workbook
sourceWorkbook.Close
Next i
End Sub
您需要为复制的表单声明一个新变量:
Dim NewSheet As Worksheet
然后你必须调整你复制的部分:
For Each tempWorkSheet In SourceWorkbook.Worksheets
Set NewSheet = tempWorkSheet.Copy(After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count))
NewSheet.Name = SourceWorkbook.Name
Next tempWorkSheet
通过这个非常简单的调整,所有的工作表都将获得SourceFile的名称(包括". xml "),但是如果在一个SourceFile中有多个工作表,它可能会崩溃。如果使用
NewSheet.Name = SourceWorkbook.Name + "_" + tempWorkSheet.Name
相反,如果整个字符串的长度超过31个字符,则可能会遇到一些问题。