Automating Excel by Using Windows INI Files

This article will use the example project that was created in Automating Excel with VB 2005 Express. If you haven't already done so, download the project zip file from here!

Unzip the file and open the ExcelApplication1.vbproj. Make sure this project is correctly working as expected before continuing.

Problem Statement

Take a look at the source code for ExcelApplication1.vbproj. You will note that the Button2 click event creates a dynamic spreadsheet with hard-coded formatting and text settings. Hard-coding is a technique that generally should be avoided whenever possible.

Solution

What you'll do for this tutorial is replace the hard-coded values and instead read and write them from and to a Windows Initialization file, or INI file for short. The API functionality should be encapsulated in a reusable Visual Basic class module.

Note: Object-oriented and API programming are both beyond the scope of this lesson and, as such, the class module is available for download with this article without additional comment.

About Windows INI Files

Windows INI files are, simply put, specially formatted ASCII text files that contain program configuration information. INI files often use the reserved extension "ini", but not always. They are sub-divided into sections and each section can contain any number of KEY-VALUE pairs. In short, as long as you know the section and the key, the values can be read and/or written in any random order. This functionality can be used from Visual Basic by declaring the PrivateProfileString Windows Applied Programming Interface, or API for short. INI files were once the foundation of the Windows operating system. They were largely supplanted by the Registry in Windows 95, although it might be argued that the Registry is merely a souped-up INI file. XML is the new configuration kid on the block, especially for Internet-based applications. That said, because of their ease of use, flexibility, and simplicity, INI files continue to be widely used.

Step 1. Add C_INIMANAGER.vb to ExcelApplication1.vbproj.

Download C_INIMANAGER.zip and extract the contents to a directory on your hard drive. Open ExcelApplication1 and click Project-Add Existing Item. Locate the extracted file C_INIMANAGER.vb and add it to the project. The object should now be listed in Solution Explorer.

Step 2. Declare a class level instance of C_INIMANAGER.vb in your form.

Add the line of code shown below. I'm calling it myIni but, like any other variable, you can give it any meaningful name of choice.

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

Step 3. Add two additional buttons to the existing form.

Label one button "Save" and the other "Open".

Step 4. Add the code to the Save button.

Double-click the Save button to open the Code Editor. Add the code shown below. Note that Filename and Section are public properties of the C_INIMANAGER class and PutStr and PutInt are 2 public methods. The file below is named myExcel.ini. You can name yours anything you want as long as you are consistent when referring to it.

myIni.Filename = My.Application.Info.DirectoryPath & "\myExcel.ini"
myIni.Section  = "DEFAULT"
myIni.PutStr(objExcel.Range("A1").Value, "TITLE")
myIni.PutInt(objExcel.Range("A1").ColumnWidth, "COLUMNWIDTH")
myIni.PutInt(objExcel.Range("A1").Font.Bold, "FONTBOLD")
myIni.PutStr(objExcel.Range("A2").Value, "STREET")
myIni.PutStr(objExcel.Range("A3").Value, "STATE")

Step 5. Testing the Save functionality.

Once you've added the above code, press F5 to run your project. It's important to remember, at this point, that objExcel is empty. The program code for for the two pre-existing buttons shows you how to open an existing spreadsheet or dynamically create a new one. You will use the dynamic sheet here, so click Button2. Minimize the instance of Excel (don't close it) and click the Save button on your form. Use Windows Explorer to locate the project directory; you should find a new file named myExcel.ini in the location as specified by the Filename property. If you open it with Notepad, you should see something similar to that shown below.

[DEFAULT]
TITLE=A1 Construction, Inc.
COLUMNWIDTH=20
FONTBOLD=-1
STREET=So. Main St.
STATE=Hartford, CT

Step 6. Add the code to the Open button.

The last step is to re-create the spreadsheet based on the values found in the INI file. Double-click the Open button and add the following code. Once again, Filename and Section are the public properties; GetInt and GetStr are public methods.

myIni.Filename = My.Application.Info.DirectoryPath & "\myExcel.ini"
myIni.Section  = "DEFAULT"

With objExcel
   .Visible = True
   .Workbooks.Add()
   .Range("A1").Value       = myIni.GetStr("TITLE")
   .Range("A1").ColumnWidth = myIni.GetInt("COLUMNWIDTH")
   .Range("A1").Font.Bold   = myIni.GetInt("FONTBOLD")
   .Range("A2").Value       = myIni.GetStr("STREET")
   .Range("A3").Value       = myIni.GetStr("STATE")
End With

Step 7. Testing the Open button.

Press F5 to start the application. It's no longer necessary to open or create an existing sheet. Click the Open button. Your resultant spreadsheet should be identical to that created by the code under Button2. Use Notepad to open the INI file again. Change the CT to MA or anything else. Close and save the change. Click the Open button again and you should now see that change on the spreadsheet.

Final Thoughts

If you're not sure how the code in the class module works, don't worry about it. As long as you know how to set the properties and call the methods, that's all you need. If you set the two properties individually, as I did in Steps 4 and 6 above, you don't need to include them in the four method arguments. If your INI file contains multiple sections, make sure you change that property value or you might not get your expected values.

Although this example is small, you can use these same techniques to build some extremely complicated spreadsheets that include formulas, charts, and even pivot tables.



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

  • Microsoft Interop

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

    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
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 …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds