To export an Excel workbook with VBA, use Workbook.SaveAs to save the open workbook in a different file format, or Workbook.SaveCopyAs to create a same-format copy without changing the open workbook. For a PDF, use Workbook.ExportAsFixedFormat. The right method depends on whether you want a new format, a backup copy, a printable document, or just the data.
What “export a workbook” can mean
Excel does not have one VBA command called “export workbook” that covers every kind of output. In practice, people use export to mean several different tasks, and choosing the wrong one can discard macros or produce a file that looks different from the workbook.
- Save in another Excel format:
SaveAswrites the workbook as XLSX, XLSM, XLS, or another supported format. It also changes the open workbook’s file name and format. - Make a backup copy:
SaveCopyAswrites a copy in the workbook’s existing format and leaves the open workbook as it was. - Publish a PDF:
ExportAsFixedFormatcreates a PDF or XPS document from the workbook’s printable pages. - Export rows as text: A CSV or text file contains data from a sheet, not a faithful copy of the whole workbook.
These distinctions matter. A PDF is for viewing or printing, CSV is for exchanging plain tabular data, and an Excel workbook format is needed to preserve spreadsheet features such as formulas, formatting, and multiple worksheets.
Save a workbook in a different format with SaveAs
In Microsoft 365 Excel for Windows, Workbook.SaveAs saves the workbook that the code refers to under a specified name and format. The example below saves the workbook containing the macro as an XLSX file in its current folder. It assumes that the workbook has already been saved once, so it has a folder path.
Sub SaveCurrentWorkbookAsXlsx()
Dim wb As Workbook
Dim targetPath As String
Set wb = ThisWorkbook
' This example needs an existing folder path.
If Len(wb.Path) = 0 Then
MsgBox "Save the workbook once before running this macro."
Exit Sub
End If
targetPath = wb.Path & Application.PathSeparator & "Report.xlsx"
' Save the open workbook as an Excel workbook without macros.
wb.SaveAs Filename:=targetPath, FileFormat:=xlOpenXMLWorkbook
End Sub
ThisWorkbook means the workbook containing the VBA code. If the macro is stored in PERSONAL.XLSB or an add-in, that is not necessarily the workbook you mean to export; in that case, set wb to the intended workbook, such as ActiveWorkbook, after confirming which file is active.
Important: XLSX files cannot store VBA macros. Saving an XLSM workbook as XLSX can prompt Excel to warn that VBA code will be removed. SaveAs also changes the identity and format of the open workbook; it is not simply a separate export. Save a backup first, or use a copy-based workflow if you need to keep the original macro-enabled workbook intact.
Choose the correct Excel file format
The FileFormat argument tells Excel what kind of file to write. Common choices include:
xlOpenXMLWorkbookfor XLSX. This is the standard modern Excel format and does not retain VBA projects.xlOpenXMLWorkbookMacroEnabledfor XLSM. Use it when the workbook contains VBA that must remain available.xlExcel8for the older XLS format. This format has older worksheet limits and may not preserve newer Excel features exactly.xlCSVfor comma-separated text. CSV does not preserve multiple sheets, formulas as formulas, or workbook formatting.
For example, with a workbook object named wb and a destination path stored in targetPath, an XLSM save uses wb.SaveAs Filename:=targetPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled. The extension and file format should agree: use an .xlsm name for the macro-enabled format and an .xlsx name for the non-macro format. Excel may display a format warning if they do not match.
Create a same-format copy with SaveCopyAs
If the goal is a backup or duplicate rather than a format conversion, SaveCopyAs is usually the safer choice. It saves a copy using the workbook’s current file format and does not switch the open workbook to the new path. The code below assumes the source workbook has a saved folder and that the destination name is suitable for that workbook’s current extension.
Sub MakeWorkbookCopy()
Dim wb As Workbook
Dim targetPath As String
Set wb = ThisWorkbook
' Save the source workbook before using its folder path.
If Len(wb.Path) = 0 Then
MsgBox "Save the workbook once before running this macro."
Exit Sub
End If
targetPath = wb.Path & Application.PathSeparator & "Report_backup.xlsm"
' Creates a copy; the open workbook remains at its original path.
wb.SaveCopyAs Filename:=targetPath
End Sub
Change the example extension to match the source workbook’s format; for an XLSX source, use an .xlsx destination. SaveCopyAs does not convert an XLSM file into XLSX. If a file with the destination name already exists, handle that situation deliberately rather than assuming the macro will silently overwrite it; Excel’s prompts and behavior depend on the method and circumstances.
Export the workbook as a PDF
To create a document for sharing or printing, use ExportAsFixedFormat. This exports the workbook’s printable pages, so the result depends on page setup, print areas, scaling, and which sheets are included. Set those options in Excel before exporting if page breaks or missing content would be a problem.
Sub ExportWorkbookToPdf()
Dim wb As Workbook
Dim pdfPath As String
Set wb = ThisWorkbook
' This example uses the workbook's existing folder.
If Len(wb.Path) = 0 Then
MsgBox "Save the workbook once before exporting the PDF."
Exit Sub
End If
pdfPath = wb.Path & Application.PathSeparator & "Report.pdf"
' Export the workbook's printable pages as a PDF.
wb.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=pdfPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
With IgnorePrintAreas:=False, Excel respects print areas set on the sheets. Set it to True if you want Excel to ignore those print areas. A workbook PDF is not necessarily one page per worksheet: a long sheet can span several pages, and a sheet configured not to print may not appear as expected. Review the output before sending it.
Export a sheet as CSV or plain text
A CSV file is useful when another system needs a simple table, but it is not a complete workbook export. A CSV holds plain text values from one sheet at a time. It does not retain cell formatting, charts, formulas as editable formulas, or the other worksheets in the workbook. Excel may write formula results as displayed values, and dates or numbers can be interpreted differently by the receiving application or regional settings.
Excel’s Save As interface can save the active sheet as CSV. A VBA routine can also use SaveAs with a CSV file format, but because CSV is a single-sheet format, Excel may warn that features are not supported. Do not suppress that warning unless the macro explicitly accounts for the loss. If you need several sheets, export each sheet to its own file or use an XLSX workbook instead. For data exchange, confirm the expected delimiter, character encoding, decimal conventions, and column headers with the program that will read the file.
How to run the VBA macro safely
On Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. Return to Excel, then use Developer > Macros to select and run it. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Excel for the web can open workbooks but cannot create, edit, or run VBA macros; use the desktop application.
On a Mac, use Option+F11 to open the VBA editor; depending on the keyboard, you may also need the Fn key. The Mac editor and supported VBA features are not identical to Windows, and Windows-only components such as ActiveX controls are not supported in the same way. The basic workbook methods described here are Excel VBA methods, but test any larger automation on the platform where it will run.
Before running any macro that changes or exports a file, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, and a save to the wrong format or path can be difficult to undo. Only enable macros from sources you trust. If Excel blocks a macro, review the file’s source and Excel’s security settings rather than weakening macro security indiscriminately.
Common export problems and fixes
- The path is empty: A new workbook may not have a folder path yet. Save it manually first, or ask the user for a destination path instead of building one from
Workbook.Path. - Excel reports that the format and extension do not match: Match the filename extension to the
FileFormatconstant. XLSX, XLSM, and PDF are different output types. - The exported XLSX has no macros: That is expected; XLSX does not store VBA. Use XLSM when the output must retain macros.
- The PDF is missing rows or has unexpected page breaks: Check the sheet’s print area, orientation, paper size, margins, and scaling, then inspect Print Preview or the exported PDF.
- A CSV contains only one worksheet: That is a limitation of CSV, not a VBA failure. Use an Excel workbook format or create a separate CSV for each sheet.
- The macro cannot write to the destination: Confirm that the folder exists, you have permission to write there, the file is not locked, and the destination is not open in another application.
For unattended exports, add error handling and logging, check whether the destination already exists, and restore any application settings changed by the macro. Avoid turning off alerts globally without a cleanup path: if an error occurs while alerts are disabled, later Excel actions may behave unexpectedly.
Frequently Asked Questions
How do I export an Excel workbook without losing the original?
Use SaveCopyAs for a copy in the same format. To make a different-format output, use a deliberate copy-and-convert workflow or save a separate working copy first; SaveAs changes the open workbook’s format and path.
Can VBA export all worksheets to one CSV file?
No. CSV represents a single flat sheet, not a multi-sheet workbook. Export each worksheet to its own CSV or use XLSX if the receiving application needs multiple tabs.
Does SaveAs keep macros?
It depends on the chosen format. Save as XLSM with xlOpenXMLWorkbookMacroEnabled to retain VBA. Saving as XLSX with xlOpenXMLWorkbook removes VBA from the saved workbook.
Can I export only one worksheet as a PDF?
Yes. Select the worksheet and export the selected sheet, or use a worksheet-level fixed-format export. A workbook-level export can include printable pages from multiple sheets, depending on the workbook and print settings.
Can I run an Excel VBA export macro in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; an Office Script may be an alternative for some web-based automation tasks.
Which export method should you use?
Use SaveAs when you intend to change the open workbook’s format or save it under a new working identity. Use SaveCopyAs for a straightforward same-format backup. Use ExportAsFixedFormat for a PDF intended for reading or printing, and CSV only when a plain, single-sheet data table is what the recipient needs. Choosing the output type first prevents the most common surprises: missing macros, lost workbook features, and exports that contain less than expected.
Related
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary If: Check Whether a Key ExistsTo check whether a key is in a VBA Dictionary, use its Exists method inside an If statement:…
- Excel VBA Loops: How to Repeat Tasks EfficientlyAn Excel VBA loop repeats a block of code until it has processed a set number of items…
- Excel VBA Function Return: Values, Syntax, and ExamplesAn Excel VBA function returns a value by assigning that value to the function’s own name; VBA does…
- Open an Excel File for Reading in VBA: A Step-by-Step GuideTo open an Excel workbook for reading in VBA, use Workbooks.Open with ReadOnly:=True, then refer to the workbook…
- Excel VBA Find Sheet by Name: Safe Code and ExamplesTo find a worksheet by its tab name in Excel VBA, use ThisWorkbook.Worksheets("Summary") and handle the error that…
- How to Put a Photo in a VBA UserFormTo put a photo in an Excel VBA UserForm, add an Image control, then either set its Picture…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.