Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Naming a new workbook without saving

Hi,

Is there a way to create a new workbook and give it a name (for referencing)
without saving the file?

I currently use SaveAs, but don't really need to keep the file.

However, I do need to jump between files so need to be able to reference
this new workbook.

It's probably something simple that I have overlooked, but any help would be
greatly appreciated.

My function is currently

Function NewWorkbookFunc(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add
NewWorkbook.SaveAs Filename:=curPath & "\Comparison_" & Format(Date,
"yyyy_mm_dd")
Worksheets.Add().Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

Can I just remove the SaveAs line and refer to NewWorkbook when activating
the new workbook?

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Naming a new workbook without saving

Okay, found my way around that problem, but now I would like my function to
return the newworkbookname as a string

I am calling the function like so

NewWorkbookName = NewWorkbookFunc(1)

and the function now looks like

Function NewWorkbookFunc(wsCount As Integer) As String
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
Dim NewWorkbookName As String

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add()
NewWorkbookName = NewWorkbook.Name
Worksheets("Sheet1").Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

but this is passing back an empty string value rather than the filename.

Any solutions?

"Wullie" wrote:

Hi,

Is there a way to create a new workbook and give it a name (for referencing)
without saving the file?

I currently use SaveAs, but don't really need to keep the file.

However, I do need to jump between files so need to be able to reference
this new workbook.

It's probably something simple that I have overlooked, but any help would be
greatly appreciated.

My function is currently

Function NewWorkbookFunc(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add
NewWorkbook.SaveAs Filename:=curPath & "\Comparison_" & Format(Date,
"yyyy_mm_dd")
Worksheets.Add().Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

Can I just remove the SaveAs line and refer to NewWorkbook when activating
the new workbook?

Thanks

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default Naming a new workbook without saving

hi,
change NewWorkbookName = NewWorkbook.Name
to NewWorkbookFunc = NewWorkbook.Name

another possibility is something like

Dim NewWorkbook
Function MakeWB()
Set NewWorkbook = Workbooks.Add
End Function
Function AccessNewWorkbook()
NewWorkbook.Sheets(1).Range("A1") = Time
End Function

If you need NewWorkbook in macros in different modules, use Public
instead of Dim (see VBA help also).

stefan

On 4 Sep., 12:10, Wullie wrote:
Okay, found my way around that problem, but now I would like my function to
return the newworkbookname as a string

I am calling the function like so

NewWorkbookName = NewWorkbookFunc(1)

and the function now looks like

Function NewWorkbookFunc(wsCount As Integer) As String
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
Dim NewWorkbookName As String

* * Set NewWorkbook = Nothing
* * If wsCount < 1 Or wsCount 255 Then Exit Function
* * OriginalWorksheetCount = Application.SheetsInNewWorkbook
* * Application.SheetsInNewWorkbook = wsCount
* * Set NewWorkbook = Workbooks.Add()
* * NewWorkbookName = NewWorkbook.Name
* * Worksheets("Sheet1").Name = "Comparison"
* * Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

but this is passing back an empty string value rather than the filename.

Any solutions?



"Wullie" wrote:
Hi,


Is there a way to create a new workbook and give it a name (for referencing)
without saving the file?


I currently use SaveAs, but don't really need to keep the file.


However, I do need to jump between files so need to be able to reference
this new workbook.


It's probably something simple that I have overlooked, but any help would be
greatly appreciated.


My function is currently


Function NewWorkbookFunc(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long


* * Set NewWorkbook = Nothing
* * If wsCount < 1 Or wsCount 255 Then Exit Function
* * OriginalWorksheetCount = Application.SheetsInNewWorkbook
* * Application.SheetsInNewWorkbook = wsCount
* * Set NewWorkbook = Workbooks.Add
* * NewWorkbook.SaveAs Filename:=curPath & "\Comparison_" & Format(Date,
"yyyy_mm_dd")
* * Worksheets.Add().Name = "Comparison"
* * Application.SheetsInNewWorkbook = OriginalWorksheetCount


End Function


Can I just remove the SaveAs line and refer to NewWorkbook when activating
the new workbook?


Thanks- Zitierten Text ausblenden -


- Zitierten Text anzeigen -


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Naming a new workbook without saving

Thanks for your help Stefan.

Turns out that they user did want the file saved after all, so I can just
revert back to my original code (typical).

"stefan onken" wrote:

hi,
change NewWorkbookName = NewWorkbook.Name
to NewWorkbookFunc = NewWorkbook.Name

another possibility is something like

Dim NewWorkbook
Function MakeWB()
Set NewWorkbook = Workbooks.Add
End Function
Function AccessNewWorkbook()
NewWorkbook.Sheets(1).Range("A1") = Time
End Function

If you need NewWorkbook in macros in different modules, use Public
instead of Dim (see VBA help also).

stefan

On 4 Sep., 12:10, Wullie wrote:
Okay, found my way around that problem, but now I would like my function to
return the newworkbookname as a string

I am calling the function like so

NewWorkbookName = NewWorkbookFunc(1)

and the function now looks like

Function NewWorkbookFunc(wsCount As Integer) As String
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
Dim NewWorkbookName As String

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add()
NewWorkbookName = NewWorkbook.Name
Worksheets("Sheet1").Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

but this is passing back an empty string value rather than the filename.

Any solutions?



"Wullie" wrote:
Hi,


Is there a way to create a new workbook and give it a name (for referencing)
without saving the file?


I currently use SaveAs, but don't really need to keep the file.


However, I do need to jump between files so need to be able to reference
this new workbook.


It's probably something simple that I have overlooked, but any help would be
greatly appreciated.


My function is currently


Function NewWorkbookFunc(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long


Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add
NewWorkbook.SaveAs Filename:=curPath & "\Comparison_" & Format(Date,
"yyyy_mm_dd")
Worksheets.Add().Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount


End Function


Can I just remove the SaveAs line and refer to NewWorkbook when activating
the new workbook?


Thanks- Zitierten Text ausblenden -


- Zitierten Text anzeigen -



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excel 2003, how do I automate file naming/saving + date? MichaelOfficer Setting up and Configuration of Excel 1 July 19th 09 01:46 PM
Naming and saving a workbook Kevin Porter Excel Programming 6 February 22nd 07 05:42 PM
Naming a workbook WITHOUT saving it? Maury Markowitz Excel Programming 1 March 21st 06 07:44 PM
Naming a workbook WITHOUT saving it? Tom Ogilvy Excel Programming 0 March 21st 06 07:41 PM
Naming a workbook WITHOUT saving it? Tom Ogilvy Excel Programming 1 March 21st 06 07:40 PM


All times are GMT +1. The time now is 05:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"