Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you name a new workbook in VBA
I am trying to create a new workbook out of vba. When I try to name it
something beside the default book1,book2, etc... I get a "can't assign to read-only property" error. How do I name a new workbook? My code follows with public variables savetofile and savetorange Public Sub manageoutput_new() Dim mywb As Workbook, myws As Worksheet Set mywb = Workbooks.Add '* problem line is next mywb.Name = savetofile mywb.Activate Set myws = Worksheets.Add myws.Name = savetorange End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you name a new workbook in VBA
Bill
You will have to save as, you can't just rename it e.g mywb.SaveAs Filename:="Drive:\Path\Filename.xls" -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "in-over-his-head-bill" wrote in message ... I am trying to create a new workbook out of vba. When I try to name it something beside the default book1,book2, etc... I get a "can't assign to read-only property" error. How do I name a new workbook? My code follows with public variables savetofile and savetorange Public Sub manageoutput_new() Dim mywb As Workbook, myws As Worksheet Set mywb = Workbooks.Add '* problem line is next mywb.Name = savetofile mywb.Activate Set myws = Worksheets.Add myws.Name = savetorange End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you name a new workbook in VBA
You must save it with a name
Workbooks you save and not name like worksheets mywb.SaveAs "C:\test.xls" -- Regards Ron de Bruin http://www.rondebruin.nl "in-over-his-head-bill" wrote in message ... I am trying to create a new workbook out of vba. When I try to name it something beside the default book1,book2, etc... I get a "can't assign to read-only property" error. How do I name a new workbook? My code follows with public variables savetofile and savetorange Public Sub manageoutput_new() Dim mywb As Workbook, myws As Worksheet Set mywb = Workbooks.Add '* problem line is next mywb.Name = savetofile mywb.Activate Set myws = Worksheets.Add myws.Name = savetorange End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you name a new workbook in VBA
Thanks to you both for the quick reply --- that did the trick.
"Ron de Bruin" wrote: You must save it with a name Workbooks you save and not name like worksheets mywb.SaveAs "C:\test.xls" -- Regards Ron de Bruin http://www.rondebruin.nl "in-over-his-head-bill" wrote in message ... I am trying to create a new workbook out of vba. When I try to name it something beside the default book1,book2, etc... I get a "can't assign to read-only property" error. How do I name a new workbook? My code follows with public variables savetofile and savetorange Public Sub manageoutput_new() Dim mywb As Workbook, myws As Worksheet Set mywb = Workbooks.Add '* problem line is next mywb.Name = savetofile mywb.Activate Set myws = Worksheets.Add myws.Name = savetorange End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you name a new workbook in VBA
Hi ?
Try this, please. Option Explicit Sub NewFile() Dim FileFullName As String Dim ShName As String Dim FileName As String Dim NewBook As Workbook Dim sh As Variant '// Filename with Path FileFullName = "c:\Allsales01.xls" '// Sheet Name ShName = "Test" FileName = Mid(FileFullName, _ InStrRev(FileFullName, "\") + 1 _ , Len(FileFullName) - InStrRev(FileFullName, "\")) On Error Resume Next If Dir(FileFullName) < "" Then Application.Windows(FileName).Close True End If On Error GoTo 0 Set NewBook = Workbooks.Add With NewBook '// Properties .Title = "All Sales" .Subject = "Sales" Application.DisplayAlerts = False '// Save .SaveAs FileName:=FileFullName Application.DisplayAlerts = False End With '// Only one sheet For sh = 1 To (ActiveWorkbook.Sheets.Count - 1) Application.DisplayAlerts = False Sheets(sh).Delete Next sh Application.DisplayAlerts = True ActiveSheet.Name = ShName Set NewBook = Nothing '// Close Application.Windows(FileName).Close True End Sub -- Best regards Joergen Bondesen "in-over-his-head-bill" wrote in message ... I am trying to create a new workbook out of vba. When I try to name it something beside the default book1,book2, etc... I get a "can't assign to read-only property" error. How do I name a new workbook? My code follows with public variables savetofile and savetorange Public Sub manageoutput_new() Dim mywb As Workbook, myws As Worksheet Set mywb = Workbooks.Add '* problem line is next mywb.Name = savetofile mywb.Activate Set myws = Worksheets.Add myws.Name = savetorange End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ?? | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |