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

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

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

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

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