Create An Empty Workbook (VBA)

Ken1000

Active Member
Joined
Sep 28, 2003
Messages
315
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)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You need to get the path before you create the new workbook. How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> yada()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
        x = ActiveWorkbook.Path
        y = InputBox("Enter the new workbook name", "Save As")
        
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=x & "\" & y & ".xls"
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 
Upvote 0
Smitty, thank you, very much. If stars meant any thing here,
I'd give you a 100 starts for your help and the simplicity and
greatness of your code. This works perfectly.

Ken
 
Upvote 0
Here's a bit condensed:
<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> yada()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
        x = ActiveWorkbook.Path & "\" & InputBox("Enter the new workbook name", "Save As") & ".xls"
        
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=x
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Note that this doesn't take into account if the WB name entered already exists.

Smitty
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top