How do you name a new workbook in VBA

Discussion in 'Microsoft Excel Programming' started by Guest, Jul 10, 2006.

  1. Guest

    Guest Guest

    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
     
    Guest, Jul 10, 2006
    #1
    1. Advertisements

  2. Guest

    Nick Hodge Guest

    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 news:...
    >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, Jul 10, 2006
    #2
    1. Advertisements

  3. Guest

    Ron de Bruin Guest

    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
    news:...
    >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
     
    Ron de Bruin, Jul 10, 2006
    #3
  4. Guest

    Guest Guest

    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
    > news:...
    > >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

    >
    >
    >
     
    Guest, Jul 10, 2006
    #4
  5. 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 news:...
    >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, Jul 10, 2006
    #5
  6. Guest

    Guest Guest

    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 < >




    "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" <> wrote
    > in message news:...
    > >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

    >
    >
    >
     
    Guest, Jul 11, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steven R. Berke

    What commands do you use to name a workbook, save a workbook,open a workbook

    Steven R. Berke, Jul 24, 2003, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    471
    pancho
    Jul 24, 2003
  2. gr8guy

    Export selected Sheet in a new workbook with old workbook name!

    gr8guy, Sep 9, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    257
    Dave Peterson
    Sep 11, 2004
  3. Corey
    Replies:
    2
    Views:
    735
    Corey
    Nov 2, 2006
  4. Guest

    move module from a workbook to new workbook via VBA

    Guest, Mar 22, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    433
    Guest
    Mar 22, 2007
  5. Replies:
    7
    Views:
    981
    JLatham
    Feb 3, 2008
Loading...

Share This Page