How do you name a new workbook in VBA

[in-over-his-head-bill]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Ron de Bruin]

You must save it with a name
Workbooks you save and not name like worksheets

mywb.SaveAs "C:\test.xls"

 

[in-over-his-head-bill]

Thanks to you both for the quick reply --- that did the trick.

 

[Joergen Bondesen]

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

 

[Halim]

Hi Bill,

Of course you can't change the design time properties ...!

But you can always can seek a way by :
1. Change the caption of active Workbooks from
application class or your macro ...(most of us recognize activeworkbooks name
from it's windows caption)

2. Seek a way this from ApplicationClass from Class Module.
by changing Newworkbook's caption of it's window.

Try this:

'Place this to your ClassModule:

Option Explicit

Public WithEvents appl As Application
'After typing words above choose 'appl' in module mode (General); Class;
'or appl you made then will appear appl's events

Private Sub appl_NewWorkbook(ByVal Wb As Workbook)
Windows(Wb.Name).Caption = "MyNewWorkBook" & r
End Sub

Private Sub appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
SendKeys CStr((ActiveWindow.Caption)) & ".xls"
End Sub

'Place this to Stdard Module :

Dim AplClass As New AppEventClass '--> must match to your Class module (Name)
' Rename Class1 (name) to AppEventClass

'then run this procedure :
Option Explicit
Public r as long 'sign new series of WB
Private Sub ChangeNewWBName()
r = r + 1
Set AplClass.appl = Application
End Sub

'then try to create new Workbooks from your excel by normal way

Try .... this please and reply... if it's work or not ...


<smile>

halim < (e-mail address removed) >

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