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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
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:
    984
    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:
    548
    Dave Peterson
    Sep 11, 2004
  3. Greg

    Create New Workbook - Name book - 4 Sheets - Name Sheets

    Greg, Jun 11, 2005, in forum: Microsoft Excel Programming
    Replies:
    6
    Views:
    1,067
    Dick Kusleika
    Jun 12, 2005
  4. Corey
    Replies:
    2
    Views:
    1,092
    Corey
    Nov 2, 2006
  5. Guest

    move module from a workbook to new workbook via VBA

    Guest, Mar 22, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    702
    Guest
    Mar 22, 2007
  6. Fireworks99
    Replies:
    2
    Views:
    317
    Guest
    Apr 24, 2007
  7. Rligouri

    Create New Workbook and add VBA Code to using VBA

    Rligouri, Oct 15, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    2,146
    Dave Peterson
    Oct 15, 2007
  8. Replies:
    7
    Views:
    1,405
    JLatham
    Feb 3, 2008
Loading...