Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel 2003, how do I automate file naming/saving + date? | Setting up and Configuration of Excel | |||
Naming and saving a workbook | Excel Programming | |||
Naming a workbook WITHOUT saving it? | Excel Programming | |||
Naming a workbook WITHOUT saving it? | Excel Programming | |||
Naming a workbook WITHOUT saving it? | Excel Programming |