Saving Excel Add-In settings
 

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

Saving Excel Add-In settings
 

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

Saving Excel Add-In settings
 

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

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

Saving Excel Add-In settings
 

Thanks :)

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

Rael

Saving Excel Add-In settings
 

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

RBS


"rael_lucid" wrote in message
...
Thanks :)

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

Rael