HTMLProject Object and HTMLProjectltem Object

Both Office 2000 and Office XP contain a special development tool called Microsoft Script Editor (MSE) that allows you to work with documents as web pages. To access this tool in Excel 2002, open the Tools menu, point to Macro, and choose Microsoft Script Editor. The MSE can also be accessed by pressing Alt+Shift+F11 or programmatically by using the HTML-Project object. Try the latter by opening any workbook you want in Microsoft Excel, switch to the Visual Basic Editor screen, activate the Immediate window, and type the following statement:


When you press Enter, the above statement will open the Active workbook in the Microsoft Script Editor (Figure B-2). Notice that the MSE user interface is similar to the VBE (Visual Basic Editor) interface. It contains a Project Explorer, a Properties window, a Toolbox, and a Code window. In addition to that, you can access the Object Browser and Document Outline windows by choosing Other Windows from the MSE View menu.

When you open an Excel workbook in the MSE, the Project Explorer displays a Sheet object for each sheet in the workbook. To see the HTML code for any sheet, double-click the sheet name in the Project Explorer. Note that the Code window displays both the HTML and XML code.

Special xls - Microsoft Script Editor - Speech

TaWe loots V^indow Help

Todbctt HTML

Panter jjjg jgj

_J Button Reset Sulton Sübmil Button 0i Texi Fiefd TexiAiea ITTi Fde Field R Password Fiekä W Checkbox f? Raefo Btfton □ TaUe Clipboard Rrg


res.htrn Spcech | Spell Check Sheet5 îtylestieetcî ^ ^ x

< html xtn 1 us : o ■* " ur n : a c hemas-tn icr o so;— XKilns : x=Burn r schémas-microsof t-cons—" xialns="http : / / wvv. w3, org/TR/ REC-hu

<Mieta http"equiv= Content-Type conti -cwieta nanie-Progld concent=£xcel <Kieta nrmie "Generator content ""Kien -cl^ink id=Main-F"He re l=Hain-I"ile h: <iijnk rel-File-List href-filelist.: <ij.nk rel=Edit-Tiroe-Data href=editi rel-3tylesheet href-styleshei <st^le> < ■—Cätole

{mso- disp lay e d- de c irnal- s epar at ■ ms o-d isp1ayed-efto usand-s e par ac i ßpage

Project Explorer - Spéciales

Ip 50

ffil Special, xls 3) Specié.Kls ¡$1 slylesheetcss ~ Speech Spei Check Shee© _ Sheet4 Sheer3 ffi Miscellaneous Fies


<► X



i, üb a


- -



Document baekgiotrd color.

Figure B-2: The Microsoft Script Editor (MSE)

Using the Microsoft Script Editor is beyond the scope of this book. You can learn all you want about this subject by using the built-in help. Simply choose Microsoft Script Editor Help from MSE's Help menu to gain access to various topics (Figure B-3).

Using the HTMLProject object and HTMLProjectItem object from the Microsoft Office XP object model, you can access and manipulate the HTML objects of an Excel workbook or another Office document.

The HTMLProject object represents the HTML code in an Office document. The HTMLProject object contains a collection of HTMLProjectItem objects. Each HTMLProjectItem represents an individual project item, as displayed in the Project Explorer in the Microsoft Script Editor. You can use the Name property to return and display the name of the project item. Use the Count property of the HTMLProjectItems collection to return the number of project items in the HTML project for the specified workbook. The

¡^ Microsoft Script Editor Help mm^m mM

Contents | Answer Wizaid | ircfex \

lyj Microsoft Script Editor

_?] Microsoft Script Editor Hsip 7] Aboui ihe Microsoft Saipl Edtcu Wndow ?] Aboui. ihe Refiesh Toobar ?] About Ihe Defcug Tods

SHTML in Miciosoft Dflice Applcatcns Workrg wih Script and HTML in ihe Script Editor ^ Custornizing Ihe Sctipl Ecftot ^ Menagng Views and Wndows tNavgathg, Searchng and RepSacing Ujirg Ire Debugger

Iii HTML RefeienceHelp tU DHTML HTML fc CSS Rcference ^ HIML Relerence; ]?] HTML Applications |HTA) Refeience % DHTML Refeiences % DHTML Saipllets ¡7] HTML Compcoerts|HrC| Retefence £ CSS G2l JSeri* Heb ^ JScnpt IjJ VßSciipt Häp VBSaipl

About the Microsoft Script Editor window

The ncrosoft Script Edtor window supports viewing HTML f<? all Office applications that support saving documents h html. At any given time, there is only one Microsoft Script Edtcr window into which all open Office apptcotons tan load ther html- In that same window you can create new html fles or open existing ones from any avail=fcle locabon.

You can use the Project Explorer window in the Microsoft Script Editor to nav^jate among all open HTML pages, whether they were haded from Office documents open in Office applications, or were fifes opened drectty from a disk or network server. Ycu can activate a particular HTML fie by double-clicking k in the Project Explorer whdow, by selecting ¡L from the list windows on the Window menu, or by dlcfcng the tab for the HTML file on the s&ries of tabs at the top of the document window,

A Design button and an HTML button are located at the bottom of the document window, wh*h mdcate the view that the active html fie is h (Design cr html), tote that Microsoft Office documents cannot be dsplayed in Design view in the Microsoft Script Edtor. If the active HTML file represents an open Office document, ths Design button is disabled and dsplays an atert when deked. You must activate the Offce applcaten to modify the document in Design view. !f the active html file was newly created ot was opered from a disk or network, server, the Design button is enabted and you tan edit the html fHe in Design view. To view you- html fie as it wil be displayed h the browser, ekek the View in Browser corrmand cn the File menu. The Microsoft Script Editor launches a new browser window and loads the active html flemtolt.

Figure B-3: Study the Microsoft Script Editor Help to learn about scripting.

following procedure will get you started working with these objects and their properties and methods programmatically. Notice that after retrieving the number of project items in the current HTML project of the Active-Workbook, the procedure loops through these items and prints their names to the Immediate window. If the found project item's name is "Speech," the procedure will store the HTML text of the project item in a text file.

Sub HTML_Project()

Dim objHTMLPrj As HTMLProject Dim itm As Variant

Set objHTMLPrj = ActiveWorkbook.HTMLProject

With objHTMLPrj

Debug.Print "The HTML Project contains " & _

.HTMLProjectItems.Count & " items as follows:" For Each itm In .HTMLProjectItems Debug.Print itm.Name If itm.Name = "Speech" Then

.HTMLProjectItems(itm).SaveCopyAs "C:\Scores.txt" End If


If .State = msoHTMLProjectStateDocumentProjectUnlocked Then .Open

End If End With End Sub

This page intentionally left blank

0 0

Post a comment