Force use of the Default Directory

[Guest]

Hi All.......
I have a file I call MyMaster.xls and I want to open another file and copy a
sheet out of it into the MyMaster file. This is fine and I can do it, but
only if I declare the exact path and the filename of MyMaster to copy it to.
I would like to be able to force the macro to use the same directory that
MyMaster.xls is in, wherever that might be, and to copy the sheet into the
current " MyMaster" file, no matter how I might have renamed MyMaster....like
MyMaster2, or MyMaster3, etc.

Heres the code I'm using now, but of course it doesn't do as I want.

Sub GetQ5PlanFile()
ChDir "D:\JumpMicroCruizer122804\Special\PlanningEvent"
Workbooks.Open FileName:= _
"D:\JumpMicroCruizer122804\Special\PlanningEvent\Q5PLAN.XLS"
Sheets("Q5PLAN").Select
Sheets("Q5PLAN").Copy Before:=Workbooks("MyMaster.xls").Sheets(2)
Windows("Q5PLAN.XLS").Activate
ActiveWindow.Close
End Sub

Any help would be much appreciated,

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Workbooks.Open FileName:= _
Workbooks("MyMaster.xls").Path & "\Q5PLAN.XLS"


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob, that helps, but it still depends on the filename of the MyMaster
file being hardcoded in.....I would like to avoid that if possible.

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Well if you have an object variable pointing at the master workbook, say
oWB, you can use

Workbooks.Open FileName:= oWB.Path & "\Q5PLAN.XLS"


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)