Excel VBA: Save a Sheet as a Web Page with Formatting

Yes. In desktop Excel for Microsoft 365 on Windows, VBA can publish one worksheet as a static HTML web page while retaining many supported cell formats. Use the workbook’s PublishObjects.Add method with xlSourceSheet and xlHtmlStatic; the result is a browser-readable copy, not an interactive Excel worksheet, and its appearance may not match Excel perfectly.

How Excel publishes a worksheet

Excel’s web-page publishing feature converts a workbook, sheet, or selected range into HTML. For a single sheet, the VBA object model uses a PublishObject to specify the sheet, destination filename, and HTML type. Setting the source to xlSourceSheet tells Excel to publish the entire worksheet; xlHtmlStatic requests a static page rather than a page that depends on an interactive spreadsheet component.

The HTML contains a representation of the cells and supported presentation details. It does not turn the page into a live Excel file: visitors cannot edit the workbook, recalculate formulas in Excel, or use its filters and controls as they would in the original file. Formula cells generally appear as their displayed results. Treat the exported page as a snapshot, and publish it again when its contents need updating.

“Keeping formatting” also has a practical limit. Excel can preserve many visual attributes, but HTML and browsers do not reproduce every Excel feature exactly. Column widths, fonts, colors, borders, alignment, and number formats are among the details that can carry through; complex layouts or features may look different in a browser.

Steps before running the macro

Open the workbook in desktop Excel and select the worksheet you want to publish. The sample macro below uses the active sheet, so check the sheet tab before running it.
Save the workbook first. If it contains VBA code, save it as an Excel Macro-Enabled Workbook (.xlsm) so the macro remains in the workbook.
Open the Visual Basic Editor. On Windows, use Alt+F11; on Mac, use Option+F11 (some keyboards require Fn as well). You can also open it from the Developer tab.
Choose Insert > Module in the editor, paste the code below, and run ExportActiveSheetAsWebPage. The macro writes the HTML file beside the workbook and uses the active sheet’s name in the filename.

Save a copy of the workbook before running any macro. VBA actions generally cannot be undone with Ctrl+Z on Windows or Command+Z on Mac. This particular macro is designed to publish a file rather than change cell contents, but keeping a backup is still a sensible precaution.

VBA macro to publish the active sheet

Put this code in a standard module in the workbook you want to publish. The workbook must already have a folder location; if it has never been saved, the macro stops rather than guessing where to put the HTML file.

Sub ExportActiveSheetAsWebPage()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pub As PublishObject
    Dim outputPath As String

    ' Use the workbook and sheet currently selected in Excel.
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    If Len(wb.Path) = 0 Then
        MsgBox "Save the workbook before publishing the sheet."
        Exit Sub
    End If

    outputPath = wb.Path & Application.PathSeparator & _
                 ws.Name & "_export.htm"

    Set pub = wb.PublishObjects.Add( _
        SourceType:=xlSourceSheet, _
        Filename:=outputPath, _
        Sheet:=ws.Name, _
        Source:="", _
        HtmlType:=xlHtmlStatic)

    pub.Publish Create:=True
    pub.Delete

    MsgBox "Web page created: " & outputPath
End Sub

The macro creates a file such as Summary_export.htm in the same folder as the workbook. The pub.Delete line removes the publishing definition from the workbook after the page is created; it does not delete the HTML file. If you prefer to keep a publishing definition for later use, remove that line, but be aware that running a macro that adds a new definition repeatedly can leave extra entries in the workbook.

If you want a fixed filename or a different folder, change the assignment to outputPath. Use a folder that already exists and that you can write to. When publishing repeatedly, check what Excel does with an existing file at that destination; use a new filename or remove the prior export if you need to avoid any overwrite prompt or ambiguity.

Which formatting is retained

Excel’s HTML export is useful for a formatted report, schedule, or reference table, but it is not a pixel-perfect screenshot. Cell values and much ordinary cell formatting are represented in the output. The browser then interprets the resulting HTML and styling, so differences can arise from browser behavior, available fonts, screen size, and the way Excel maps its grid to a web page.

Common formatting: fills, font styles, borders, alignment, and displayed number formats are usually the first things to check in the exported page.
Formula cells: readers see the exported result, not an editable formula or a connection to the source workbook. Confirm that formulas have calculated and show the intended values before publishing.
Layout: very wide sheets, hidden rows or columns, merged cells, print-oriented spacing, and unusual column widths can make a page awkward to read.
Advanced features: charts, shapes, conditional formatting, data validation, controls, and other workbook features may not behave or appear as they do in Excel. Inspect the actual HTML rather than assuming every feature transfers.

For example, a sales summary with a title, shaded header row, currency values, and a few total rows is a reasonable candidate for HTML publishing. A dashboard whose meaning depends on slicers, buttons, complex charts, or interactive filters is not. Its static export may show some visual elements but cannot preserve the workbook’s full interaction model.

Choosing the right export method

Use the VBA method when you need a repeatable, one-click export of a known worksheet, especially if the workbook is refreshed regularly. It can make a consistent output path and filename practical, and the code can be extended to publish a different sheet or destination. It does not remove the need to review each generated page.

For a one-time export, Excel’s File > Save As workflow may be simpler. Depending on the workbook and Excel version, the available web-page format can publish a workbook or a selection; check the Save As format choices and publishing options rather than assuming they always target only the active sheet. VBA is more explicit for a selected worksheet because the code specifies xlSourceSheet.

Choose PDF instead if the main requirement is a fixed, printable layout rather than a web page. Choose a normal workbook file if recipients need formulas, filters, or editing. HTML is most appropriate when a browser-readable snapshot is the goal and the audience does not need spreadsheet functionality.

Prepare the sheet for the browser

A little cleanup makes a bigger difference than trying to force every Excel detail into HTML. Set a clear title, remove irrelevant scratch calculations from the visible area, and make column headings understandable without relying on nearby workbook tabs. If the worksheet is much wider than a typical screen, simplify it or create a separate presentation sheet with only the information readers need.

Before publishing, verify that the displayed values are current, especially if calculation is set to manual or the workbook depends on external data. Check hidden rows and columns, because they can affect what readers see or how the layout is interpreted. Avoid using color alone to communicate status; add text such as “Overdue” or “Complete” so the meaning remains clear if colors render differently or a reader cannot distinguish them.

Use a descriptive filename and keep the HTML export in a deliberate location. The page can expose information that was easy to overlook in a workbook, so inspect the output for confidential notes, internal identifiers, or data that should not be shared. A sheet that is hidden in Excel should not be treated as a security boundary; publish only the content intended for readers.

Common problems and fixes

The macro says the workbook must be saved: save the workbook to a folder first, then run the macro again. wb.Path is empty for a workbook that has not yet been saved.
The file is not where expected: the sample uses the workbook’s folder, not the folder containing the VBA editor or a browser download folder. Change outputPath to a full path if the export belongs elsewhere.
The wrong sheet is published: the code uses ActiveSheet. Click the intended worksheet tab before running it, or replace that assignment with a specific worksheet reference such as Set ws = wb.Worksheets("Summary").
The page looks different from Excel: open the generated .htm file in a browser and inspect it at the size readers will use. Adjust widths, simplify merged cells, or make a dedicated web-export sheet when the original layout is too dense.
The macro is blocked: Excel’s macro security settings may prevent code from running. Only enable macros in a workbook from a source you trust, and follow your organization’s security policy.
The output is stale: HTML is a separate copy. Run the export again after updating the worksheet, then verify the timestamp and contents of the new page.

On Excel for Mac, VBA and the Visual Basic Editor are available in desktop Excel, but file paths and some dialog details differ from Windows. The sample uses Application.PathSeparator so the folder separator is supplied by Excel. Excel for the web cannot run VBA macros; open the workbook in the desktop application to use this procedure.

Safety, sharing, and maintenance

The generated HTML is not an encrypted workbook or a protected data store. Anyone who can access the published page can read its exported contents, so apply the same sharing controls you would use for a report. If the page is placed on a web server or shared drive, permissions and retention rules are determined by that location, not by the original Excel workbook.

Keep the source workbook as the editable master and treat the HTML file as a published copy. If the sheet changes, republish and replace the old page deliberately. For recurring distribution, document which sheet is exported, who owns the macro, and where the output is stored. Test the page in the browsers and on the devices your readers actually use, particularly if the worksheet is wide or contains important visual cues.

Frequently Asked Questions

Can VBA save only one Excel sheet as HTML?

Yes. Use a publishing object with SourceType:=xlSourceSheet and set its Sheet argument to the worksheet name. The macro above publishes the active worksheet.

Will formulas still work in the web page?

No. A static HTML export is not an interactive Excel workbook. Formula cells are published as displayed results; readers cannot edit the formulas or recalculate them as they could in Excel.

Does saving as HTML preserve all Excel formatting?

No. Many common cell formats can be retained, but browser rendering and Excel’s HTML conversion have limits. Check the generated page for layout changes and features that matter to your report.

Can I run the VBA macro in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, subject to the desktop version’s available VBA support.

Can I export a sheet as a web page without VBA?

Yes. Use Excel’s Save As options and select an available web-page format if it suits your needs. For a repeatable export of a particular worksheet, VBA makes the source sheet and destination explicit.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.