Force use of the Default Directory
 

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\PlanningEve nt"
Workbooks.Open FileName:= _
"D:\JumpMicroCruizer122804\Special\PlanningEvent\Q 5PLAN.XLS"
Sheets("Q5PLAN").Select
Sheets("Q5PLAN").Copy Befo=Workbooks("MyMaster.xls").Sheets(2)
Windows("Q5PLAN.XLS").Activate
ActiveWindow.Close
End Sub

Any help would be much appreciated,

Vaya con Dios,
Chuck, CABGx3

Force use of the Default Directory
 

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)



"CLR" wrote in message
...
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\PlanningEve nt"
Workbooks.Open FileName:= _
"D:\JumpMicroCruizer122804\Special\PlanningEvent\Q 5PLAN.XLS"
Sheets("Q5PLAN").Select
Sheets("Q5PLAN").Copy Befo=Workbooks("MyMaster.xls").Sheets(2)
Windows("Q5PLAN.XLS").Activate
ActiveWindow.Close
End Sub

Any help would be much appreciated,

Vaya con Dios,
Chuck, CABGx3

Force use of the Default Directory
 

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" wrote:

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)



"CLR" wrote in message
...
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\PlanningEve nt"
Workbooks.Open FileName:= _
"D:\JumpMicroCruizer122804\Special\PlanningEvent\Q 5PLAN.XLS"
Sheets("Q5PLAN").Select
Sheets("Q5PLAN").Copy Befo=Workbooks("MyMaster.xls").Sheets(2)
Windows("Q5PLAN.XLS").Activate
ActiveWindow.Close
End Sub

Any help would be much appreciated,

Vaya con Dios,
Chuck, CABGx3

Force use of the Default Directory
 

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)



"CLR" wrote in message
...
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" wrote:

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)



"CLR" wrote in message
...
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\PlanningEve nt"
Workbooks.Open FileName:= _
"D:\JumpMicroCruizer122804\Special\PlanningEvent\Q 5PLAN.XLS"
Sheets("Q5PLAN").Select
Sheets("Q5PLAN").Copy Befo=Workbooks("MyMaster.xls").Sheets(2)
Windows("Q5PLAN.XLS").Activate
ActiveWindow.Close
End Sub

Any help would be much appreciated,

Vaya con Dios,
Chuck, CABGx3