Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change default file name? (Office XP)

    Is it possible to change the default file name from "Book1.xls" to something of our own making? My organisation has moved to a set of file naming conventions that include the date the file was written (I know, I know, it's already in the file properties. <img src=/S/RollEyes.gif border=0 alt=RollEyes width=19 height=19> Would they listen? <img src=/S/nope.gif border=0 alt=nope width=15 height=15> ) as well as a few other details. It would be great if I could automate as much of the mumbo jumbo they require as possible.

    (Sorry for the <img src=/S/rant.gif border=0 alt=rant width=66 height=37> in the middle of that!)
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change default file name? (Office XP)

    You could open a new file, add this code to the thisworkbook object and save it as Book.xlt in the XLStart or alternate startup directory.(this creates the default book)

    <pre>Option Explicit
    Private Sub Workbook_Open()
    Dim sPath As String
    sPath = Application.DefaultFilePath
    If Right(sPath, 1) <> "" Then _
    sPath = sPath & ""
    With ThisWorkbook
    If .FileFormat <> xlTemplate And _
    UCase(Right(.Name, 4)) <> ".XLS" Then
    .SaveAs FileName:=sPath & "Book " & _
    Format(Now, "yyyy-mm-dd hh-mm") & ".xls"
    End If
    End With
    End Sub
    </pre>


    When a new book is created or excel is opened it will save it the the default path with the name "Book date time" for ecample something like:
    "Book 2005-06-04 11-37.xls"

    Change the formatting as desired...

    If the workbook has already been saved [it will have an XLS extension or is a template (reopening the book.xlt)] no save as is done

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change default file name? (Office XP)

    Steve,

    I'd prefer to put some code in Personal.xls tied to a button, so that not each and every workbook gets a macro in it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change default file name? (Office XP)

    A good thought. It all depends on what they want. I went this way, since they wanted a "standard" for everyone and personal.xls can be personal and be different for each individual and they wanted to actually change the default.

    Making them always remember to run some code (even by pushing a button) is just a notch better than having them name the file correctly themselves...

    Another option, If desired, is to clear the code (Chip Pearson has some examples of Programming To The VBE) after renaming and then save the file.

    It is possible that book.xlt could create the new file, saveas a new name, then clear the code in itself...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •