Read from INI File (VBA/Excel/2003)

Topic

New Reply

Hi All – I have developed a series of complex XL workbooks that use a REG file to populate a listbox on a VBA form. The owners now require that the application use an INI file instead. Security and Standard Operating Environment being the reasons for the change.

An internet search reveals the Windows API Function GetPrivateProfileString as the best option. I have no experience with INI files and would like to know of any experience others may have had with this Function.

In testing with that Function so far I am unable to read the text to the listbox but I note that the length of the listbox is governed by the number of relevant Section and Keys that exist in the INI. I cannot fathom this out.

Any suggestions or users with experience in changing from REG to INI for Excel please?

Reply | Quote

Replies

Perhaps it’s because I am a light user of Excel, but I’m not sure what a REG file is. Presumably not a file you merge into the Windows Registry…

If it’s a data file, would they let you use a .csv or other data format rather than the possibly less appropriate .ini file format?

Reply | Quote

Thanks for your input Jefferson. A REG is indeed a file (e.g. MyFile.REG) that is merged into the Registry.

The purpose of the INI (basically a text file) is that it can have a structure of Sections, Keys and strings of information that my VBA code will call from the INI file when a user clicks on individual option buttons in the form. The data then populates text or listboxes for further choices for the user.

A CSV is basically a text file also but I have not seen them used in the same way that I believe I require. I think an INI file is the way I must pursue.

If I am successful with what I am doing presently, I will post it for others to consider because I cannot find any other references to it on the ‘net.

Reply | Quote

Because an INI file does not behave like an array or a dataset, I think you will need to “code in” certain metadata about your registry keys and values. I made up a sample based on my initial thoughts, but perhaps you can build the data into an array instead?

Reply | Quote

Thanks again Jefferson but the path I am looking at is to have a totally text file of the format of standard INIs without reference to Registry keys.

Not quite sure what is meant by building the data into an array. If it makes it clearer for you I would have an INI file with a structure that includes Sections and Keys (with Keynames) that my Excle VBA code would attempt to get information from using the GetPrivateProfileString function. Some of this data would go into a textbox and other data would populate a listbox dependent on which option button the user clicked on the form.

If I can get the function to work correctly it will perform a similar function (‘scuse the pun) to what the GetSetting function does in importing data from relevant keys in the Registry.

At the moment it is not working but I’ll keep going as I need it to work…

Reply | Quote

I use INI files to save info that is reused in user forms in Word — user preferences, a default for a listbox, that kind of thing.

The advantage of using an INI file is that I’m not messing with my client’s registry settings and if I need to tweak something (or my client needs to tweak something) it’s easier to tweak the INI file than the registry. Of course, that’s also the shortcoming — it’s easy enough for a curious user to find and fiddle with the INI file.

That aside, if my INI file has this in it:

[MyNames]
Name1=Jane Smith
Name2=John Doe
Name3=Frank James
Name4=Wyatt Earp

The Section is [MyNames] and the Key is Name1, Name2, etc. You can have many Sections within an INI file.

So, I can then populate a listbox by retrieving the names with:

ListBox1.AddItem System.PrivateProfileString(“C:MyFormInfo.ini”, “MyNames”, “Name1”)
ListBox1.AddItem System.PrivateProfileString(“C:MyFormInfo.ini”, “MyNames”, “Name2”)
ListBox1.AddItem System.PrivateProfileString(“C:MyFormInfo.ini”, “MyNames”, “Name3”)
ListBox1.AddItem System.PrivateProfileString(“C:MyFormInfo.ini”, “MyNames”, “Name4”)

Note that I use PrivateProfileString and not GetPrivateProfileString.

So this is for Word, but hopefully it gives you enough to help tweak your code?

Best,
Kim

Reply | Quote

Unfortunately, Excel doesn’t have System.PrivateProfileString, so you either have to use Automation to employ Word’s PrivateProfileString (but that generates a lot of overhead) or use the API functions.

But the general idea of your code will remain the same, so that should still be useful.

Reply | Quote

Hans,

Bummer.

I have made great use of this in Word. But you’re right, invoking Word just to populate dialog boxes in Excel is a little much.

Kim

Reply | Quote

Thanks Kim and Don and Hans

If I can’t get another way to resolve this I might have to resort to invoking Word. But as Hans said, I believe it increases the overhead and I would like to keep it down.

Battling on…

Reply | Quote

(Edited by wdwells on 02-Jul-08 16:20. )

Hi Kim
This post was anticipated by HansV’s post. Thank you Hans.

I have been using text files and the Open/Close commands to achieve this sort of functionality; and now see the advantage of an INI approach. However after creating an INI file (D:Trashtest.ini), with the same data as you posted, I entered

?System.PrivateProfileString(“D:Trashtest.ini”,”MyNames”,”Name1″)

in the Immediate window and received the compile error, “Invalid qualifier”.

Any thoughts on what I have overlooked or misunderstood?

T.I.A.

Reply | Quote

In which application are you doing this? It works fine for me from Word (where System.PrivateProfileString is defined).

Reply | Quote

You nailed it Hans. I was using Excel; Word works a treat.

Reply | Quote

Don,

Glad Hans answered your question. As I said, I’ve used this a lot in Word, both writing to and reading from the INI. When I save it in an Application Data folder, I can capture and reuse defaults for each person using the computer, and easily troubleshoot.

K

Reply | Quote

I’d suggest taking a look at vbAccelerator – Easy INI File Access and download the cIniFile Class. This (or a similar offering) will take a lot of the headaches out of the coding.

Alan

Reply | Quote

Hi Alan – I had a look at the vbAccelerator site and tried to work with the CLS file but got error message about the form class not being supported in VBE.

However if anyone can see the way to use the function GetPrivateProfileString to work where I have used GetSetting in my attached example INI and code, I would be immensely grateful.

Reply | Quote

Copy the following code into a standard module:

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

Public Function ReadINIfile( _
Filename As String, _
Section As String, _
Entry As String, _
Optional Default As String) As String
Dim lngRet As Long
Dim strBuf As String, intLen As Integer
Dim sValue As String

strBuf = String(256, 0) '256 null characters
intLen = Len(strBuf)
lngRet = GetPrivateProfileString(Section, Entry, _
Default, strBuf, intLen, Filename)
ReadINIfile = Left(strBuf, lngRet)
End Function

Instead of

Num = GetSetting("MSLab", "TemplatesArea", "Num")

you can now use

Num = ReadINIFile("C:TestMSLab.ini", "TemplatesArea", "Num")

where C:Test is the path of the .ini file. (Your code uses “Template LocationsArea” but I didn’t see such a section in the .ini file)

Reply | Quote

Hello Hans

I believe that I understand the ReadINIfile function, but the GetPrivateProfileString function which it uses, leaves me totally in the dark. Any required reading would be appreciated.

Do you happen to have a chunk of code up your sleeve which will delete a given entry from an INI file? I can see a way to do it, but it will be quite a few lines of code.

T.I.A.

Reply | Quote

GetPrivateProfileString is a Windows API function, i.e. it is one of the “native” Windows functions that Microsoft makes available to programmers.
You tell your VBA program about it by using a Declare statement; this specifies the arguments for the function.
See for example GetPrivateProfileString.

Reply | Quote

Hello Hans
In order to get the code example which you identified to run properly, it was necessary to revise the remmed line 20 to as shown for line 30. I am running Excel 2003 in Windows XP. Any thoughts?

T.I.A.

'// INI CONTROLLING PROCEDURES

'reads ini string
Public Function ReadIni(Filename As String, Section As String, Key As String) As String
Dim RetVal As String * 255, v As Long
10 v = GetPrivateProfileString(Section, Key, "", RetVal, 255, Filename)
20 ' ReadIni = Left(RetVal, v - 1)
30 ReadIni = Left(RetVal, v)
End Function


Reply | Quote

The code that I posted works OK for me. Why change it?

Reply | Quote

I guess that I asked my question poorly. I would like to understand why GetPrivateProfileSection returns nSize as 1 greater than the number of characters pasted into the buffer; while GetPrivateProfileString as used in the code you posted here to read the Entry value returns nSize as the exact number of characters pasted into the buffer.

Option Explicit

Private Declare Function GetPrivateProfileSection _
Lib "kernel32" Alias "GetPrivateProfileSectionA" ( _
ByVal lpAppName As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long

Private Sub Test4Hans()
'local variables
Dim File As String, OFLen As Double, _
Str As String

'set our varibles
File = "C:temp.txt"
OFLen = FileLen(File)

Str = Str & "Test2 section: " & vbTab _
& ReadIniSection(File, "Test2") & vbCrLf
Str = Str & "Test1 section: " & vbTab _
& ReadIniSection(File, "Test1") & vbCrLf

MsgBox Str

End Sub
Public Function ReadIniSection( _
Filename As String, _
Section As String _
) As String
Dim RetVal As String * 255, v As Long

v = GetPrivateProfileSection(Section, _
RetVal, 255, Filename)
ReadIniSection = Left(RetVal, v - 1)
End Function


Reply | Quote

This is about a different API function: GetPrivateProfileSection instead of GetPrivateProfileString. You didn’t even mention GetPrivateProfileSection in your previous question!

GetPrivateProfileSection returns a string of the following format:

"Item1=Value1 Item2=Value2 Item3=Value3 "

That is, the lines of the section of the ini file are concatenated, with a space after each of them. This is by intent. Your using v-1 instead of v simply removes the last space.

Reply | Quote

Thanks for the insight Hans
[indent]

---

That is, the lines of the section of the ini file are concatenated, with a space after each of them.

---

[/indent]
I was being confused by only the first line in a section showing up in a message box. I traced this down (thanks to your last post), to the expected space between lines being a Null character “Chr(0)” instead.

Reply | Quote

If you haven’t figured it already Don, take a look at GetPrivateProfileSection Function (Windows). In particular,

“lpReturnedString [out]
A pointer to a buffer that receives the key name and value pairs associated with the named section. The buffer is filled with one or more null-terminated strings; the last string is followed by a second null character.

The return value specifies the number of characters copied to the buffer, not including the terminating null character. “

Many (most?) of the API functions, particularly the older 16-bit ones, use C-style strings, which use the null terminator as the “end-of-string” marker.

Alan

Reply | Quote

Thank you for that Alan.

Reply | Quote

You can use WritePrivateProfileString (see below) to set the value of an entry in an INI file. If you set the value to vbNullString, the entry is deleted.

At the top of a standard module:

Private Declare Function WritePrivateProfileString Lib “kernel32” Alias _
“WritePrivateProfileStringA” (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long

Below the Declare statements:

Public Function WriteINIfile( _
Filename As String, _
Section As String, _
Entry As String, _
Value As String) As Boolean

WriteINIfile = WritePrivateProfileString(Section, Entry, _
Value, Filename)
End Function

This function returns True if it succeeded, False if it failed.

Examples of use: to set the value of Num in the TemplatesArea section to 37:

WriteINIFile “C:TestMSLab.ini”, “TemplatesArea”, “Num”, 37

To delete the Num entry:

WriteINIFile “C:TestMSLab.ini”, “TemplatesArea”, “Num”, vbNullString

Reply | Quote

Hans

Reply | Quote

OK, that particular VB example seems like it would need a little recoding to work in VBA. You might have to hunt down one of the “or similar” solutions I alluded to. Maybe you’ll have more luck with Private Profile Strings using INI-files using VBA in Microsoft Excel.

Alan

Reply | Quote

Alan, Chris, Don and Kim – the good news is that it now runs the way it should. While the efforts of everyone who offered their suggestions, recommendations were very much appreciated, it was Hans simple response that nailed it. I had looked at a similar solution very early (nearly two days of Googling, trying code and hairtearing) but I couldn’t get it to work…
|
|
|
– And then it dawned on me, the format of the INI file was critical. Single-stepping the code was coming up with no response for the bit where it had to get the string from the INI. I found out that there has to be no ‘blank’ entries in the Keys otherwise it just goes with a default value (none given in this case). I overcame it with a simple ‘Reserved’ in the blanks and behold, it worked flawlessly.

Thanks again all, and to Hans, who seems to always find an answer for me, many thanks. Very much appreciated.

As promised I will put this into a small package and post it here for others to use. That is if they haven’t put a few bits of code together and come up with their own answer.

Reply | Quote

I wrote my own INI functions years ago, and use them still.
I allow string values >256 characters, and have played around with multiple keys in a single section etc.
The get-ini function (strGPA) takes a key and a default value, returns the default value of the key was not found.
This allows an easy “reset ini” function – merely delete the INI file.

Reply | Quote

Thanks for your input Chris – However I need the INI file to be untouched as it contains data that the users need to use but not modify. I can’t have any chance of the INI being deleted therefore the WritePrivateProfileString function will not not be declared.

You might like to have a look at what I used originally and offer suggestions based on that though (ZIP file in my response to Alan)

Reply | Quote

>However I need the INI file to be untouched
That is nothing to do with using an INI file; You set network permissions (on the INI file) that allow end-users to read it but not to modify or delete it.
I manage INI files, and make extensive use of them throughout all Office applications.
Despite opinions expressed to the contrary, you are not restricted to the facilities offered by Word, Excel etc.
I wrote my INI file code to read a text file, parse it (by section & key) and manage it as a series of strings.
It is something I wrote once and can use in any Office application and, with a little modification, in any desktop application. There’s no reason why a Corel application can’t share the same code concept.

>I can’t have any chance of the INI being deleted therefore the WritePrivateProfileString function will not not be declared
I don’t make use of the WritePrivateProfileString, and whether or not I declare it makes no difference to the end user.

The end-user can modify the registry with impunity; not so a protected INI file.

I mentioned the “reset” facility as an example (only) of the superb advantages of INI files driven by custom Get/Put functions. Of course, if one of my applications doesn’t need/want to offer the user the ability to reset the INI file, then that application doesn’t make such a facility available to the end user.

Microsoft’s approach to software can be downright mean and nasty. I couldn’t see why a key value should be restricted to 256 chars, so I wrote my own INI get/put functions.

I recommend that you consider bypassing MSoft’s functions, if they don’t suit your purpose.

I just checked my code and discover that I seem to be using a compile-time switch to switch between the registry, short, and long key values. I’d forgotten I did that.

Here is a set of functions and headers, to give you some idea of the versatility
ResetEnvironment(strAp As String, strSection As String)
strBuildEnvFile(strFilename As String) As String
strCreateKeyString(strKeyName As String, strKeyValue As String) As String
strCreateSectionHeader(strSectionName As String) As String
strGetEnvVar(ByVal strFile As String, ByVal strSection As String, strKey As String, strValue As String)
strGetNamedKeyOfSection(strSectionData As String, strKeyName As String, lngKeyStart As Long, lngKeyEnd As Long) As String
strGetNextKeyFromSection(strSectionData As String, lngKeyStart As Long, lngKeyEnd As Long) As String
strGetNextSection(strFileData, lngStart As Long, lngSectionHeaderStart As Long, lngSectionHeaderEnd As Long, lngSectionEnd As Long) As String
strGetNextSectionFromFile(strFileData As String, lngSectionStart As Long, lngSectionEnd As Long) As String
strGPA(strKey As String, strDefaultValue As String) As String
strINIFileName(Optional strAp) As String
strMaintainKeyInSection(strSectionData As String, strKeyData As String, lngKeyStart As Long, lngKeyEnd As Long) As String
strMaintainSectionInFile(strFileData As String, strSectionData As String, lngSectionStart As Long, lngSectionEnd As Long) As String
strNameOfKey(strSectionData As String, lngKeyStart As Long, lngKeyEnd As Long) As String
strNameOfSection(strFileData As String, lngSectionStart As Long, lngSectionEnd As Long) As String
strPPA(strKey As String, strValue As String) As String
strProfileIn(strFile As String, strSection As String, strKey As String, strValue As String) As String
strProfileOut(strFile As String, strSection As String, strKey As String, strValue As String) As String
strPutEnvVar(ByVal strFile As String, ByVal strSection As String, strKey As String, strValue As String)
strValueOfKey(strSectionData As String, lngKeyStart As Long, lngKeyEnd As Long) As String

The attached notepad file contains the VBA code for the function “strGetEnvVar”. If you glance through it you’ll find me grabbing a complete file of data, parsing it by section, parsing it by key, and essentially treating the entire file as a single string that belongs to me, the INI routines.

Reply | Quote