HOWTO: Automate Using VC++ to Save Excel Worksheet as HTML File

ID: Q199691


The information in this article applies to:


SUMMARY

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 Excel 2000


MORE INFORMATION

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.

Steps to Create the Project

  1. Follow steps 1 through 13 in the following Microsoft Knowledge Base article to create a sample project:


  2. Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library
  3. 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();
    
       //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 has 13 parameters
    
       //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")
      {
       break;
             }
       }
    
       //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 parameters
       );
    
       //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");
    
       //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.
       oApp.Quit();
       oApp.ReleaseDispatch();
       return;
       // End sample code 



REFERENCES

For additional information about using MFC to do Automation, please see the following article in the Microsoft Knowledge Base:

Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Chris Jensen, Microsoft Corporation.

Additional query words:


Keywords          : kbAutomation kbExcel kbMFC kbVC kbVC500 kbVC600 kbGrpDSO kbOffice2000 kbexcel2000 
Version           : WINDOWS:2000,97; winnt:5.0,6.0; :
Platform          : WINDOWS winnt 
Issue type        : kbhowto 

Last Reviewed: June 3, 1999