CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
