Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 questions! Related to combobox and time function. HELP!! | Excel Discussion (Misc queries) | |||
Questions related to Online Quiz | Excel Programming | |||
A couple RTD related questions, plus VBA timer object | Excel Programming | |||
Change cell value immediately after a save | Excel Programming | |||
Change cell value immediately after a save | Excel Programming |