Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Excel Add-In settings
Thanks :)
Is there any documentation you can point me to, regarding the problems caused? Rael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Sort settings | Excel Discussion (Misc queries) | |||
saving printing settings in excel 2007 | Excel Discussion (Misc queries) | |||
Saving settings | Excel Discussion (Misc queries) | |||
HELP! Settings are not saving | Excel Discussion (Misc queries) | |||
Saving Print Settings? | Setting up and Configuration of Excel |