If you have the Internet Assistant Wizard add-in (Html.xla)
installed, Microsoft Excel 97 or Excel 2000 provides a menu option on the File
menu to save a worksheet as an HTML file.
This article provides
sample code to do the same thing through Automation, using the htmlConvert
macro provided by the Wizard. The code requires Excel 97 with Service Release 2
or a later version of Excel.
To use the Internet Assistant Wizard through the user
interface of Excel, the Html.xla workbook must be added to the Add-Ins list
(from the Tools menu), and it must be checked to show that it is installed.
Since an .xla file is a form of a workbook, it must also be opened; this is
done for you automatically by the Add-In manager when working in Excel
interactively, but NOT when working in Automation. Once opened, from the File menu, click Save as HTML and work with the Wizard to save the file in HTML
format.
To accomplish the same thing in Visual C++, using Microsoft
Foundation Classes (MFC), you use the Run member of the Excel _Application
object to run the macro direct rather than working through a wizard.
With adaptation, the Visual C++ code in this article can be used in your
application. However, the purpose of this article is to help you learn, both by
walking through the code and by running the program.
The example
assumes a workbook exists named "Book1.xls" in the root of drive C:\. It has a
range of values from $A$7 to $D$10.
Follow steps 1 through 13 in the following Microsoft
Knowledge Base article to create a sample project:
178749
(http://support.microsoft.com/kb/178749/
)
How to create an automation project using MFC and a type library
When working with Microsoft Excel 2002 or a later version of Excel, the typelib is incorporated into the Excel executable. By default,
Excel.exe is located at one of the following locations:
Microsoft Office Excel 2007: C:\Program Files\Microsoft Office\Office12
Microsoft Office Excel 2003: C:\Program Files\Microsoft Office\Office11
Microsoft Excel 2002: C:\Program Files\Microsoft Office\Office10
Add the following code to the CAutoProjectDlg::OnRun()
event handler in the AutoProjectDlg.cpp file:
//Sample code
// char buf[1024]; // General purpose buffer.
// Convenient variables. Uncomment before shipping.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application oApp;
_Workbook oBook;
Workbooks oBooks;
_Workbook oHTML;
Worksheets oSheets;
_Worksheet oSheet;
Range oRange;
AddIns oAddIns;
AddIn oAddIn;
VARIANT ObjToConvert[1];
ObjToConvert[0].vt = VT_DISPATCH;
ObjToConvert[0].pdispVal = oRange; //We'll set this value later.
//Launch Excel and make it visible.
oApp.CreateDispatch("Excel.Application");
oApp.SetVisible(TRUE);
//Get the Workbooks collection.
oBooks = oApp.GetWorkbooks();
/* Excel 2000 takes 13 arguments
//Open the test workbook.
oBook = oBooks.Open("C:\\Book1.xls", // This is a test workbook.
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional); // Open for Excel 2000 has 13 parameters
/*
/*
// Excel 2002 takes 16 arguments
oBook = oBooks.Open("C:\\Book1.xls", // Filename,
covOptional, // UpdateLinks,
covOptional, // ReadOnly,
covOptional, // Format,
covOptional, // Password,
covOptional, // WriteResPassword,
covOptional, // IgnoreReadOnlyRecommended,
covOptional, // Origin,
covOptional, // Delimiter,
covOptional, // Editable,
covOptional, // Notify,
covOptional, // Converter,
covOptional, // AddToMru,
covOptional, // Local,
covOptional, // CorruptLoad,
covOptional // OpenConflictDocument
);
*/
// Excel 2003 takes 15 arguments
oBook = oBooks.Open("C:\\Book1.xls", // Filename,
covOptional, // UpdateLinks,
covOptional, // ReadOnly,
covOptional, // Format,
covOptional, // Password,
covOptional, // WriteResPassword,
covOptional, // IgnoreReadOnlyRecommended,
covOptional, // Origin,
covOptional, // Delimiter,
covOptional, // Editable,
covOptional, // Notify,
covOptional, // Converter,
covOptional, // AddToMru,
covOptional, // Local,
covOptional // CorruptLoad,
);
/*
// Excel 2007 takes 15 arguments
oBook = oBooks.Open("C:\\Book1.xlsx, // Filename,
covOptional, // UpdateLinks,
covOptional, // ReadOnly,
covOptional, // Format,
covOptional, // Password,
covOptional, // WriteResPassword,
covOptional, // IgnoreReadOnlyRecommended,
covOptional, // Origin,
covOptional, // Delimiter,
covOptional, // Editable,
covOptional, // Notify,
covOptional, // Converter,
covOptional, // AddToMru,
covOptional, // Local,
covOptional // CorruptLoad,
);
*/
//Get the Worksheets collection.
oSheets = oBook.GetWorksheets();
//Get the worksheet 1.
oSheet = oSheets.GetItem(COleVariant((short)1)); // Worksheet 1
//Get the AddIns collection.
oAddIns = oApp.GetAddIns();
//Search for the HTML.XLA AddIn.
long lCount = oAddIns.GetCount();
for (long l = 1; l<=lCount; l++)
{
oAddIn = oAddIns.GetItem(COleVariant((long)l));
if(oAddIn.GetName() == "HTML.XLA")
/*
//When using Excel 2007 refer to the XLAM
if(oAddIn.GetName() == "HTML.XLAM")
*/
{
break; // AddIn is checked in the list
}
}
//Make sure the HTML.XLA AddIn is installed.
if(!oAddIn.GetInstalled())
{
AfxMessageBox("Installing AddIn");
oAddIn.SetInstalled(TRUE);
}
// The addin can be in the list, and can be installed,
// but it is a workbook (.xla) which must be open also.
oHTML = oBooks.Open(oAddIn.GetFullName(), // Returns a _Workbook
// object reference.
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional /*13 arguments for Excel 2000*/,
covOptional, covOptional
/* 15 Arguments for 2003 and 2007*/
// ,covOptional
/* 16 arguments for 2002*/
);
//Get the Range we want to convert to HTML.
oRange = oApp.GetRange(COleVariant("A7"), COleVariant("D10"));
// Using active worksheet, pass an array of LPDISPATCH
// variables, which can have one or many elements.
// We pass only one range in this example.
ObjToConvert[0].pdispVal = oRange;
AfxMessageBox("Check the Macros list\n"
"There should be nothing there.\n"
"You're about to run a hidden macro\n"
"It is in the .xla", 327744);
//Call the htmlconvert macro. We use the Application.Run method to
//do this.
VARIANT Result;
Result = oApp.Run(COleVariant("htmlconvert"), // The "macro" name
COleVariant(ObjToConvert), // An array of Variants
// which are the table ranges and
//charts you wish to convert.
covFalse, //UseExistingFile
covFalse, //UseFrontPageForExistingFile
covFalse, //AddToFrontPageWeb
COleVariant("1252"), //CodePage (1252 U.S./Western Europe)
COleVariant("c:\\Book1111.htm"), //HTMLFilePath
COleVariant("Test Page"), //TitleFullPage
covTrue, //LineBeforeTableFullPage
COleVariant("Luke Skywalker"), //NameFullPage
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional // Run() takes 31 parameters!!
);
//Quit Excel and release the IDispatch pointer we used to automate
//it.
oHTML.SetSaved(TRUE);
oBook.SetSaved(TRUE);
oHTML.ReleaseDispatch();
oBook.ReleaseDispatch();
oApp.Quit();
oApp.ReleaseDispatch();
AfxMessageBox("You can see the .html file by opening it in Excel.\n"
" Its name is Book1111.html.",327744);
return;
// End sample code
Help and Support
Back to the top
