HOWTO: Automating Excel From Client-Side VBScript

• Microsoft Visual InterDev, version 6.0
• Microsoft Excel 97 for Windows
• Microsoft Internet Information Server version 4.0
• Microsoft Office 2000 Developer

SUMMARY

This article walks you through building a Web page that launches and automates Microsoft Excel 97 when a user clicks a button. To use this article you need to have the following:

• Installed and configured:


Microsoft Internet Information Server (IIS) 4.0 on a Windows NT 4.0 Server.

- or -

Microsoft Personal Web Server (PWS) on a Windows NT Workstation or Windows 9x computer.


• Microsoft Visual InterDev version 6.0 installed.



• Microsoft FrontPage Server Extensions Version 3.0 or later. If you need to install these you will find them on Disk 13 of the January 1998 Microsoft MSDN Development Platform(U.S.) - Windows NT Option Pack 4.0 (or later.)

MORE INFORMATION

Use the following steps:

1. Start Microsoft Visual InterDev version 6.0.



2. In the New Project dialog box, type "MyVIProj" in the Name edit box, then double-click the New Web Project icon.



3. Type or select your Web server name in the Web Project Wizard dialog box that opens. Leave the working mode as Master, click Next, then click Finish in the following dialog box.



4. After Visual InterDev finishes creating your project, from the Project menu, choose Add Web Item\HTML Page...



5. In the Add Item dialog box, type MyPage as the name of your page, and then click Open.



6. The HTML page you added opens in Design view by default. Click inside the page while in Design view and type in the following text, as if you were typing it into a Microsoft Word document:


Welcome to my Web page! This page demonstrates the use of client-side script by automating Microsoft Excel 97. Click the button below to start the demonstration...


7. Click the Source tab at the bottom of your HTML page.



8. Add the following HTML just before the </BODY> tag:


<INPUT id=button1 name=button1 type=button value=Button<<P> <SCRIPT LANGUAGE="VBScript">

    sub button1_onclick()
       ' Launch Excel
       dim app
       set app = createobject("Excel.Application")
 
       ' Make it visible
       app.Visible = true
 
       ' Add a new workbook
       dim wb
       set wb = app.workbooks.add
 
       ' Fill array of values first...
       dim arr(19,9) ' Note: VBScript is zero-based
       for i = 1 to 20
          for j = 1 to 10
             arr(i-1,j-1) = i*j
          next
       next
 
       ' Declare a range object to hold our data
       dim rng
       set rng = wb.Activesheet.Range("A1").Resize(20,10)
 
       ' Now assign them all in one shot...
       rng.value = arr
 
       ' Add a new chart based on the data
       wb.Charts.Add
       wb.ActiveChart.ChartType = 70 'xl3dPieExploded
       wb.ActiveChart.SetSourceData rng, 2 ' xlColumns
       wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
 
       ' Rotate it around...
       for i = 1 to 360 step 30
          wb.activechart.rotation = i
       next
    end sub
    </SCRIPT> 

9. From the File menu, choose Save All (or select CTRL+SHIFT+S).



10. Open Microsoft Internet Explorer (either locally on your server or remotely), and navigate to your page. For example, in http://<myservername>/MyVIProj/MyPage.htm substitute your server's name for <myservername>.


Assuming your Web server is configured properly, you should see your Web page. Now click the button to execute the client-side script. You should see Excel launch, a new workbook get created, data filled into Sheet1, and a chart get created and rotated. You may receive the following error after clicking the button on the page:

ActiveX component can't create object 'createobject'.

If you get the error, you need to change the security settings on your client-side browser. The instructions for making the change are in the article listed in the REFERENCES section.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Joe Crump, Microsoft Corporation

REFERENCES

For additional information,please click the article number below to view the article in the Microsoft Knowledge Base:

Q195826 PRB: CreateObject Fails from Client-Side Scripts

Additional query words:

Keywords : kbASP kbExcel kbIE kbVisID kbOffice2000 kbDSupport
Version : WINDOWS:6.0,97; winnt:4.0; :
Platform : WINDOWS winnt
Issue type : kbhowto