INI File

[Christmas May]

I'm attempting to store a variable for reference when different workbooks
open. I'm aware of the following options

Hidden names: I believe this to be an undocumented/unsupported feature
which may be unstable or no longer available in future releases. (Are there
"non-hidden" names?)

Excel worksheet cell (hidden worksheet, hidden workbook, personal.xls, etc.)

Add-in: Not really sure of advantages/disadvantages?

INI file:

I would actually prefer to used the INI file if not only because other
applications could access the information. I was under the impression that
an INI file was simply a text file. I opened c:\windows\win.ini and it did
appear to be a simple text file of variables and their values. However, the
following code retrieved a variable and it's value that doesn't appear to be
in the INI file? Any ideas what happens if you try to retrieve a value from
an INI file that doesn't exist. For example, if I provide a co-worker with
the .xls file but forget to provide them with the INI file what happens when
the code runs?

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

Private Sub readFromINI()
Dim sFileName As String, sHeader As String, sKey As String
Dim buf As String * 256
Dim length As Long


sFileName = "C:\WINDOWS\WIN.INI"
sHeader = "intl"
sKey = "sCountry"

length = GetPrivateProfileString( _
sHeader, sKey, "<no value>", _
buf, Len(buf), sFileName)
MsgBox Left$(buf, length)
End Sub

Thanks in advance,

Christmas May

 

[Bob Phillips]

Nothing wrong with Hidden names. Visible is a property of the name object,
so it is supported. But you still have to have a workbook to hold that name.

As for INI files you test for a successful action, both in opening the file,
and retrieving the data. You would have to do that for a hidden name, a
value on a hidden (maybe non-existent) spreadsheet.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RB Smissaert]

This is all the code you will need in dealing with .ini files:

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

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

Sub WriteMissingKey(inipath As String, _
KEY As String, _
Variable As String, _
strValue As String)

If CheckForStringInFile(Variable, inipath) = False Then
WriteIniValue inipath, _
KEY, _
Variable, _
strValue
End If

Exit Sub
ERROROUT:

MsgBox "The file " & _
vbCrLf & vbCrLf & _
inipath & _
vbCrLf & vbCrLf & _
"was not present, so could not write the value" & _
vbCrLf & vbCrLf & _
strValue, , "WriteMissingKey"

End Sub

Function ReadINIValue(ByVal strINIPath As String, _
ByVal strHeader As String, _
ByVal strKey As String) As String

'will return <no value> if the header or the key is not there
'will return <no file> if the .ini file is not there
'------------------------------------------------------------
Dim buf As String * 256
Dim Length As Long

If bFileExists(strINIPath) = False Then
ReadINIValue = "<no file>"
Exit Function
End If

Length = GetPrivateProfileString(strHeader, _
strKey, _
"<no value>", _
buf, _
Len(buf), _
strINIPath)

ReadINIValue = Left$(buf, Length)

End Function

Sub DeleteIniKey(ByVal strINIPath As String, _
ByVal strSection As String, _
ByVal strKey As String)

On Error Resume Next
WritePrivateProfileString strSection, _
strKey, _
vbNullString, _
strINIPath
On Error GoTo 0

End Sub

Function WriteIniValue(ByVal inipath As String, _
ByVal PutKey As String, _
ByVal PutVariable As String, _
ByVal PutValue As String) As Boolean

'will return True if successful, otherwise False
'-----------------------------------------------

If bFileExists(inipath) = False Then
WriteIniValue = False
Exit Function
End If

WritePrivateProfileString PutKey, _
PutVariable, _
PutValue, _
inipath

WriteIniValue = True

End Function

Function CheckForStringInFile(strString As String, _
strFile As String) As Boolean

'result will be True if string is in the file
'and False if the string is missing
'because of vbBinaryCompare this will be case sensitive
'------------------------------------------------------

Dim hFile As Long
Dim buff As String

On Error GoTo ERROROUT

'obtain file handle, open file
'and load into a string buffer
hFile = FreeFile

Open strFile For Input As #hFile

buff = Input$(LOF(hFile), hFile)

Close #hFile

If InStr(1, buff, strString, vbBinaryCompare) = 0 Then
CheckForStringInFile = False
Else
CheckForStringInFile = True
End If

Exit Function
ERROROUT:

MsgBox "The file " & _
vbCrLf & vbCrLf & _
strFile & _
vbCrLf & vbCrLf & _
"was not present, so could't check for the string" & _
vbCrLf & vbCrLf & _
strString, , "CheckForStringInFile"
On Error GoTo 0

End Function

Public Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS

 

[NickHK]

Bob,
I think the OP's referring to Excel's hidden NameSpace, rather than hidden
name.
As I see it you can use that, but you need understand what you are doing and
why. If there's a suitable, more standard way, use it.

NickHK

 

[Christmas May]

You are correct NichHK. I believe Pearson's site has a "white
paper"/"technical brief" on this very topic.

Christmas May

 

[NickHK]

There's various references around, but personally I have never felt the need
to use it.

NickHK