To export data from Excel with VBA, you write a macro that copies a range or worksheet and saves it to a file using methods like Workbook.SaveAs, ExportAsFixedFormat, or FileCopy. The simplest example: ThisWorkbook.SaveAs "C:\Exports\data.xlsx" saves the entire workbook, while ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Exports\sheet.pdf" exports just the active sheet to PDF. Below, you’ll find the full range of export options, code examples, and how to choose the right method for your data.
Why Export Data with VBA?
Excel’s built-in export commands (File > Save As, or Export) work fine for one-off tasks, but they require manual steps each time. VBA (Visual Basic for Applications) automates the process, letting you export data on a schedule, from a button click, or as part of a larger workflow. Common reasons to use VBA for exporting:
- You need to export the same data daily or weekly to a shared folder.
- You want to export only a specific range (e.g., A1:D50) instead of the whole sheet.
- You need to convert data to a different format (CSV, PDF, text) without re-formatting.
- You want to combine data from multiple sheets into one export file.
Core VBA Export Methods
There are four main ways to export data in VBA, each suited to different file types:
- SaveAs – saves the entire workbook (or a copy) in formats like .xlsx, .xls, .csv, .txt. Works with any format Excel supports.
- ExportAsFixedFormat – exports to PDF or XPS. Available in Excel 2010 and later.
- FileCopy – copies an existing file (like a template) to a new location, useful for duplicating workbooks.
- ADO or QueryTable – exports data to external databases or text files via connection strings (advanced, usually for SQL).
For most users, SaveAs and ExportAsFixedFormat cover 95% of needs.
How to Export a Range to a New Workbook
Often you don’t want the entire workbook—just a specific range. Here’s a macro that copies range A1:C20 from the active sheet and saves it as a new .xlsx file:
Sub ExportRangeToNewWorkbook()
Dim sourceRange As Range
Dim newWb As Workbook
' Set the range you want to export
Set sourceRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C20")
' Create a new workbook
Set newWb = Workbooks.Add
' Copy the range to the new workbook's first sheet
sourceRange.Copy Destination:=newWb.Sheets(1).Range("A1")
' Save the new workbook (adjust path as needed)
newWb.SaveAs Filename:="C:\Exports\range_data.xlsx", FileFormat:=xlOpenXMLWorkbook
' Close the new workbook without saving changes (since we already saved)
newWb.Close SaveChanges:=False
MsgBox "Export complete"
End Sub
Note: Always save a copy of your original workbook before running any macro that creates or modifies files—macros can’t be undone with Ctrl+Z.
Exporting to CSV or Text Files
CSV (comma-separated values) is a common export format for data interchange. To export a single worksheet as a CSV file, use SaveAs with the xlCSV format. But beware: SaveAs with CSV only saves the active sheet, and it may alter your original file if you’re not careful. Here’s a safe way to export a specific sheet to CSV:
Sub ExportSheetToCSV()
Dim ws As Worksheet
Dim csvPath As String
' Set the sheet to export
Set ws = ThisWorkbook.Sheets("Data")
csvPath = "C:\Exports\data.csv"
' Copy the sheet to a new workbook to avoid changing the original
ws.Copy ' creates a new workbook with just that sheet
ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV
ActiveWorkbook.Close SaveChanges:=False
End Sub
For text files with custom delimiters (e.g., tab or semicolon), use xlTextWindows or xlTextMac formats, or write a loop that outputs values to a text file using the Open and Print statements.
Exporting to PDF
PDF export is ideal for reports or sharing data that shouldn’t be edited. The ExportAsFixedFormat method gives you control over what gets exported:
Sub ExportToPDF()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Report")
' Export the sheet to PDF, fit to page
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Exports\report.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
You can also export a range by setting the Range parameter (e.g., Range:="A1:F30"). To export the entire workbook, use ThisWorkbook.ExportAsFixedFormat instead of a sheet.
Choosing the Right Export Method
Your choice depends on the file format and the data’s destination:
- Excel workbook (.xlsx) – use
SaveAswithFileFormat:=xlOpenXMLWorkbook. Preserves formulas, formatting, and multiple sheets. - CSV (.csv) – use
SaveAswithFileFormat:=xlCSV. Only the active sheet is saved, and only values (no formatting). - PDF (.pdf) – use
ExportAsFixedFormat. Best for printable reports. - Text (.txt) – use
SaveAswithFileFormat:=xlTextWindows(tab-delimited) orxlTextMSDOS(comma-delimited). - Copying a file – use
FileCopyif you have a pre-made template.
Common Problems and Solutions
Here are frequent issues users hit when exporting with VBA:
- “SaveAs” changes the original workbook – If you use
ThisWorkbook.SaveAs, the original file is replaced. Always use a copy or a new workbook, as shown above. - CSV export only saves the active sheet – That’s by design. To export multiple sheets to CSV, you’d need to loop through sheets and save each separately.
- File path errors – Ensure the folder exists before saving. Use
Dirto check orMkDirto create it. - Macro security blocks the code – Save the workbook as a macro-enabled file (.xlsm) and enable macros when opening.
- ExportAsFixedFormat fails on some systems – Make sure you have a PDF printer installed (usually built-in with Windows).
Frequently Asked Questions
How do I export a single column to a text file?
Loop through the cells in the column and write each value to a text file using Open and Print. For example, to export column A to a text file, you’d iterate from row 1 to the last used row and write each value with a line break.
Can I export data to an existing Excel file without overwriting it?
Yes, use Workbooks.Open to open the destination file, copy the data to a new sheet, and save. Or use ADO to append to a table, but that’s more complex.
Is there a way to export to Excel without VBA?
Yes, you can use Power Query to export data to CSV or use the built-in Export commands. But VBA is more flexible for automation.
Why does my exported CSV show dates in the wrong format?
CSV files are plain text; Excel stores dates as serial numbers. When exporting, the date appears as a number unless you format it as text first. Use Range.NumberFormat = "@" to convert to text before saving.
How do I export data from multiple sheets into one PDF?
Set the IgnorePrintAreas to False and use ThisWorkbook.ExportAsFixedFormat with Type:=xlTypePDF. By default, it exports all sheets that have print areas or content.
Can I run a VBA export without opening Excel?
Yes, you can run a VBA macro from a script (like PowerShell) that opens Excel in the background, runs the export, and closes. Or use a scheduled task with a .vbs script. But that requires Excel to be installed on the machine.
Related
- Excel Reverse Mortgage Calculator Spreadsheet
- Excel Repair Tool: What It Is & How to Use It
- Excel Rank Values in Range: RANK, RANK.EQ, RANK.AVG
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Select to Bottom of Array in Excel VBA: Safe MethodsTo select from the active cell to the bottom of its contiguous data block in Excel VBA, use…
- Excel VBA Copy Paste Special: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination, specifying what to paste. For example,…
- VBA Copy and Paste Data Between Excel SheetsTo copy a range from one Excel worksheet to another with VBA, use a macro that names both…
- VBA UserForm Scrollbar: Setup, Sizing, and FixesTo add a scrollbar to an Excel VBA UserForm, set its ScrollBars property to vertical, horizontal, or both,…
- Excel VBA: Get the Day of the Week from a Date ValueIn Excel VBA, use Weekday to get a date’s weekday number and WeekdayName to turn that number into…
- VBA Excel Calculate Range: Methods and ExamplesTo calculate a specific range in Excel VBA, use the range’s Calculate method: for example, Worksheets("Sheet1").Range("B2:B100").Calculate. It recalculates…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.