I have some script that I have to use on multiple projects but the excel file that it opens depends on which project I am in. I do not want to have to go through the code and change the file that is opened each time. I would prefer to have a Private Function or something where I could define IF Project X then some VARIABLEA is equal to this excel file Else if Project Y then VARIABLEA is equal to this excel file and so on, that way I can keep adding projects just to the function and it can be referenced throughout the code where it asks for the workbook to be opened.
For Example I have the following code throughout my code
Set objExcel = CreateObject("excel.Application") 'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' substitute your file here objExcel.Visible = False
Instead of having to substiture my file for each different project, since the directories differ. I would prefer to have it where the objExcel.Workbooks.Open ("VARIABLEA")
and that VARIABLEA is defined in some sort of Private Function. I can set (1, 1) to equal the name of the project. Then maybe it can search (1,1) and then if (1,1) = Training then VariableA = \\TrainingProject2009\Documents\notes.xls
Else if (1,1) = ProjectX then VARIABLEA = \\ProjectX\Documents\notes.xls
I am not totally sure. Any help with this would be appreciated. I am assuming the private function would need to go near the beginning of the code before it reaches the "VARIABLEA" but I am not sure.