To convert a range to a formatted HTML file in Excel for Microsoft 365 on Windows, add a VBA macro that publishes the range with Excel’s PublishObjects collection. Set the worksheet and range in the code, run the macro, and Excel writes a static HTML file you can open in a browser or share. Save a copy of your workbook first: macros cannot be undone with Ctrl+Z.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows. Excel for the web can store a workbook containing VBA, but it cannot run the macro.
- A workbook with the range you want to publish. Save it as an Excel Macro-Enabled Workbook (
.xlsm) if you want to keep the macro in that file. - A worksheet name, a range address such as
A1:D12, and a folder where Excel can create the HTML file. Save the workbook before running the sample macro because it writes the HTML beside the workbook. - Permission to run macros in this workbook. Only enable macros for files you trust; do not change Excel’s security settings globally just to run an unknown file.
Step-by-Step
- Save a working copy and choose the content. Make a copy of your workbook before running any macro. Decide which worksheet and rectangular range to publish, including any header rows you want readers to see. For example, if the report occupies cells A1 through D12 on a sheet named Report, the macro below will export those cells. Check the range for blank rows or columns that you do not want included. The export produces a separate HTML file; it does not replace the worksheet or change the cells in the selected range.
- Save the workbook as a macro-enabled file. In Excel, select File > Save As, choose a location, and set the file type to Excel Macro-Enabled Workbook (*.xlsm). If you already have an
.xlsmfile, save your copy normally. An.xlsxworkbook cannot retain VBA code when saved, so choose the macro-enabled format if the macro needs to remain available in this workbook. - Open the Visual Basic Editor and insert a module. On Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, select Insert > Module. A blank code window appears. If the Developer tab is visible, Developer > Visual Basic opens the same editor. If Excel displays a macro-security warning, use the workbook only if you trust its source. Do not select an option that enables all macros in every workbook.
- Paste the macro and set the worksheet and range. Paste this code into the module. Change
Reportto the exact worksheet tab name and changeA1:D12to the range you want. The output file will be namedReport.htmland saved in the same folder as the workbook.
Sub ExportRangeToHTML()
Dim ws As Worksheet
Dim rng As Range
Dim pub As PublishObject
Dim htmlPath As String
Dim oldScreenUpdating As Boolean
Dim errText As String
On Error GoTo CleanFail
' Save the workbook first so it has a folder for the HTML file.
If Len(ThisWorkbook.Path) = 0 Then
MsgBox "Save the workbook before exporting the range.", vbExclamation
Exit Sub
End If
' Update these two references for your worksheet and range.
Set ws = ThisWorkbook.Worksheets("Report")
Set rng = ws.Range("A1:D12")
htmlPath = ThisWorkbook.Path & Application.PathSeparator & "Report.html"
oldScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
' Publish the selected cells as static HTML; True allows overwriting the file.
Set pub = ThisWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=htmlPath, _
Sheet:=ws.Name, _
Source:=rng.Address(RowAbsolute:=False, ColumnAbsolute:=False), _
HtmlType:=xlHtmlStatic)
pub.Publish True
pub.Delete
Set pub = Nothing
Application.ScreenUpdating = oldScreenUpdating
MsgBox "HTML file created: " & htmlPath, vbInformation
Exit Sub
CleanFail:
errText = Err.Description
On Error Resume Next
If Not pub Is Nothing Then pub.Delete
Application.ScreenUpdating = oldScreenUpdating
On Error GoTo 0
MsgBox "Export failed: " & errText, vbExclamation
End Sub
- Review the references before running. The macro uses
ThisWorkbook, meaning the workbook that contains the code. That is convenient when the macro is stored in the report workbook. If you keep the code in your Personal Macro Workbook or another workbook,ThisWorkbookrefers to that other file, not necessarily the workbook with your data. In that situation, deliberately change the workbook reference and confirm that the worksheet and range are in the workbook you intend to export. - Run the macro. Return to Excel and press Alt+F8. Select
ExportRangeToHTML, then select Run. You can also run it from the Visual Basic Editor with the cursor inside the procedure by pressing F5. The macro publishes the selected cells as static HTML and displays the destination path when it succeeds. If a file with that name already exists, theTrueargument allows Excel to overwrite it, so change the filename in the code if you need to keep the previous export. - Open and check the result. Use File Explorer to open the workbook’s folder and double-click
Report.html, or open it from a browser. Check that the correct rows and columns appear, the text is readable, and the formatting is acceptable at browser width. Excel exports a snapshot of the range at the time the macro runs; changes made later in the workbook do not automatically update the HTML. Run the macro again to create a fresh export. - Save and adapt the macro if needed. If you want to keep the macro for future exports, save the workbook after adding it. You can change the output name by editing
Report.htmlin the code, or select a different folder by replacing the path expression with a full path that exists and that you can write to. Keep the extension as.htmlso the file opens as a web page.
The sample uses the worksheet’s existing cell formatting as a starting point, but it is not a full interactive web page. The output is a static representation; formulas are not recalculated by a browser, and Excel features such as filters, buttons, or workbook interactions should not be expected to work as they do in Excel. If the range includes formulas, the published page presents their displayed results rather than functioning formulas for visitors to edit.
Common Mistakes
Leaving the sample sheet name unchanged. If your tab is called Sales rather than Report, the worksheet reference must say Worksheets("Sales"). Worksheet names must match the tab text, including spaces. The range address must also be valid on that sheet.
Running code from the wrong workbook. ThisWorkbook is the workbook containing the macro, while ActiveWorkbook is the workbook currently active in Excel. They can be different. If you store the macro separately, check which workbook the references target before running it; otherwise the macro may look for a sheet that is not there or publish the wrong data.
Expecting live synchronization. The HTML file is an export, not a live connection to Excel. Editing cells will not change an existing file. Run the macro again to refresh the output, and verify the destination so you do not mistake an older copy for the latest one.
Saving in an unsuitable format or location. Saving the macro workbook as .xlsx removes its VBA code. Also, a read-only folder, a network location that is disconnected, or an HTML file currently locked by another program can prevent export. Choose a writable folder and close the existing HTML file if an overwrite fails.
Tips
Give the exported range a tidy layout before publishing. Set sensible column widths, use clear headings, and remove temporary notes or helper data from the selected cells. The web page is easier to read when the range is narrow enough to fit a typical browser window. Preview it at a smaller window size as well as full screen, because wide worksheets can require horizontal scrolling.
Use a filename that identifies the report and date if you need to retain versions, such as Sales-November.html. To do that, change the filename in htmlPath rather than relying on the same name each time. If the page includes images or other supporting assets, Excel may create a related files folder next to the HTML file. Keep that folder with the HTML when sharing the export, and check the page on the recipient’s computer if the assets matter.
For a regular process, keep the export range and destination predictable, and add a clear message to the macro if you customize it. Avoid exporting whole worksheets unless that is intentional: selecting a specific range reduces clutter and lowers the risk of exposing unrelated cells. Before sending the page, review it for confidential details, hidden assumptions in the displayed values, and information that was not meant for the recipient.
On Mac, Excel has a Visual Basic Editor and supports many VBA macros, but keyboard shortcuts can vary by keyboard. Try Developer > Visual Basic, or use the Mac keyboard’s function-key combination for F11 if available; you may need to hold Fn as well. The example is written for Excel on Windows, so confirm that the publishing method is available in your Mac edition and test it on a copy. Excel for the web does not run VBA; open the workbook in the desktop app to run the macro.
Troubleshooting
“Subscript out of range” appears. This commonly means Excel cannot find the worksheet named in the code. Compare the text inside Worksheets("...") with the worksheet tab, then correct it and run the macro again. If the macro is stored in a different workbook from the data, verify the workbook reference as well.
The macro says the workbook must be saved. Save the file to a folder first, then run the macro again. The sample uses ThisWorkbook.Path to find the output folder, and an unsaved workbook has no folder path. If the workbook is saved to a location you cannot write to, change the output path to a writable local folder.
The output is missing or Excel reports a publish error. Confirm that the folder exists, you have permission to write there, and the filename is not being held open or blocked by another process. Close the HTML page in an editor if it is open, then run the macro again. If necessary, change the output name to avoid an overwrite and inspect the exact error text shown by the macro.
The page is blank, incomplete, or shows the wrong cells. Check that the selected range contains the content you expect and that the worksheet reference is correct. A range that is too small will omit rows or columns; a range that includes mostly blank cells may look empty in a browser. Update the address, export again, and compare the result with the worksheet.
Formatting looks different in the browser. HTML rendering varies between browsers, and a static export does not reproduce every Excel display feature. Check the page in another browser, simplify unusual formatting, widen narrow columns, and avoid depending on Excel-only controls. If the layout must be exact, inspect the HTML output or use a purpose-built web page rather than assuming a spreadsheet export will match pixel for pixel.
The macro is disabled or Alt+F8 does not list it. Confirm the file is an .xlsm workbook, the code is in a standard module, and the procedure is named ExportRangeToHTML and declared as shown. Open the file in desktop Excel and follow your organization’s trusted-file policy. Do not enable macros in files from unknown sources.
Frequently Asked Questions
Does this export formulas or their results?
The browser receives a static page, not editable Excel formulas. It displays the range as published, generally using the values visible in the cells. If the source formulas change, run the macro again to publish the updated output.
Can I export more than one range?
The example publishes one rectangular range per run. To export separate areas, create additional publishing code for each range and give each export a distinct filename. For nonadjacent cells, it is usually simpler to place the intended content together in a clean rectangular export area first.
Will the HTML file preserve Excel formatting?
It can retain common cell formatting, such as fonts, fills, borders, and number display, but browsers may render details differently. Review the exported page rather than assuming every Excel feature will look identical. Interactive workbook features are not carried over as working Excel controls.
Can I run this from Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot execute VBA macros. Use the desktop version of Excel for Windows or a compatible desktop edition of Excel for Mac.
Can I save the exported HTML somewhere else?
Yes. Replace the sample htmlPath assignment with a full path to a folder you can write to, for example a local folder. Check that the folder exists and that the filename ends in .html. If you share the export, include any supporting files Excel creates beside it.
Can I undo an export with Ctrl+Z?
No. Excel’s Undo command does not reverse a VBA macro. The macro writes a separate file and may overwrite a file with the same name. Keep a workbook copy before running it and choose a new output filename when you need to preserve an earlier export.
Related
- How to Autofill in an Excel Table: Steps That Work
- Power Query Data Validation in Excel: What It Can Do
- Excel Data Validation: Create Rules and Drop-Down Lists
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range.Clear: What It Clears, Keeps, and How to Use ItIn Excel VBA, Range.Clear clears the specified cells, removing their values or formulas and their cell formatting, along…
- Excel VBA Find the First Empty RowTo append data after the last entry in a known column, use Cells(Rows.Count, "A").End(xlUp).Row + 1, with a…
- Macro Recorder for Mac: Options and How to ChooseYes—Mac users can record and replay repetitive actions, but macOS does not provide one universal recorder that captures…
- VBA Send Email With Attachments From a Folder in ExcelTo send an Outlook email with every file in a folder attached, run an Excel VBA macro that…
- VBA Array Clear: How to Erase, Reset, and Reuse ArraysTo clear a VBA array, use Erase arrayName. For a dynamic array, Erase releases its allocated storage; for…
- Excel VBA: Copy a Worksheet and Rename ItTo copy a worksheet and rename the copy with VBA, use the worksheet’s Copy method, then set the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.