VBA批量导出选中的工作表为单独文件
时间: 2024-05-21 18:12:07 浏览: 15
您可以通过以下步骤来批量导出选中的工作表为单独文件:
1.选中要导出的工作表。
2.按下 Alt+F11 打开 VBA 编辑器。
3.在 VBA 编辑器中,点击“插入”->“模块”。
4.将以下代码复制粘贴到新建的模块中:
Sub ExportWorksheets()
Dim ws As Worksheet
Dim newBook As Workbook
For Each ws In ActiveWindow.SelectedSheets
Set newBook = Workbooks.Add
ws.Copy Before:=newBook.Sheets(1)
newBook.Sheets(1).Delete
newBook.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".xlsx"
newBook.Close
Next ws
End Sub
5.按下 F5 执行代码,将选中的工作表批量导出为单独的 Excel 文件。
注意:本代码仅适用于 Excel 2010 或更高版本。
相关问题
批量导出选取的工作表为单独文件VBA
可以使用以下VBA代码来批量导出选取的工作表为单独文件:
Sub ExportSelectedSheets()
Dim Sheet As Worksheet
Dim TempWB As Workbook
Dim TempWS As Worksheet
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim FolderPath As String
Dim FileName As String
'Set default file extension
FileExtStr = ".xlsx": FileFormatNum = 51
'Folder where files will be saved
FolderPath = "C:\Users\Username\Documents\"
For Each Sheet In ActiveWindow.SelectedSheets
'Create a temporary workbook
Set TempWB = Workbooks.Add(1)
Sheet.Copy After:=TempWB.Sheets(1)
Set TempWS = TempWB.Sheets(1)
'Save the temporary workbook as a separate file
FileName = Sheet.Name
TempWB.SaveAs FolderPath & FileName & FileExtStr, FileFormatNum
'Close the temporary workbook without saving changes
TempWB.Close SaveChanges:=False
Next Sheet
End Sub
将此代码复制并粘贴到新的VBA模块中,然后在Excel电子表格中选择要导出的工作表并运行此代码。此代码将为每个选定的工作表创建一个单独的文件,将其保存在指定的文件夹中。
VBA选取特定工作表并批量导出为单独文件
您可以使用以下 VBA 代码选取特定工作表并批量导出为单独文件:
```
Sub ExportWorksheets()
Dim xWorksheet As Worksheet
Dim xWorkbook As Workbook
Dim xFolderPath As String
Dim xDialog As FileDialog
Set xDialog = Application.FileDialog(msoFileDialogFolderPicker)
xDialog.AllowMultiSelect = False
xDialog.Title = "Select a folder"
If xDialog.Show = -1 Then
xFolderPath = xDialog.SelectedItems(1)
End If
Set xWorkbook = Application.ActiveWorkbook
For Each xWorksheet In xWorkbook.Worksheets
xWorksheet.Select
xWorksheet.Copy
xWorkbook.SaveAs Filename:=xFolderPath & "\" & xWorksheet.Name & ".xlsx", FileFormat:=51
xWorkbook.Close False
Set xWorkbook = Application.Workbooks.Open(ThisWorkbook.FullName)
Next
End Sub
```
您可以将上面的代码复制到 VBA 编辑器中,并按 F5 键运行它。然后会打开一个文件夹选择器,让您选择保存导出文件的文件夹。接着,它将逐一选中每个工作表、复制它、保存为单独的文件并关闭工作簿。最后,它将重新打开工作簿,以便继续处理下一个工作表。导出的每个文件都将以其工作表名称命名,并保存为 xlsx 格式。