Automate Excel with VB 2005 Express

This is the first of a series of related articles that I hope to write concerning my solution for a recent programming problem. I needed a VB 2005 Express Edition program that had the ability to automate both Microsoft Office 2002/2003 Word and Excel. The program must be able to open either pre-existing files or templates and also create new ones as required. Some of the spreadsheets needed to be built and formatted using values obtained from pre-existing standard Windows INI files. A few of these also would include either charts and/or pivot tables. Many of the samples I found on the Internet didn't seem to work as advertised, were too long and complicated, or relied too much on the CreateObject method of instantiating the Office programs. CreateObject is one of those classic examples of late or dynamic binding that I personally consider an anathema. The big issue I have with this method is the fact that the compiler can't differentiate between an instance of Excel or a stack of pancakes at design time and thus no Intellisense help is available. In this first article, I'll show you how I managed to automate Excel painlessly.

Start Visual Basic 2005 Express and create a new Windows Application project.

To replace the CreateObject method with "early binding", you need to add a project reference to the Excel Object Library.

Click the Project - Add Reference menu selection. From the COM tab, locate and select the Microsoft Excel 10.0 or 11.0 Object Library. Your installed Office version (XP or 2003) will determine which one of these libraries you have installed.

Take a look at "All Files" in your Solution Explorer and note there are three new References. Two of these are common to all the Office programs and are added automatically along with the main library reference.

With Form1 in Design view, open or expand your toolbox and add two command buttons. I'll use one of those buttons to open a pre-existing file and the other to create, populate, and format a new file.

Double-click Button1 and open the code editing window. The cursor should have automatically positioned itself in the button click event. Move it to a location under the Class declaration and declare a form-level Excel object variable of the type as shown below.

Public Class Form1
Dim objExcel As New Microsoft.Office.Interop.Excel.Application

Place the cursor back into the Button1 click event. Now that you've declared your object instance, you can then go ahead and use it. I've included PCSample.xls with the download, but you can also use any file you happen to have on your hard drive. Make sure to edit the code and change the name accordingly and that a copy of the file is located in the path you are running the program from. If you're a VB6 guy like me, this big long path is the replacement for App.Path.

With objExcel
.Visible = True
.Workbooks.Open(My.Application.Info.DirectoryPath & "\PCSample.xls")
End With

That's it for Button 1. Pretty simple. You can go ahead press F5 and test your program.

Now for Button 2. I'll open a new Excel application and use VB to add some text and formatting to a few cells. Double-click Button 2 to open the editing window again. Add the following code to the click event:

With objExcel
.Visible = True
.Workbooks.Add()
.Range("A1").Value       = "A1 Construction, Inc."
.Range("A1").ColumnWidth = 20
.Range("A1").Font.Bold   = True
.Range("A2").Value       = "So. Main St."
.Range("A3").Value       = "Hartford" &", " & "CT"
End With

That's it for Button 2. Again, pretty simple. Try setting some of the other properties such as fonts, colors, and so forth. Try dynamically adding a function that totals rows and/or columns.

In my next article, I'll show you how I created an INI file class that enables this program to replace the hard-coded values dynamically. Enjoy.



About the Author

John Macomber

John is a long time VB6 user based in Connecticut and is currently employed as a software developer for DTI Publishing, Inc., a Utah based publisher of various educational programs in the Computer, Automotive, and Medical fields.

Downloads

Comments

  • Old format or invalid type library

    Posted by space2044 on 03/07/2011 09:30pm

    I have "Old format or invalid type library" error message. Please help I am using VB 2005 .

    Reply
  • automated excell

    Posted by JTH on 09/01/2010 02:41pm

    this was good. this realy was a lot simpler than a lot of them i have read ... i am adding a excel wrkbk to a personal project . and was wonder do i need to index the wrkbk so i can add things daily or will append like a stream writer. i have a few ideas but thought i would ask the GURU

    Reply
  • Microsoft Interop

    Posted by BarbaMariolino on 07/14/2009 10:42am

    Hi,
    
    using Excel Interop is not best way to work with Excel in .NET. You can try using GemBox .NET Excel component which is free for commercial use (limit is 150 rows).
    
    Here is a list of reasons why GemBox.Spreadsheet is better then Excel Interop and Excel Automation

    Reply
  • Using Access with this

    Posted by Natrone on 01/07/2008 10:15am

    Hey, thank you for writing this article, It has been great help. I was wondering if it is possible to pass database info through ".Range("G9").Value". Ex: if I had a access database linked to my project and I wanted to update the info in a exel doc by using the databse.

    Reply
  • Compile error: Type 'Microsoft.Office.Interop.Excel.Application' is not defined

    Posted by argyle on 11/11/2006 10:23pm

    Hi John, 
    
    I got compile error as follows, after following your article to create a form. Could you help? tks. 
    
    Error	1	Type 'Microsoft.Office.Interop.Excel.Application' is not defined.	C:\NET\WindowsApplication1 Excel\WindowsApplication1 Excel\Form1.vb	2	25	WindowsApplication1 Excel

    • Alternative

      Posted by BarbaMariolino on 07/14/2009 10:38am

      Hi,
      
      using Interop is not best way to work with Excel in .NET. You can try using GemBox .NET Excel component which is free for commercial use (limit is 150 rows).
      
      Here is a list of reasons why GemBox.Spreadsheet is better then Excel Interop

      Reply
    • same error

      Posted by sdsvanc on 11/14/2007 01:48am

      Got the same error as in the first post even after following the steps above and adding the MSO.DLL from Office 10.
      Error	1	Type 'Microsoft.Office.Interop.Excel.Application' is not defined.	C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\CWS1\CWS1\Form1.vb	3	25	CWS1

      Reply
    • Save error

      Posted by crspycrtr on 07/19/2007 09:08pm

      When I click on the Save button I get the below error. I tried making the changes suggested here and it just makes it worse. I have Excel 11 and have it referenced. Following Microsofts suggestions are not any help either. COMException was unhandled Exception from HRESULT: 0x800A03EC

      Reply
    • Office Reference

      Posted by matthias1 on 12/12/2006 02:05pm

      Mattik....You can manually add the reference. In the Add Reference window click the Browse tab and locate the following file: C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL Click OK. The Ref shorld now display in the VB References forevermore. Office programs share a lot of common functionality so you really need both references.

      Reply
    • I got it!

      Posted by Mattik on 12/12/2006 12:23am

      Played around with the code a bit. I got this to work (open the specified excel file at least) by changing: "Dim objExcel As New.....", as specified in John's description to: Dim objExcel As New Excel.Application" Now I'm off to see just how much I can get done with my new Excel manager. Thanks John for getting me pointed in the right direction!

      Reply
    • Same compile error

      Posted by Mattik on 12/11/2006 11:47pm

      I receive the same compile error as argyle. However, I do have Excel 10.0 referenced. My version is 1.4.0.0 (is this right?). Also, I do not have the "office" reference shown in your references list. Does that matter?

      Reply
    • COM Error

      Posted by matthias1 on 11/12/2006 07:22am

      Hi Argyle...The only way I was able to reproduce your error was by removing the project COM reference to the Excel Object Library. Make sure you've added the reference to the correct object. Click Project-> Add Reference and on the COM tab locate and check the box next to the Microsoft Excel 10.0 (Office XP) or 11.0 (Office 2003)

      Reply
    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date