Saving Excel Add-In settings

[rael_lucid]

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 are:
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

 

[RB Smissaert]

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]

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]

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]

Thanks

Is there any documentation you can point me to, regarding the problems
caused?

Rael

 

[RB Smissaert]

Just type for example this in Google:
read ini file api cache
and you will find it.

RBS