+ Reply to Thread
Results 1 to 5 of 5

Add workbook & save immediately (& 2 related questions)

  1. #1
    Ray
    Guest

    Add workbook & save immediately (& 2 related questions)

    First, thanks to Chip Pearson for the "posting tips" info -- very helpful!

    I'm relatively new to VB for Excel, but have learned ALOT from this forum
    and J-Walk's "Power Programming with VBA" ..... however, a couple of fairly
    basic (I think) questions keep popping up for me.

    Oh yeah, I'm using Excel2002 on an XP computer....
    Here are my 3 issues:
    1) As part of a much larger project, I need to Add a new workbook and save
    it immediately with a specific name. I know how to add the workbook and how
    to specifiy the name (shortcut requested below) but how do I identify the new
    workbook to be saved? I've tried this code, but it didn't work:
    Workbook.Add
    ActiveWorkbook.SaveAs......
    If I need to specify which workbook to save, how do I identify it? If the
    end-user has been working for a while, the new workbook may have name
    'Book15' (or whatever).

    2) I need a shortcut and I can't find Help with this anywhere....how do I
    'combine' 2 variables into ONE? I've tried this but it didn't work:
    Dim Path as String; Dim Yw as String; Dim FileName as String
    Path = .....
    Yw = .......
    FileName = Path & Yw
    What am I doing wrong?

    3) OK, this one isn't really related, except in my project
    I'm copying data from one workbook to a new one. In the orig workbook,
    the title of the worksheet uses the worksheet tab name. When this info is
    pasted into a new workbook, I want the tab name to reflect the worksheet
    title (data in cell D1). I know the code to do this .... but how do I insert
    this code automatically into the New workbook using VBA?

    Any/all help is greatly appreciated .... I look forward to being able to
    help others at some point!

    rgds, ray



  2. #2
    Die_Another_Day
    Guest

    Re: Add workbook & save immediately (& 2 related questions)

    For Adding/saving the workbook try this:
    Dim nWorkbook as Workbook
    Set nWorkbook = Workbooks.Add
    nWorkbook.SaveAs 'enter filename

    Your string should work as long as you have all the path separotors in
    there
    HTH

    Die_Another_Day
    Ray wrote:
    > First, thanks to Chip Pearson for the "posting tips" info -- very helpful!
    >
    > I'm relatively new to VB for Excel, but have learned ALOT from this forum
    > and J-Walk's "Power Programming with VBA" ..... however, a couple of fairly
    > basic (I think) questions keep popping up for me.
    >
    > Oh yeah, I'm using Excel2002 on an XP computer....
    > Here are my 3 issues:
    > 1) As part of a much larger project, I need to Add a new workbook and save
    > it immediately with a specific name. I know how to add the workbook and how
    > to specifiy the name (shortcut requested below) but how do I identify the new
    > workbook to be saved? I've tried this code, but it didn't work:
    > Workbook.Add
    > ActiveWorkbook.SaveAs......
    > If I need to specify which workbook to save, how do I identify it? If the
    > end-user has been working for a while, the new workbook may have name
    > 'Book15' (or whatever).
    >
    > 2) I need a shortcut and I can't find Help with this anywhere....how do I
    > 'combine' 2 variables into ONE? I've tried this but it didn't work:
    > Dim Path as String; Dim Yw as String; Dim FileName as String
    > Path = .....
    > Yw = .......
    > FileName = Path & Yw
    > What am I doing wrong?
    >
    > 3) OK, this one isn't really related, except in my project
    > I'm copying data from one workbook to a new one. In the orig workbook,
    > the title of the worksheet uses the worksheet tab name. When this info is
    > pasted into a new workbook, I want the tab name to reflect the worksheet
    > title (data in cell D1). I know the code to do this .... but how do I insert
    > this code automatically into the New workbook using VBA?
    >
    > Any/all help is greatly appreciated .... I look forward to being able to
    > help others at some point!
    >
    > rgds, ray



  3. #3
    Ray
    Guest

    Re: Add workbook & save immediately (& 2 related questions)

    Thanks D_A_D .... this worked well.

    Any thoughts on my other two 'issues', esp the first one regarding
    Variables? My code is very messy, since the same long strings appear
    multiple times.

    Ray


    "Die_Another_Day" wrote:

    > For Adding/saving the workbook try this:
    > Dim nWorkbook as Workbook
    > Set nWorkbook = Workbooks.Add
    > nWorkbook.SaveAs 'enter filename
    >
    > Your string should work as long as you have all the path separotors in
    > there
    > HTH
    >
    > Die_Another_Day
    > Ray wrote:
    > > First, thanks to Chip Pearson for the "posting tips" info -- very helpful!
    > >
    > > I'm relatively new to VB for Excel, but have learned ALOT from this forum
    > > and J-Walk's "Power Programming with VBA" ..... however, a couple of fairly
    > > basic (I think) questions keep popping up for me.
    > >
    > > Oh yeah, I'm using Excel2002 on an XP computer....
    > > Here are my 3 issues:
    > > 1) As part of a much larger project, I need to Add a new workbook and save
    > > it immediately with a specific name. I know how to add the workbook and how
    > > to specifiy the name (shortcut requested below) but how do I identify the new
    > > workbook to be saved? I've tried this code, but it didn't work:
    > > Workbook.Add
    > > ActiveWorkbook.SaveAs......
    > > If I need to specify which workbook to save, how do I identify it? If the
    > > end-user has been working for a while, the new workbook may have name
    > > 'Book15' (or whatever).
    > >
    > > 2) I need a shortcut and I can't find Help with this anywhere....how do I
    > > 'combine' 2 variables into ONE? I've tried this but it didn't work:
    > > Dim Path as String; Dim Yw as String; Dim FileName as String
    > > Path = .....
    > > Yw = .......
    > > FileName = Path & Yw
    > > What am I doing wrong?
    > >
    > > 3) OK, this one isn't really related, except in my project
    > > I'm copying data from one workbook to a new one. In the orig workbook,
    > > the title of the worksheet uses the worksheet tab name. When this info is
    > > pasted into a new workbook, I want the tab name to reflect the worksheet
    > > title (data in cell D1). I know the code to do this .... but how do I insert
    > > this code automatically into the New workbook using VBA?
    > >
    > > Any/all help is greatly appreciated .... I look forward to being able to
    > > help others at some point!
    > >
    > > rgds, ray

    >
    >


  4. #4
    Die_Another_Day
    Guest

    Re: Add workbook & save immediately (& 2 related questions)

    You are combining them correctly. What error are you getting?

    Die_Another_Day

    Ray wrote:
    > Thanks D_A_D .... this worked well.
    >
    > Any thoughts on my other two 'issues', esp the first one regarding
    > Variables? My code is very messy, since the same long strings appear
    > multiple times.
    >
    > Ray
    >
    >
    > "Die_Another_Day" wrote:
    >
    > > For Adding/saving the workbook try this:
    > > Dim nWorkbook as Workbook
    > > Set nWorkbook = Workbooks.Add
    > > nWorkbook.SaveAs 'enter filename
    > >
    > > Your string should work as long as you have all the path separotors in
    > > there
    > > HTH
    > >
    > > Die_Another_Day
    > > Ray wrote:
    > > > First, thanks to Chip Pearson for the "posting tips" info -- very helpful!
    > > >
    > > > I'm relatively new to VB for Excel, but have learned ALOT from this forum
    > > > and J-Walk's "Power Programming with VBA" ..... however, a couple of fairly
    > > > basic (I think) questions keep popping up for me.
    > > >
    > > > Oh yeah, I'm using Excel2002 on an XP computer....
    > > > Here are my 3 issues:
    > > > 1) As part of a much larger project, I need to Add a new workbook and save
    > > > it immediately with a specific name. I know how to add the workbook and how
    > > > to specifiy the name (shortcut requested below) but how do I identify the new
    > > > workbook to be saved? I've tried this code, but it didn't work:
    > > > Workbook.Add
    > > > ActiveWorkbook.SaveAs......
    > > > If I need to specify which workbook to save, how do I identify it? If the
    > > > end-user has been working for a while, the new workbook may have name
    > > > 'Book15' (or whatever).
    > > >
    > > > 2) I need a shortcut and I can't find Help with this anywhere....how do I
    > > > 'combine' 2 variables into ONE? I've tried this but it didn't work:
    > > > Dim Path as String; Dim Yw as String; Dim FileName as String
    > > > Path = .....
    > > > Yw = .......
    > > > FileName = Path & Yw
    > > > What am I doing wrong?
    > > >
    > > > 3) OK, this one isn't really related, except in my project
    > > > I'm copying data from one workbook to a new one. In the orig workbook,
    > > > the title of the worksheet uses the worksheet tab name. When this info is
    > > > pasted into a new workbook, I want the tab name to reflect the worksheet
    > > > title (data in cell D1). I know the code to do this .... but how do I insert
    > > > this code automatically into the New workbook using VBA?
    > > >
    > > > Any/all help is greatly appreciated .... I look forward to being able to
    > > > help others at some point!
    > > >
    > > > rgds, ray

    > >
    > >



  5. #5
    Die_Another_Day
    Guest

    Re: Add workbook & save immediately (& 2 related questions)

    For your 3rd question. Are you want to copy vba code to the new
    workbook?

    if it is a module try code from Joseph Rubin's site:
    http://www.exceltip.com/st/Copy_modu...Excel/501.html

    Die_Another_Day
    Ray wrote:
    > Thanks D_A_D .... this worked well.
    >
    > Any thoughts on my other two 'issues', esp the first one regarding
    > Variables? My code is very messy, since the same long strings appear
    > multiple times.
    >
    > Ray
    >
    >
    > "Die_Another_Day" wrote:
    >
    > > For Adding/saving the workbook try this:
    > > Dim nWorkbook as Workbook
    > > Set nWorkbook = Workbooks.Add
    > > nWorkbook.SaveAs 'enter filename
    > >
    > > Your string should work as long as you have all the path separotors in
    > > there
    > > HTH
    > >
    > > Die_Another_Day
    > > Ray wrote:
    > > > First, thanks to Chip Pearson for the "posting tips" info -- very helpful!
    > > >
    > > > I'm relatively new to VB for Excel, but have learned ALOT from this forum
    > > > and J-Walk's "Power Programming with VBA" ..... however, a couple of fairly
    > > > basic (I think) questions keep popping up for me.
    > > >
    > > > Oh yeah, I'm using Excel2002 on an XP computer....
    > > > Here are my 3 issues:
    > > > 1) As part of a much larger project, I need to Add a new workbook and save
    > > > it immediately with a specific name. I know how to add the workbook and how
    > > > to specifiy the name (shortcut requested below) but how do I identify the new
    > > > workbook to be saved? I've tried this code, but it didn't work:
    > > > Workbook.Add
    > > > ActiveWorkbook.SaveAs......
    > > > If I need to specify which workbook to save, how do I identify it? If the
    > > > end-user has been working for a while, the new workbook may have name
    > > > 'Book15' (or whatever).
    > > >
    > > > 2) I need a shortcut and I can't find Help with this anywhere....how do I
    > > > 'combine' 2 variables into ONE? I've tried this but it didn't work:
    > > > Dim Path as String; Dim Yw as String; Dim FileName as String
    > > > Path = .....
    > > > Yw = .......
    > > > FileName = Path & Yw
    > > > What am I doing wrong?
    > > >
    > > > 3) OK, this one isn't really related, except in my project
    > > > I'm copying data from one workbook to a new one. In the orig workbook,
    > > > the title of the worksheet uses the worksheet tab name. When this info is
    > > > pasted into a new workbook, I want the tab name to reflect the worksheet
    > > > title (data in cell D1). I know the code to do this .... but how do I insert
    > > > this code automatically into the New workbook using VBA?
    > > >
    > > > Any/all help is greatly appreciated .... I look forward to being able to
    > > > help others at some point!
    > > >
    > > > rgds, ray

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1