Naming a new workbook without saving

  • Thread starter Thread starter Wullie
  • Start date Start date
W

Wullie

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
 
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?
 
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
 
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).
 
Back
Top