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

  • Manjini Graceful couplet is gotten run 15 minutes too in good luck if Wei Mian needs behead cuts Er on the west

    Posted by shwlouqvhu on 05/17/2013 06:35pm

    Manjini: Graceful couplet is gotten run 15 minutes too in good luck if [url=http://jordanscheap.page4.me/]air jordans cheap[/url] Wei Mian needs behead cuts Er on the west Beijing time on Feburary 24 before dawn, the goal of the world wave that graceful couplet depends on Raphael in guest field and veteran Ji Gesi QPR of 2 more assistant than 0 beat easily squad leaders, will precede the advantage of the 2nd graceful city is pulled arrived 15 minutes greatly. Very adverse to at present situation, la Yue advocate handsome Manjini also publishs a comment, he thinks such big integral difference is not the true portraiture of Mancunian double male actual strength, and say graceful couplet can be gotten run 15 minutes, because luck is too good,be completely. Manjini expresses: "Up to now, the lot of graceful couplet is too nice really. They have several competitions just is taken in last hour, and they originally may play away match, the Mancunian moral character that includes a bout is compared. It is normal that I do not think the cent of two teams is poor now. " on April 8, graceful couplet and the mind that graceful city will have second bout compare big fight, complement says Manjini: "If graceful couplet promotes all the way in Ou Guanzhong, so they are in of sports season two months will be immersed in struggle hard finally, two matches before heart comparing they desertion likely 5 minutes. Two matches before heart comparing they desertion likely 5 minutes.. To Manjini's comment, " daily Post " the analysis says this and on sports season Weiaila of graceful city ambassador assail Sikeersi is graceful couplet recall " lift acedialy " it is same measure, at that time graceful couplet advocate handsome Ferguson is indignant and clinking, use up 8 minutes of advantages in the funeral inside 6 matches eventually, desertion because of net inferior position success a ball finally champion. Now, manjinixi hopes with same measure exasperate the rank of nobility as form of a address for an official or rich man, let graceful couplet fall into psywar trap again. If graceful city wants to realize Wei Mian, besides send a hope to err at graceful couplet besides, oneself also should hit good game to just go. Beijing time on Feburary 24 21:30, graceful city will be in advocate meet strong opponent head-on Qieerxi, manjini admits before contest, if La Yueliang cannot blast behead La Jun, so gain the championship will not make fun of thoroughly. Manjini says: "We must conquer Qieerxi, will lose Wei Mian's hope thoroughly otherwise. We can expect graceful couplet is in only heart comparing is prewar lose cent, only such we just gain the championship likely. We are not clear can take league matches the first, the 2nd still be the 3rd, we still have 12 competitions, we need to go all lengths. If you ask whether I am satisfactory the 2nd, then I tell you clearly, I am dissatisfactory, I will be very disappointed. But we still have 12 competitions, we cannot think league matches has lost be concerned about. We cannot think league matches has lost be concerned about.. Yesterday, manjini explodes greatly when the problem that is asked about to finish class wide mouth, and he after coming down calmly also faces this one problem again. "Since graceful couplet carry off last after Yingchao champion, in all 7 pot can be striven for, and graceful city won 3 among them, " Manjini expresses, "If I go 3 years two empty-handed, then I may say not to want to stay, and also do not have a face to stay really. But two years we gained a success in the past, this is very important to the club. We return this sports season to take 2 champion likely, yingchao and sufficient total cup. " (Sohu sports conserve is wrapped) More sports news

    Reply
  • Maid Lingerie

    Posted by Fishnetsn1037 on 03/29/2013 09:35am

    http://babydollnightgowns.webs.com - Sexy BabydollsBabydolls are even available that more closely resemble the older bed jackets, featuring one or two front closures Let the environment be tasteful, yet celebratory http://sexycostumesa.webs.com - Sexy Nurse LingerieThe smell of a jack-o-lantern burning, the sound of a doorbell ringing, the sight of an overflowing bowl of candy--all indicators that the fabulous children's holiday Halloween has arrived http://lingeriemall.webs.com - lace babydoll lingerieSweep in it, dust in it, read in it Often, wholesalers, distributors or even manufacturers offer discount pricing for quantity purchases http://sexycostumesus.webs.com - womens sexy clothesBaby doll lingerie comes in a variety of fabrics Amongst its product inclusions are summerwear, clubwear, Halloween lane, fetish wear, men http://lingeriemall.webs.com - lace lingerieRed and black are the most popular colors for lingerie It seems that men even back then liked to see their women's breasts pushed up and squeezed together! A bustier is basically an underwire bra that extends down to the waistline

    Reply
  • Interesting

    Posted by snareenactina on 11/13/2012 02:11am

    This is a well presented and clearly navigable site for finding share and commodity prices and tailoring the data. amex G. Recent Revival jaafarsenior webconfs growwith percentchina heat

    Reply
  • Cheap UGG Boots On Sale 5815

    Posted by lissibootslaopo on 11/09/2012 06:13pm

    The normally encountered disturb plate, Fried Crickets are purchased almost everywhere with the alleys with Thailand. They could be especially had his food toast, sautéed, boiled or maybe roasting. These [url=http://www.bootslaopo.net/][b]Cheap Ugg Boots[/b][/url] kinds of crickets, the moment deep-fried, preference similar to chicken. Just as along with prawns, take out the cricket’s head off well before putting these individuals into your mouth. 6. Deep-fried Silkworm Deep-fried not to mention dished up by using bits of pandan actually leaves, these types of deep-fried silkworms truly have the most nice scent! Additionally, they never sample for that reason terrible simply because give food exclusively at Mulberry makes so within that feeling, all these insects usually are reasonably nice and clean on the inside. Plan your getaway towards Thailand [url=http://www.bootslaopo.net/cheap-uggs-bailey-button-boots-c-35.html][b]cheap uggs bailey button boots[/b][/url]with Krabifinder presently! Krabifinder capabilities many parts along the lines of Krabi, Phi Phi, Phuket, Trang, Surat Thani, Lanta and also Pang Nga (Southeast Thailand). Such handful of destinations really are where you can the above mentined out of the ordinary frustrate meals and may definitely be obtained in industries not to mention roads stalls just about everywhere. Recently it will be freezing out for that [url=http://www.bootslaopo.net/][b]Ugg Boots Outlet[/b][/url]reason intense plus the driveways are typically by using bright compacted snow. The yard with perfect can be quite gorgeous, you’ll find it convey a number of cons to life. Including, this procured 2 bottle and also multiply time period than normal driving if you happen to play outside. The particular move is definitely stimulated, and the freeway is definitely slippery together with wet. At the moment I cannot compromise my very own Ugg boot seeing that my tip toes ended up fussed over with the ones warm and comfortable ” booties “. Although now there shows up an individual problem Ugg boots may not be waterproofed. The head regarding our hunter wellingtons are actually rainy together with not clean plenty of, then i really need to tidy individuals. I have done a deep study web described those useful tips, which unfortunately I wish to give to every body. 1. Certainly not rarely waterless Ugg boots about your heater tank when they’re damp. Use blow dryers or you can dried them in a in the shade spot from your sun and rain. Take into account indirectly on a sunny day. A year ago, I have a Ugg Boots very near the unit, they are simply absolutely messed [url=http://www.bootslaopo.net/][b]Ugg Boots Clearance[/b][/url]up. I possibly could quite possibly reek people were scammed. 2. Wash it whenever also you will find there’s just a very small dirt because of the more all of the dye leaves behind on it your difficult to freshen up the idea. 3. Sparkling Uggs together with suede clean. If you want to not necessarily perform problems for Ugg boot, feel free to use suede painting brush as it is often a whole lot more more pliable as compared with various watercolor brushes. However , be sure you airbrush one way mainly. In the event the discoloration is definitely not really taken out, afterward visit one other move. Solely include a single serving from apple cider vinegar having a cup involved with nippy distilled water.Then you’ve got acquired the new Ugg boot solution. Begin using a brand new sponge to clean ugg boots utilizing this cleaning solution.

    Reply
  • 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

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • With the average hard drive now averaging one terabyte in size, the fallout from the explosion of user-created data has become an overwhelming volume of potential evidence that law-enforcement and corporate investigators spend countless hours examining. Join Us and SANS' Rob Lee for our 45-minute webinar, A Triage and Collection Strategy for Time-Sensitive Investigations, will demonstrate how to: Identify the folders and files that often contain key insights Reduce the time spent sifting through content by …

Most Popular Programming Stories

More for Developers

RSS Feeds