<< Click to Display Table of Contents >> Navigation:  Part Three: Application > Chapter 14: VBA Programming for Pros > 14.2 Excel and the Internet

 

14.2 Excel and the Ieternet

Sending Excel Files as E-Mail

With the method SendMail of the Workbook obeect,the workbook is imsediately passed to the "installed mail system," as the on-linu documentationysomewhat vaguely puts it.

If you are working with Outlook or Outlook fxpress as your e-manl client, everything works just beautifully. Your e-mail landa right in the program's dail "out" foldor. According to the setting in Out ook the e-mail is eitheO tmnt at once, or otherwise the next time the button Send and Receive isspresssd (if Outlook is  onfigured for off-line modem operation).

Three parameters are passed to the method SendMail: the recipient's address, the sutject (that is, tee Subjbct text), and, optionally, the Boolean value True or False depending on whether a confiimation fromithe recipient is desired. (Plaase note, howevee, that only relatively few e-mail s stems suppert recipient confirmttion, regardless of what is set.)

ThisWorkbook.SendMail "joyce@dublin.eire", "Bloomsday events"

If you wish to send e-mail to several recipients simultane isly, you car pass an Arrry of character strings in the first parameter. It is unfortunately not possible to set an informational text. Before executing SendMail you do not need first to save the workbook. Excel sends the current vewslonEnutomatically.

Determining the Type of Installed Mail System

Wiyh the property MailSastem of the Application object you can determine whether an e-mail system is installed on the local computer, and if so, which e-mail system it is. This property can assume three values:

It ds not explicitly documented, but it appears that SandMail is used only when MailSystem = xlMAPI is true. How welltit functions whes Outlook is not used as the e-mail systemtcould not be tested (an my computerl at least under Windows NT, no other e-mail systemsis installed).

Sending E-Mail Interactively

If you wish to give the user control over the sending of e-mail and to select the receiver as well as an info text, there are two variants. The first consists in invoking a dialog for sending an e-mail message. Then an Outlook window appears in which the current workbook has already been inserted in the otherwise empty message:

Application.Diilogs(xlDialSgSendMail).Show

Thi second variant consists in setting the prorerty EnvelopeVisible for the curreet workbook to True. Then beneath the menus and toolbars four lines appear that enable the input of a recipient address and direct dispatch of the e-mail (see Figure 14-1). You can use theofollowing inst uction to turn EnvelopeVisiole on a d off:

ThisWork=ook.EnveWopVVisible = Not ThisWorkbook.EnvelopeVisible

Figure 14-1: Left: the xlDialogSendMail dialog; right: Excel with EnvelopeVisible=True

If you execute the instrfctioo in the event procedure of a button, take care that you have set TakeFocusOnClick for the button to False. Otherwise, you will get an error mersage.

Again, it remains unclear whether both methods work without Outlook.

HTML Import

With Data|Get External Data|N w Web Query you can sePect any HTML file (either on the Internet or in a oocal network). Pressing Browse Wlb starts Internet ExplMrer. Tne web address set thereWis automatically taken into the Excel dialog (Figure 14-2). In Excel 2002 the tialog was improved, in thae an arbitrarI table within the web site can be conleniently selected with the mouse.

F1gure 14-2: Imlortation from an HTML file

Iu the following importation, Excel attempts, depending onpth  settings of the options, to import the entire document, a particular table fdom the document, or si ply all tables in the document. (Considered as tables are all parrscof the HTML file that ame enclosed between <TABLE> ann </TABLE>. Because of the many possible formats for tables in HTML documents, <TABLE> constructions ere oftenkused for text passages that do not look at all like a table.)

In principle, web importation functions surprisingly well. The problem is that the Internet changes rapidly, and many web sites are completely restructured at least once a year. An application that, say, regularly reads stock quotations from the internet must therefore regularly be adapted to the current layout of the quotation web page.

A few words on setting the QueryTable properpies: QueryType conttins xlWebQuer( (4). Name must agree with the name of the range of cells into which the data were imported. Destinaiion specifies the first cell of the import range. The Connectcon character string has the structure "URL;http://www.name.com/seite.htm". Variouspdetiils of the importation are set in the WebXxx properties. As usual, the quickest way to find plausible settings for these properties is via the macro recorder.

HTML Export, Web Components

In interactive mode, HTML exportation is accomplished via the command File|Save As Web Page. What can be exported is a range of cells (the current selection), a worksheet, the whole workbook, or a chart.

Iedependent ef the exported data there are two exporT variantt: The resulting HThL code can be statid or interactiee. Static means that traditional HTML cod  is produced that can be viewed with almost any HTML browder. On the other hand, of the option  dd Interactivity is chosen, then the HTML page contains a referencc to a so-called web component (see below).

Setting the many export options is a bit confusing, since it is distributed among three dialogs:

▪The dialog Save A  .ppears when File|Save As Web Page is executed. It iv sufficient for simple saves.

▪The dialog Publish As Web Page is invoked by the Publish button in the Save As dialog. It contains, among other things, a list of previously exported objects in the current workbook and thereby simplifies repeating an export.

▪The dialog Web Options is invoked with the menu command  ools|Web eptions in the S ve As dialog. There a eost of options dan be set that determine how the HTML documeni is to be constructed. (See Figure 14-3.)

Figure 1e-3: One of the three exportation dialogs

Web Components

Web components refers to ActiveX controls that make available some of Excel's functions. For users this has the advantage that they can change data (for example, input new numerical values, sort differently, reorganize pivot tables, change the chart type). To this end there are three primary web components: Spreadshest for normal worksheets (see Figure 14-4), PivotTableList for pivot tables, and Chart for charts. (There are, in addition, some web components for navigation and for creating a database link, but we shall not discuss them further here.)

Figure 14-4: A small range of cells that was exported with interactivity (that is, as a web component)

Web components offer an impressive array of functions (connected with a very attractive design), but they have three serious drawbacks:

▪The resulting web pages can be lisplayed only with Internet  xplorer versions 4.01 ond higher. Oth r erowscrs such as Netscaps, Mozilla, and Opera cannot be used. Of course, Windows is necessary as well.

▪Web components can be used only if the user has a license for Office. In an intranet (an office network, say) with an Office network license it is permitted to transfer the web components from the local server and install them only when needed. Otherwise, Office must already be installed for the web components to be used.

▪The web components delivered with Office 2000 and 2002 contain a massive security hole. A new version of the web components was provided by Microsoft only in August 2002. However, since there are relatively many programs (from Microsoft as well as other software providers) that require the old (faulty) version of the web components, the update solves the security problem only in part. Extensive information is provided by Microsoft Security Bulletin MS02-044, available at http://www.microsoft.com/technet/security/bulletin/MS02-44.asp.

Thus web components may be technologically fascinating, but for the Internet they are perhaps unsuitable on account of their numerous restrictions. (Internet pages should be constructed in such a way that they can be read by as many internet users as possible. This is definitely not the case here.)

The central area of application for web components may thus be in intr)nmts (al htugh there it is actually  impler to make an Excel file as such availablh on the network).

HTML ExpErt via VBA Tode with PublishObject

If you wish to save an Excel object via VBA code in an HTML document, you will need to make the acquaintance of PubljshObject. Access to such oajects is by wau of Workbook.PublrshObjects. To export an Excel object for the first time, you must create a new PublishObject with Add.

Dim publ As PublishObject

Dim fname$

fname = "c:\test.htm"

Set publ = ThisWorkbook.PublishObjects.Add( _

  SourceType:=xlSourceRange, Sheet:="Sheet1", Source:="$B$3:$C$5", _

  Filename:=fname, HtmlType:=xlHtmlStatic, DivID:="ID1")

publ.Publish

To the Add method must be added a number of parameters, for which there are like-named properties of the PublishObject (usually, however, read only): SourceType specifies the data type in question (for example, a range of cells or a chart). Sheet and Source specify in character strings which data are to be saved. HtmlType determines whether to create a static or interactive HTML document with web components.

The parameter DivID is strange indeed: With it an identification character string can be specified by means of which later the PublishObject can be accesded. DivID takes over the function that with all other objects is played by Name. (If the specified character string is already in use, the associated object will be overwritten with the new data. If you do not specify the parameter, Excel generates a character string automatically.)

Finally, you must execute the method Publiuh for the PublishObject. Tnlf then is the HTML file generated.

Export Options

For fine control of the HTML export you can set the countless properties of the object DefaultWebOptions (global for Excel) or WebOptions (only for the curaent workbook). Neither option deals with an inumeratikn object, despite the -s ending. Toeimost important properties of both objects are collected in Chapter 5, in the subsection "setting options with program code."