Creating and Publishing HTML Files Using VBA

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Like previous versions, Excel 2002 allows you to save files in the HTML (Hypertext Markup Language) format. This format is recognized by Excel just like its standard .xls format. When you save an Excel file in the HTML format, you can view the spreadsheets using an Internet browser, such as Internet Explorer or Netscape Navigator. When you save a workbook or its part as HTML, Excel saves the options of the original workbook. Thanks to this, the user can view the file either in the browser or inside the Microsoft Excel application window.

Excel 2002 is capable of saving data and charts as interactive web pages. While saving an entire workbook or its part in the HTML format, you can choose between creating a static or interactive HTML document and specify a location where the file should be saved. You can save your files directly to the web server, a network server, or a local computer.

Working with nothing else but the user interface (File | Save as Web Page) you can place an entire workbook or its parts on the web page, so users can work with its information interactively or only view the data. Detailed instructions on how to place an entire workbook or any worksheet

(or one of its elements — such as chart, PivotTable report) on a web page can be found in Excel online help. Because this book is about programming, we will focus only on the way these tasks are performed via VBA code.

Useform Menu

Figure 16-7:

The Publish as Web Page dialog box appears after clicking the Publish button on the Save As dialog box (see Figure 16-6).

Figure 16-6:

The Save As dialog box that appears after choosing Save As Web Page from the File menu allows saving the workbook as a web page.

Figure 16-7:

The Publish as Web Page dialog box appears after clicking the Publish button on the Save As dialog box (see Figure 16-6).

The Visual Basic for Applications (VBA) object library in Excel 2002 offers objects for publishing worksheets on web pages. To programmatically create and publish Excel files in the HTML format, you should become familiar with the PublishObject object and the PublishObjects collection.

PublishObject represents a worksheet element that was saved on a web page, while PublishObjects is a collection of all PublishObject objects of a specific workbook. To add a worksheet element to the PublishObjects collection, use its Add method. This method will create an object representing a specific worksheet element that was saved as a web page. The format of the Add method looks like this:

expression.Add(SourceType, Filename, [Sheet], [Source], [HtmlType], _ [DivID], [Title])

The arguments in square brackets are optional. Expression returns an object that belongs to the PublishObjects collection. SourceType specifies the source object using one of the following constants:

Constant

Description xlSourceAutoFilter An AutoFilter range xlSourceChart A chart xlSourcePivotTable A PivotTable report xlSourcePrintArea A range of cells selected for printing xlSourceQuery A query table (an external data range)

xlSourceRange A range of cells xlSourceSheet An entire worksheet

Filename is a string specifying the location where the source object (SourceType) was saved. This can be a URL (Unified Resource Locator) or the path to a local or network file. Sheet is the name of the worksheet that was saved as a web page. Source is a unique name that identifies a source object. This argument depends on the SourceType argument. Source is a range of cells or a name applied to a range of cells when the SourceType argument is the xlSourceRange constant. If the argument of SourceType is a constant, such as xlSourceChart, xlSourcePivotTable or xlSourceQuery, Source specifies a name of a chart, PivotTable report, or query table. HTMLType specifies whether the selected worksheet element is saved as an interactive Microsoft Office web component or static text and images. This can be one of the following constants:

Constant Description xlHTMLCalc xlHTMLChart xlHTMLList

XlHTMLStatic

(default value)

Use the Spreadsheet component. This component makes it possible to view, analyze, and calculate spreadsheet data directly in an Internet browser. This component also has options that allow you to change the formatting of fonts, cells, rows, and columns.

Use the Chart component.

This component allows you to create interactive charts in the browser.

Use the PivotTable component.

This component allows you to rearrange, filter, and summarize information in a browser. This component is also able to display data from a spreadsheet or a database (for instance Microsoft Assess, SQL Server or OLAP servers).

Use static (non-interactive) HTML for viewing only.

The data published in an HTML document does not change.

Note: The Office Web Components allow you to use Excel analytical options in an Internet browser.

DivlD is a unique identifier used in the HTML DIV tag to identify the item on the web page. Title is the title of the web page.

Before we can see how to use the Add method from a VBA procedure, you also need to learn how to use the Publish method of the PublishObject. This method will allow you to publish an element or a collection of elements in a particular document on the web page. This method is quite simple and looks like this:

expression.Publish([Create])

Expression is an expression that returns a PublishObject object or PublishObjects collection. The optional argument, Create, is used only with a PublishObject. If the HTML file already exists, setting this argument to True will overwrite the file. Setting this argument to False inserts the item or items at the end of the file. If the file does not yet exist, a new HTML file is created, regardless of the value of the Create argument.

Now that you've been introduced to VBA objects and methods used for creating and publishing an Excel workbook in HTML format, you can begin programming. In the following exercises, you will create two VBA procedures. The first one will create and publish an Excel worksheet with an embedded chart as static HTML. The second procedure will demonstrate how the same worksheet can be made available as an interactive web page. 1. Create a worksheet and chart, as shown in Figure 16-8.

Figure 16-8:

A worksheet like this one with an embedded chart can be placed on a web page using the Save As Web Page option on the File menu or programmatically from a VBA procedure.

Figure 16-8:

A worksheet like this one with an embedded chart can be placed on a web page using the Save As Web Page option on the File menu or programmatically from a VBA procedure.

2. Save the workbook in a file named PublishExample.xls.

3. Activate the Visual Basic Editor window, and insert a new module into the current VBA project.

4. In the Code window, enter the two procedures shown below. The first one, PublishOnWeb, publishes on a web page a worksheet with an embedded chart as static HTML. The second procedure, CreateHTML-File, calls the PublishOnWeb procedure and feeds it the two required arguments: the name of the workbook that you want to publish and the name of the HTML file where the data should be saved.

' The procedure below will publish a worksheet

' with an embedded chart as static HTML

Sub PublishOnWeb(strSheetName As String, strFileName As String)

Dim objPub As Excel.PublishObject Set objPub = ThisWorkbook.PublishObjects.Add( _ SourceType:=xlSourceSheet, _ Filename:=strFileName, Sheet:=strSheetName, _ HtmlType:=xlHtmlStatic, Title:="Calls Analysis") objPub.Publish True

End Sub

Sub CreateHTMLFile()

Call PublishOnWeb("Help Desk", "C:\WorksheetWithChart.htm")

End Sub

5. After entering both procedures, run the procedure named Create-HTMLFile. When this procedure finishes, you will see a new file called C:\WorksheetWithChart.htm. Also, there will be a folder named WorksheetWithChart_files storing supplemental files.

6. In Windows Explorer, double-click the C:\WorksheetWithChart.htm file created in step 5. This action will cause the published worksheet to appear in an Internet browser (Figure 16-9).

Activeworkbook Publishobjects Add

Figure 16-9:

An Excel worksheet published as a static (non-interactive) web page

Figure 16-9:

An Excel worksheet published as a static (non-interactive) web page

To interactively publish the example worksheet with the embedded chart shown in Figure 16-8, perform the following:

1. In the Visual Basic Editor window, insert a new module into the current VBA project.

2. In the Code window, enter the InterHTML procedure, as shown below.

Sub InterHTML() Dim strSheetName As String strSheetName = "Help Desk"

' ensure that the chart is not selected Range("A1").Select ActiveWorkbook.PublishObjects _

.Add(xlSourceChart, "C:\Inter_WorksheetWithChart.htm", _ strSheetName, "Chart 1", xlHtmlChart).Publish (True)

End Sub

3. Run the procedure you've just created.

4. In Windows Explorer, double-click C:\Inter_WorksheetWith-Chart.htm. This will cause an Internet browser to be activated with the interactive chart (Figure 16-10). The web page also contains the interactive worksheet that supplies the chart data.

Activeworkbook Publishobjects Add

Figure 16-10:

An interactively published Excel worksheet can be modified directly in the browser.

Figure 16-10:

An interactively published Excel worksheet can be modified directly in the browser.

5. Change any values in column B, and see the changes on the chart.

Note: To interactively use data, you must have Microsoft Internet Explorer 4.01 or higher and Microsoft Office Web Components installed on your computer.

Was this article helpful?

0 0

Responses

Post a comment