Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Rename active workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    New Zealand
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to rename a workbook without saving it. Is there any way of doing this in VBA, or any other way?
    Thanks

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 21:08, John wrote:
    I need to rename a workbook without saving it. Is there any way of doing this in VBA, or any other way?
    Thanks
    Try this (and yes, to rename you have to save...otherwise there will be nothing there. If you still want your old copy, then don't use the Kill line).

    ActiveWorkbook.SaveAs "MyFile.xls"
    Kill "MyPreviousFileName.xls"

    Hope this helps,

    Russell

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rename active workbook

    What can you do if the file you want to delete varies in names?

    My file name will depend on what it was saved as originally, and this is pulled from a cell.

  4. #4
    Board Regular
    Join Date
    Feb 2011
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rename active workbook

    this is what I have so far that isn't working, looking at what you provided and amending it with some information I got from elsewhere -


    ActiveWorkbook.SaveAs "S:\aliclock\Department Forms\Delete\Sickness Forms Complete.xlsm"
    Kill "S:\aliclock\PAULINE SICKNESS TEST\" & Range("b11").Value
    Application.Quit

    It still is erroring, any ideas?

  5. #5
    New Member
    Join Date
    Apr 2013
    Location
    San Jose, Calif
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rename active workbook

    Try this one ......

    WBname$ = ThisWorkbook.FullName
    NewWBname$ = Left(WBname$, Len(WBname$) - 4) ' remove .xls
    NewWBname$ = NewWBname$ & "SMALL.xls"
    ActiveWorkbook.SaveAs NewWBname$
    Kill WBname$


    Prompt$ = "Now Saved as a SMALL file at " & NewWBname$ & Chr(13)
    Prompt$ = Prompt$ & " Killed " & WBname$ & Chr(13)
    Prompt$ = Prompt$ & " DONE "
    MsgBox Prompt$

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rename active workbook

    I was in a similar situation. I.E. I wanted to open up a copy of a template workbook, but name it specifically to the instance. You can save it as a temp file in your default %TEMP% folder. .tmp files are deleted on close. Therefore, it renames the file without saving another copy.

    Dim wb As Excel.Workbook
    Set wb = xApp.Workbooks.Add("S:\Engineering\Test_Sample_DB_files\SampleData.xltx")
    wb.SaveAs "C:\Users\sshive\AppData\Local\Temp\" & CStr(id) & ".tmp"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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