Saving Excel Add-In settings
Reading and writing ini files with plain file I/O can cause problems to do
with the Windows cache.
You could use code as below. Put it in a normal module and read and write to
the ini file with the
2 functions. Alter it as required.
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
Function ReadINIValue(strIniPath As String, _
vSection As Variant, _
vKey As Variant, _
Optional vDefault As Variant = "<no value", _
Optional vIfError As Variant = "<error reading ini")
As String
On Error GoTo ERROROUT
'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
'although the first 3 args here are String passing Variants is fine and
'saves passing ByVal or doing a CStr if the providing procedure passes
'args that are not strings
'----------------------------------------------------------------------
Length = GetPrivateProfileString(vSection, _
vKey, _
vDefault, _
buf, _
Len(buf), _
strIniPath)
ReadINIValue = Left$(buf, Length)
Exit Function
ERROROUT:
ReadINIValue = vIfError
End Function
Function WriteINIValue(strIniPath As String, _
vSection As Variant, _
vKey As Variant, _
vValue As Variant) As Boolean
On Error GoTo ERROROUT
'will return True if successful, otherwise False
'-----------------------------------------------
If bFileExists(strIniPath) = False Then
WriteINIValue = False
Exit Function
End If
'to avoid deleting a key !!
'--------------------------
If vValue = vbNullString Then
vValue = ""
End If
'although the first 3 args here are String passing Variants is fine and
'saves passing ByVal or doing a CStr if the providing procedure passes
'args that are not strings
'----------------------------------------------------------------------
WritePrivateProfileString vSection, _
vKey, _
vValue, _
strIniPath
WriteINIValue = True
Exit Function
ERROROUT:
End Function
Function bFileExists(ByVal strFile As String) As Boolean
Dim lAttr As Long
On Error Resume Next
lAttr = GetAttr(strFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
End Function
RBS
"rael_lucid" wrote in message
...
Thanks RBS.
Please can you elaborate what you mean by "...Make sure to use the API to
read and write to .ini files..."
Rael
"RB Smissaert" wrote:
I would always go with an .ini file, but that is a matter of taste.
Always found it 100% reliable, easy and all the possible options you
would
want. Make sure to use the API to read and write to .ini files.
RBS
"rael_lucid" wrote in message
...
Hi,
I am writing an Excel 2007 addin that, when run, transfers the data in
the
active sheet into a third-party database program (MYOB Accounting).
In order to run the transfer, the user has to select : the MYOB program
executable, the MYOB datafile and then enter their username and
password.
I want the user to have to select the datafile, username and password
each
time (as they may have multiple datafiles), but the MYOB program
executable
is the same each time. Rather than hardcoding the location (as it may
differ
from PC to PC), I want to store the value somewhere.
The two logical places which come to mind a
1. In the registry
2. In a "config.ini" file in a specific location
Which would you recommend? Is there a third/better option?
Note: this value does not have to be hidden/encrypted or anything like
that.
Thanks in anticipation.
Rael
|