Results 1 to 5 of 5

Create An Empty Workbook (VBA)

This is a discussion on Create An Empty Workbook (VBA) within the Excel Questions forums, part of the Question Forums category; Hello. I am using Excel 2000 and am green in using VBA , as I am self-taught. What would seem ...

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Posts
    315

    Default Create An Empty Workbook (VBA)

    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)

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,391

    Default

    You need to get the path before you create the new workbook. How's this:

    Sub yada()
    ****Dim x As String
    ****Dim y As String
    ****
    ********x = ActiveWorkbook.Path
    ********y = InputBox("Enter the new workbook name", "Save As")
    ********
    ********Workbooks.Add
    ********ActiveWorkbook.SaveAs Filename:=x & "\" & y & ".xls"
    ****
    End Sub


    HTH,

    Smitty
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,267
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Sep 2003
    Posts
    315

    Default

    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

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,391

    Default

    Here's a bit condensed:
    Sub yada()
    ****Dim x As String
    ****
    ********x = ActiveWorkbook.Path & "\" & InputBox("Enter the new workbook name", "Save As") & ".xls"
    ********
    ********Workbooks.Add
    ********ActiveWorkbook.SaveAs Filename:=x
    ****
    End Sub


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

    Smitty
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com