Force use of the Default Directory

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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)
 
Back
Top