Saving Documents as Web Pages [Excel 2003 VBA Language Reference]
In Microsoft Excel, you can save a workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. You can also edit HTML files directly in Excel.
Saving a Document as a Web Page
Saving a document as a Web page is the process of creating and saving an HTML file and any supporting files. To do this, use the SaveAs method, as shown in the following example, which saves the active workbook as C:\Reports\myfile.htm.
ActiveWorkbook.SaveAs _ Filename:="C:\Reports\myfile.htm", _ FileFormat:=xlHTML
Customizing the Web Page
You can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions object and the WebOptions object. The DefaultWebOptions object contains application-level properties. These settings are overridden by any workbook-level property settings that have the same names (these are contained in the WebOptions object).
After setting the attributes, you can use the Publish method to save the workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. The following example sets various application-level properties and then sets the AllowPNG property of the active workbook, overriding the application-level default setting. Finally, the example saves the range as "C:\Reports\1998_Q1.htm."
With Application.DefaultWebOptions .RelyonVML = True .AllowPNG = True .PixelsPerInch = 96 End With With ActiveWorkbook .WebOptions.AllowPNG = False With .PublishObjects(1) .FileName = "C:\Reports\1998_Q1.htm" .Publish End With End With
You can also save the files directly to a Web server. The following example saves a range to a Web server, giving the Web page the URL address http://example.homepage.com/annualreport.htm.
With ActiveWorkbook With .WebOptions .RelyonVML = True .PixelsPerInch = 96 End With With .PublishObjects(1) .FileName = _ "http://example.homepage.com/annualreport.htm" .Publish End With End With
Opening an HTML Document in Microsoft Excel
To edit an HTML document in Excel, first open the document by using the Open method. The following example opens the file "C:\Reports\1997_Q4.htm" for editing.
After opening the file, you can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions and WebOptions objects.