Excel 2007 Macro ,VBAProject can put a configuration file?

Question

Firstly, I'm a newer to Excel Macro. I have read some articles and done some work these days. But now I'm so hesitant.Can .txt or .xml file be placed into VBAProject?If yes,how place and how to get the path of file or just read the file? If no, is there a transit way to do this work? Just think Macro,not AddIn project. Thanks very much!

Answer 1

You can save a txt with ini extension and save your settings in it as key value pairs. With below code you can retrieve those settings providing the key and getting the value.

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

Public Function GetINIString(ByVal sApp As String, ByVal sKey As String, ByVal filepath As String) As String
    Dim sBuf As String * 256
    Dim lBuf As Long

    lBuf = GetPrivateProfileString(sApp, sKey, "", sBuf, Len(sBuf), filepath)
    GetINIString = Left$(sBuf, lBuf)
End Function

Sub sample()
    Dim Path As String
    Path = ThisWorkbook.Path & "\" & "Path.ini"
    Link = GetINIString("Path", "Link", Path)
End Sub

Please save the .ini file in the same folder where the workbook resides or you may change the Path variable accordingly.

You may also refer this link for more details.