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

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)

"Christmas May" wrote in message
...
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,
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

"Bob Phillips" wrote in message
...
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)

"Christmas May" wrote in message
...
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

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

Christmas May



"NickHK" wrote:

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

"Bob Phillips" wrote in message
...
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)

"Christmas May" wrote in message
...
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

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

NickHK

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

Christmas May



"NickHK" wrote:

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

"Bob Phillips" wrote in message
...
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)

"Christmas May" wrote in
message
...
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

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


"Christmas May" wrote in message
...
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