Macro reads settings from txt, ini file

[Lonely]

Hi All,
I have a Excel Macro which has some a form with some checkboxes and Option buttons.

What i am planning to do is ... design a standalone VB application where I define these

checkboxes, write these values to a .ini or .txt file and then make the excel macro read

these values from this file.

Is it possible to do this.... can someone help me with the logic.. a sample code would be

greatly appreciated.
thanks in advance,
Lonely

[WorkHorse]

Here's an exaple for using an ini file.

Option Explicit
 
Const cstrPathIni = "C:\Junk\MyExcelSettings.ini"
 
Private Declare Function WritePrivateProfileString _
    Lib "kernel32" Alias "WritePrivateProfileStringA" _
   (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, _
    ByVal lpString As Any, _
    ByVal lpFileName As String) As Long
 
Private Declare Function GetPrivateProfileString _
    Lib "kernel32" Alias "GetPrivateProfileStringA" _
   (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, _
    ByVal lpDefault As String, _
    ByVal lpReturnedString As String, _
    ByVal nSize As Long, _
    ByVal lpFileName As String) As Long
 
'**************************************************************************
'Procedure: ProfileSettingSave
'Purpose:   Set user profile setting in initialization file.
'Inputs:    v_strKey:       Key for value in ini file.
'           v_strSetting:   Value to assign to key.
'Requires:  WritePrivateProfileString |   API
'Notes:     Name for section is "ExcelData".
'           Path for ini file is cstrPathIni.
'**************************************************************************
Public Sub ProfileSettingSave(ByVal v_strKey As String, ByVal v_strSetting As String)
 
    WritePrivateProfileString "ExcelData", v_strKey, v_strSetting, cstrPathIni
 
End Sub
 
'**************************************************************************
'Procedure: ProfileSettingGet
'Purpose:   Get user profile setting from initialization file.
'Inputs:    v_strKey:       Key for value in ini file.
'           v_strDefault:   Default value for setting (optional).
'Returns:   Setting if found, Default if not found.
'Requires:  GetPrivateProfileString |   API
'Notes:     Name for section is "ExcelData".
'           Path for ini file is cstrPathIni.
'**************************************************************************
Public Function ProfileSettingGet(ByVal v_strKey As String, _
                         Optional ByVal v_strDefault As String = vbNullString) _
                                  As String
 
    Dim strSetting  As String * 255  ' String buffer to accept setting value.
    ProfileSettingGet = Left$(strSetting, _
        GetPrivateProfileString("ExcelData", v_strKey, _
        v_strDefault, strSetting, 255, cstrPathIni))
 
End Function
 
Sub InYourVBApp()
 
    ProfileSettingSave "Check1Name", "First Check Box"
    ProfileSettingSave "Check1Value", 1
 
End Sub
 
Sub InExcel()
 
    Sheets(1).CheckBox1.Caption = ProfileSettingGet("Check1Name", "CheckBox1")
    Sheets(1).CheckBox1.Value = ProfileSettingGet("Check1Value", 0)
 
End Sub

[Lonely]

Hi WorkHorse,
Thank you so much for the help that you have given me.... I tried using the code for the purpose... and was successful in saving the settings in a ini file. But when i try to load the settings in the Excel macro, it gives mean error.

Please can you help me in troubleshooting the code. Have attached the whole source code here as a VB Project.
Thanks again,
Lonely

[WorkHorse]

You just need the ProfileSettingSave & WritePrivateProfileString functions in VB to save the dat. Then in the Excel macro you put the ProfileSettingGet & GetPrivateProfileString to read the data.

In VB app to write to file:

Option Explicit
 
Const cstrPathIni = "C:\Junk\MyExcelSettings.ini"
 
Private Declare Function WritePrivateProfileString _
    Lib "kernel32" Alias "WritePrivateProfileStringA" _
   (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, _
    ByVal lpString As Any, _
    ByVal lpFileName As String) As Long
 
'**************************************************************************
'Procedure: ProfileSettingSave
'Purpose:   Set user profile setting in initialization file.
'Inputs:    v_strKey:       Key for value in ini file.
'           v_strSetting:   Value to assign to key.
'Requires:  WritePrivateProfileString |   API
'Notes:     Name for section is "ExcelData".
'           Path for ini file is cstrPathIni.
'**************************************************************************
Public Sub ProfileSettingSave(ByVal v_strKey As String, ByVal v_strSetting As String)
 
    WritePrivateProfileString "ExcelData", v_strKey, v_strSetting, cstrPathIni
 
End Sub
 
Sub InYourVBApp()
 
    ProfileSettingSave "Check1Name", "First Check Box"
    ProfileSettingSave "Check1Value", 1
 
End Sub

In Excel VBA module:

Const cstrPathIni = "C:\Junk\MyExcelSettings.ini"
 
Private Declare Function GetPrivateProfileString _
    Lib "kernel32" Alias "GetPrivateProfileStringA" _
   (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, _
    ByVal lpDefault As String, _
    ByVal lpReturnedString As String, _
    ByVal nSize As Long, _
    ByVal lpFileName As String) As Long
 
'**************************************************************************
'Procedure: ProfileSettingGet
'Purpose:   Get user profile setting from initialization file.
'Inputs:    v_strKey:       Key for value in ini file.
'           v_strDefault:   Default value for setting (optional).
'Returns:   Setting if found, Default if not found.
'Requires:  GetPrivateProfileString |   API
'Notes:     Name for section is "ExcelData".
'           Path for ini file is cstrPathIni.
'**************************************************************************
Public Function ProfileSettingGet(ByVal v_strKey As String, _
                         Optional ByVal v_strDefault As String = vbNullString) _
                                  As String
 
    Dim strSetting  As String * 255  ' String buffer to accept setting value.
    ProfileSettingGet = Left$(strSetting, _
        GetPrivateProfileString("ExcelData", v_strKey, _
        v_strDefault, strSetting, 255, cstrPathIni))
 
End Function
 
Sub InExcel()
 
    Sheets(1).CheckBox1.Caption = ProfileSettingGet("Check1Name", "CheckBox1")
    Sheets(1).CheckBox1.Value = ProfileSettingGet("Check1Value", 0)
 
End Sub

[Lonely]

Hi Workhorse,
Sorry for the delay in replying......... Thanks a million for answering my query... It worked wonderfully....
Lonely