MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help defining variables within the Chdir function

Posted by Travis on July 16, 2001 7:28 AM

I need help in writing a macro to open a file based off specific cell information.

ChDir "S:\QR\PE\2001\Molding\Primary"
Workbooks.Open Filename:= _
06-jun-pchart.xls", _UpdateLinks:=0

Example: I have a spreedsheet that has Molding in cell A1, Primary in A2, 06-Jun-pchart in A3. Cells A1-A3 will change each month. I need to open the file based off the information in these cells.

VB help is not doing me alot of good!!

Posted by Damon Ostrander on July 16, 2001 8:12 AM

Hi Travis,

Just create a few variables thus:

MyPath = "S:\QR\PE\2001\" & [A1] & "\" & [A2]
MyFile = [A3]

ChDir MyPath
Workbooks.Open Filename:=MyPath & "\" & Myfile, UpdateLinks:=0

I'm assuming that the S drive is logged to \\601data\SHARED, but if not, just create another MyPath variable to contain the path to open the file from.

Happy computing.


PS. Actually, since the Chdir makes MyPath the default path, opening the file should only require:

Workbooks.Open Filename:=Myfile, UpdateLinks:=0