PC Review


Reply
Thread Tools Rate Thread

How do you name a new workbook in VBA

 
 
=?Utf-8?B?aW4tb3Zlci1oaXMtaGVhZC1iaWxs?=
Guest
Posts: n/a
 
      10th Jul 2006
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 With Quote
 
 
 
 
Nick Hodge
Guest
Posts: n/a
 
      10th Jul 2006
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 Removed)HIS


"in-over-his-head-bill" <(E-Mail Removed)> wrote
in message news:(E-Mail 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



 
Reply With Quote
 
 
 
 
Ron de Bruin
Guest
Posts: n/a
 
      10th Jul 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail 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



 
Reply With Quote
 
=?Utf-8?B?aW4tb3Zlci1oaXMtaGVhZC1iaWxs?=
Guest
Posts: n/a
 
      10th Jul 2006
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail 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

>
>
>

 
Reply With Quote
 
Joergen Bondesen
Guest
Posts: n/a
 
      10th Jul 2006
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" <(E-Mail Removed)> wrote
in message news:(E-Mail 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



 
Reply With Quote
 
=?Utf-8?B?SGFsaW0=?=
Guest
Posts: n/a
 
      11th Jul 2006
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 Removed) >




"in-over-his-head-bill" wrote:

> 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


"Joergen Bondesen" wrote:

> 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" <(E-Mail Removed)> wrote
> in message news:(E-Mail 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

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to save existing Workbook with a new new name, without changingthe open Workbook name? HammerJoe@gmail.com Microsoft Excel Programming 7 3rd Feb 2008 04:35 AM
Copy Range to a New WorkBook + Name Sheet a cell Value + Name WorkBook another Celll Value Corey Microsoft Excel Programming 2 2nd Nov 2006 06:01 AM
Re: Question??? You know when you double click in a username field you see your previous entries? How do you reset this so you can see your entries again. Thorsten Matzner Windows XP New Users 0 5th Jun 2004 04:34 PM
Question??? You know when you double click in a username field you see your previous entries? How do you reset this so you can see your entries again. =?Utf-8?B?Y2hyaXM=?= Windows XP General 1 3rd Jun 2004 08:17 PM
What commands do you use to name a workbook, save a workbook,open a workbook Steven R. Berke Microsoft Excel Programming 1 24th Jul 2003 11:37 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:00 AM.