Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read from INI File (VBA/Excel/2003)

    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?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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?
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from INI File (VBA/Excel/2003)

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

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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.

  8. #8
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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:
    <pre>[MyNames]
    Name1=Jane Smith
    Name2=John Doe
    Name3=Frank James
    Name4=Wyatt Earp</pre>


    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read from INI File (VBA/Excel/2003)

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

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Read from INI File (VBA/Excel/2003)

    <P ID="edit" class=small>(Edited by wdwells on 02-Jul-08 16:20. )</P>Hi Kim
    This post was anticipated by HansV's <!post=post ,719447>post <!/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

    <center>?System.PrivateProfileString("D:Trashtest. ini","MyNames","Name1")</center>

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

    Any thoughts on what I have overlooked or misunderstood?

    T.I.A.
    Regards
    Don

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Read from INI File (VBA/Excel/2003)

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

  13. #13
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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

  14. #14
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Read from INI File (VBA/Excel/2003)

    Hans,

    Bummer. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    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

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from INI File (VBA/Excel/2003)

    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.
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •