» Private Profile Strings using Words System.PrivateProfileString using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions If you don't want to use API-functions you can use Words object library to read and write Private Profile Strings.

Words System.PrivateProfileString can read from and write to both INI-files and the Registry.

In other applications than Word you have to add a reference to Words object library.

You can add the reference by opening the Visual Basic Editor (VBE) and activate your VB Project. Then you select Tools, References... and check the option Microsoft Word x.x Object Library.

Write information to INI-files

With the macro below you can save information in a text file:

Function SetIniSetting(FileName As String, Section As String, _
    Key As String, KeyValue) As Boolean
Dim wd As Word.Application
    SetIniSetting = False
    Set wd = New Word.Application ' create the Word application object
    On Error Resume Next
    wd.System.PrivateProfileString(FileName, Section, Key) = CStr(KeyValue)
    On Error GoTo 0
    Set wd = Nothing ' destroy the Word application object
    SetIniSetting = True
End Function

Use the macro like this to save the value 100 in the file C:\FolderName\FileName.ini in the section
MySectionName for the key TestValue:
MyBooleanVar = SetIniSetting("C:\FolderName\FileName.ini", "MySectionName", "TestValue", 100)
The text file will look like this:
[MySectionName]
TestValue=100


Read information from INI-files
With the macro below you can read information from a text file:

Function GetIniSetting(FileName As String, Section As String, _
    Key As String) As String
Dim wd As Word.Application
    GetIniSetting = ""
    Set wd = New Word.Application ' create the Word application object
    On Error Resume Next
    GetIniSetting = wd.System.PrivateProfileString(FileName, Section, Key)
    On Error GoTo 0
    Set wd = Nothing ' destroy the Word application object
End Function

Use the macro like this to return the value for the key TestValue in the section MySectionName
from the file C:\FolderName\FileName.ini:

MyStringVar = GetIniSetting("C:\FolderName\FileName.ini", _
    "MySectionName", "TestValue")

Write information to the Registry

With the macro below you can save information in the Registry:

Function SetRegistrySetting(Section As String, _
    Key As String, KeyValue) As Boolean
Dim wd As Word.Application
    SetRegistrySetting = False
    Set wd = New Word.Application ' create the Word application object
    On Error Resume Next
    wd.System.PrivateProfileString("", Section, Key) = CStr(KeyValue)
    On Error GoTo 0
    Set wd = Nothing ' destroy the Word application object
    SetRegistrySetting = True
End Function

Use the macro like this to save a new value in HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft
Excel for the key DefaultPath:

MyStringVar = "HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel"
MyBooleanVar = SetRegistrySetting(MyStringVar, _
    "DefaultPath", "C:\FolderName")

Read information from the Registry With the macro below you can read information from the Registry:

Function GetRegistrySetting(Section As String, Key As String) As String
Dim wd As Word.Application
    GetRegistrySetting = ""
    Set wd = New Word.Application ' create the Word application object
    On Error Resume Next
    GetRegistrySetting = wd.System.PrivateProfileString("", Section, Key)
    On Error GoTo 0
    Set wd = Nothing ' destroy the Word application object
End Function

Use the macro like this to read the value from the key DefaultPath
from HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel:

MyStringVar = "HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel"
MyStringVar = SetRegistrySetting(MyStringVar, _
    "DefaultPath")