Hello. I am using Excel 2000 and am green in
using VBA, as I am self-taught. What would seem to
be a simple scenario eludes me as to how to solve
this.
The Excel project I am working on is not in the
My Documents folder. From this different directory
I open up a Workbook which I refer to as Source
file. Let's say it is ABC.xls.
I want to create a macro in ABC.xls which will
open up an "empty" Workbook. By "empty" I
mean the standard blank 3 sheet Workbook,
with no content at all. (Thus, using SaveAs or
SaveCopyAs statements will not work, for they
copy the entire contents of ABC into the new
Workbook). This newly created file also has to
be in the same path as that of ABC.xls and
it has to be open.
I don't care what the original name of the newly
added file is. (I will later rename it). The reason
the newly added Workbook has to be in the
same path as that of ABC.xls is that I will copy
certain sheets to it from ABC, based upon
certain conditions.
The VBA code I have tried does not work, for
when I use it to add a new empty Workbook,
it adds it to my C root directory and not to the
same path as that of ABC.xls. I need, however,
as I stated above for the newly added Workbook
to be in the same path as the other Excel file and
to be open.
Can you please show me VBA code that would indeed
accomplish this ? I do not care how my code below has
to be altered to achieve this. Again, my needs are :
(1) the newly added Workbook to be empty, (2) open,
and (3) in the same path as that of the original Source
file.
I truly need your help and deeply appreciate your
offering it.
Thank you.
Ken
Here's the VBA code which does not work is
'Add an empty Workbook
Workbooks.Add
'Get its Name
DupeName = ThisWorkbook.Name
(using DupeName = ActiveWorkbook.Name
does not work either)
'Get its Path
DupePath = ThisWorkbook.Path
(using DupePath = ThisWorkbook.Path
does not work either)
using VBA, as I am self-taught. What would seem to
be a simple scenario eludes me as to how to solve
this.
The Excel project I am working on is not in the
My Documents folder. From this different directory
I open up a Workbook which I refer to as Source
file. Let's say it is ABC.xls.
I want to create a macro in ABC.xls which will
open up an "empty" Workbook. By "empty" I
mean the standard blank 3 sheet Workbook,
with no content at all. (Thus, using SaveAs or
SaveCopyAs statements will not work, for they
copy the entire contents of ABC into the new
Workbook). This newly created file also has to
be in the same path as that of ABC.xls and
it has to be open.
I don't care what the original name of the newly
added file is. (I will later rename it). The reason
the newly added Workbook has to be in the
same path as that of ABC.xls is that I will copy
certain sheets to it from ABC, based upon
certain conditions.
The VBA code I have tried does not work, for
when I use it to add a new empty Workbook,
it adds it to my C root directory and not to the
same path as that of ABC.xls. I need, however,
as I stated above for the newly added Workbook
to be in the same path as the other Excel file and
to be open.
Can you please show me VBA code that would indeed
accomplish this ? I do not care how my code below has
to be altered to achieve this. Again, my needs are :
(1) the newly added Workbook to be empty, (2) open,
and (3) in the same path as that of the original Source
file.
I truly need your help and deeply appreciate your
offering it.
Thank you.
Ken
Here's the VBA code which does not work is
'Add an empty Workbook
Workbooks.Add
'Get its Name
DupeName = ThisWorkbook.Name
(using DupeName = ActiveWorkbook.Name
does not work either)
'Get its Path
DupePath = ThisWorkbook.Path
(using DupePath = ThisWorkbook.Path
does not work either)