Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
Hi All,
i have an excel-project with a separate xml file where config information is stored. this xml is reference through a public constant with an absolute path, like that: Public Const strXMLFile As String = "D:\Documents and Settings\t119637\Desktop\xls_work\Config\config.xm l" This works as long as the xls file runs on the machine where this absolute file path is existing. If i'd like to move these files (the excel file with the vba code and the xml file) to another machine where this path is not existing, the excel sheet wont work cause the xml file is not found. Is there a ways to define a relative path to the xml file startin from the actual xls file? E.g: the excel is stored in: xls_work/myFile.xls and the xml file is stored in xls_work\Config\config.xml So i would assume that the vba code needs to determine what the path to the xls file is and add a /Config/config.xml to that path to have the xls file work with the settings in the xml file. I dont know the vba code that makes this possible help woul be greatly apreciated regards thomas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
thomas,
Public Const strXMLFile As String = "\Config\config.xml" Dim XMLPath as string XMLPath =Thisworkbook.path & strXMLFile NickHK wrote in message ups.com... Hi All, i have an excel-project with a separate xml file where config information is stored. this xml is reference through a public constant with an absolute path, like that: Public Const strXMLFile As String = "D:\Documents and Settings\t119637\Desktop\xls_work\Config\config.xm l" This works as long as the xls file runs on the machine where this absolute file path is existing. If i'd like to move these files (the excel file with the vba code and the xml file) to another machine where this path is not existing, the excel sheet wont work cause the xml file is not found. Is there a ways to define a relative path to the xml file startin from the actual xls file? E.g: the excel is stored in: xls_work/myFile.xls and the xml file is stored in xls_work\Config\config.xml So i would assume that the vba code needs to determine what the path to the xls file is and add a /Config/config.xml to that path to have the xls file work with the settings in the xml file. I dont know the vba code that makes this possible help woul be greatly apreciated regards thomas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
Hi Nick,
thanx a lot for ur reply. Will this XMLPath be public throughout the workbook or do i have to declare it so? regards thomas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
Nick,
I tried the solution u told me but unfortunately it doesnt work. ThisWorkbook.Path doesnt work outside a procedure. all these variables and constant are defined in module 1 before the Auto_Open() Sub is called. The strXMLFile is called throughout 10 different modules, so it should be public. Any idea how to do it? I tried copying the ThisWorkbook.Path inside the Auto_Open() Sub, but then the path is not made public. Any idea how i could fix this? thanx in advance!! regards thomas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
Hi Thomas, I modified from Nick's reply:
Public Const strXMLFile As String = "\Config\config.xml" Global XMLPath as String Sub AutoLoad() 'This should be the procedure you run at startup XMLPath = ThisWorkbook.Path & strXMLFile .... .... more of your own code .... End Sub Because the XMLPath variable is a global one, it will be available to all your modules in the workbook. Be aware that you cannot assign values to a variable outside of a sub/function (as you've noticed), only constants can be done this way. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative path for const in vba code
thanx a lot, that worked perfectly :-)
take care |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Path to a Folder | Excel Discussion (Misc queries) | |||
how to change absolute path to relative path | Excel Worksheet Functions | |||
Relative path | Excel Programming | |||
relative path. | Excel Programming | |||
crash changing const to public const | Excel Programming |