How to automate Excel from an HTML Web page by using JScript

Article translations Article translations
Article ID: 234774 - View products that this article applies to.
This article was previously published under Q234774
Expand all | Collapse all

On This Page

SUMMARY

This article demonstrates how to create and manipulate a Microsoft Office Excel workbook from an HTML page.

MORE INFORMATION

This article illustrates JScript code that automates Excel. JScript has no internal means of creating SAFEARRAYs. When using automation, if you call a function that requires a SAFEARRAY, you can use VBScript to create the SAFEARRAY. The code below demonstrates this combination of JScript and VBScript code.

Building the automation sample

  1. Start Notepad.
  2. Paste the following code into Notepad.
    <HTML>
    <BODY>
    Press the button to start Excel and display quarterly data.
    <SCRIPT LANGUAGE="VBScript">
    Function CreateNamesArray()
    ' Create an array to set multiple values at once.
      Dim saNames(5, 2)
      saNames(0, 0) = "John"
    
      saNames(0, 1) = "Smith"
      saNames(1, 0) = "Tom"
      saNames(1, 1) = "Brown"
      saNames(2, 0) = "Sue"
      saNames(2, 1) = "Thomas"
      saNames(3, 0) = "Jane"
      saNames(3, 1) = "Jones"
      saNames(4, 0) = "Adam"
      saNames(4, 1) = "Johnson"
      CreateNamesArray = saNames
    End Function
    </SCRIPT>
    
    <SCRIPT LANGUAGE="JScript"> 
    function AutomateExcel()
    {
    
       // Start Excel and get Application object.
          var oXL = new ActiveXObject("Excel.Application");
           
          oXL.Visible = true;
          
       // Get a new workbook.
          var oWB = oXL.Workbooks.Add();
          var oSheet = oWB.ActiveSheet;
             
       // Add table headers going cell by cell.
          oSheet.Cells(1, 1).Value = "First Name";
          oSheet.Cells(1, 2).Value = "Last Name";
          oSheet.Cells(1, 3).Value = "Full Name";
          oSheet.Cells(1, 4).Value = "Salary";
             
       // Format A1:D1 as bold, vertical alignment = center.
          oSheet.Range("A1", "D1").Font.Bold = true;
          oSheet.Range("A1", "D1").VerticalAlignment =  -4108; //xlVAlignCenter
             
       // Create an array to set multiple values at once.
    
       // Fill A2:B6 with an array of values (from VBScript).
          oSheet.Range("A2", "B6").Value = CreateNamesArray();
         
       // Fill C2:C6 with a relative formula (=A2 & " " & B2).
          var oRng = oSheet.Range("C2", "C6");
          oRng.Formula = "=A2 & \" \" & B2";
             
       // Fill D2:D6 with a formula(=RAND()*100000) and apply format.
          oRng = oSheet.Range("D2", "D6");
          oRng.Formula = "=RAND()*100000";
          oRng.NumberFormat = "$0.00";
             
       // AutoFit columns A:D.
          oRng = oSheet.Range("A1", "D1");
          oRng.EntireColumn.AutoFit();
             
       // Manipulate a variable number of columns for Quarterly Sales Data.
          DispalyQuarterlySales(oSheet);
            
       // Make sure Excel is visible and give the user control
       // of Excel's lifetime.
          oXL.Visible = true;
          oXL.UserControl = true;
    }
          
    function DispalyQuarterlySales(oWS)
    {
          var iNumQtrs, sMsg, iRet;
    
        // Number of quarters to display data for.
          iNumQtrs = 4;
                   
        // Starting at E1, fill headers for the number of columns selected.
          var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
          oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";
             
        // Change the Orientation and WrapText properties for the headers.
          oResizeRange.Orientation = 38;
          oResizeRange.WrapText = true;
             
        // Fill the interior color of the headers.
          oResizeRange.Interior.ColorIndex = 36;
             
        // Fill the columns with a formula and apply a number format.
          oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
          oResizeRange.Formula = "=RAND()*100";
          oResizeRange.NumberFormat = "$0.00";
             
        // Apply borders to the Sales data and headers.
          oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
          oResizeRange.Borders.Weight = 2;  // xlThin
             
        // Add a Totals formula for the sales data and apply a border.
          oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
          oResizeRange.Formula = "=SUM(E2:E6)";
        // 9 = xlEdgeBottom      
          oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
          oResizeRange.Borders(9).Weight = 4; //xlThick
             
        // Add a Chart for the selected data.
    
          oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
          var oChart = oWS.Parent.Charts.Add();
          oChart.ChartWizard(oResizeRange, -4100, null, 2);  // -4100 = xl3dColumn
          oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
          for (iRet = 1; iRet <= iNumQtrs; iRet++) {
             oChart.SeriesCollection(iRet).Name = "=\"Q" + iRet + "\"";
          }
          oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject
             
        // Move the chart so as not to cover your data.
          oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
          oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
    }
    </SCRIPT>
    <P><INPUT id=button1 type=button value="Start Excel" 
              onclick="AutomateExcel"></P>
    </BODY>
    </HTML>
    					
  3. Save the file to a directory of your choice with the file name Excelaut.htm.
  4. Close Notepad and start Internet Explorer.
  5. In the Address bar, type C:\path\excelaut.htm, where path is the directory you saved the file in.
  6. When Internet Explorer loads the file, you'll see one sentence with a button. When you press the button, Excel will start on the client's machine and fill with data.

REFERENCES

This article parallels other articles that describe the same process using different languages. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
219151 How to automate Microsoft Excel from Visual Basic
179706 How to use MFC to automate Excel and create and format a new workbook
219430 How to create/format an Excel workbook using Visual J++ Automation
231614 How to automate Excel to create a new workbook from Delphi
For more information about how to automate Excel from a HTML page, click the following article number to view the article in the Microsoft Knowledge Base:
198703 How to automate Excel from a client-side VBScript
For more information about Office Automation, visit the following Microsoft Office Development support Web site:
http://support.microsoft.com/ofd

Properties

Article ID: 234774 - Last Review: May 11, 2007 - Revision: 5.2
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Internet Explorer 4.0 128-Bit Edition
  • Microsoft Internet Explorer 4.01 Service Pack 2
Keywords: 
kbautomation kbhowto KB234774

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com