Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MSOffice default directory | Setting up and Configuration of Excel | |||
How to force a Default sort for a validation list | Excel Discussion (Misc queries) | |||
How do I force an Excel macro to ask me which file and directory? | Excel Discussion (Misc queries) | |||
How to force current directory | Excel Programming | |||
Set Default Directory | Excel Programming |