+ Reply to Thread
Results 1 to 4 of 4

Problems renaming activeworkbook via code

  1. #1
    Registered User
    Join Date
    07-01-2004
    Posts
    8

    Problems renaming activeworkbook via code

    Hello Everyone

    I am trying to re-name the file of an active workbook based on the contents of a cell.
    Thanks to the group I have managed this (sort of) but my current code is creating another copy of the file with the desired file name.

    What I need is the original file re-naming.

    The code I am using is:-

    Sub Macro1()

    Dim mypath
    Dim ThisFile As String
    Dim StartFile As String

    StartFile = ThisWorkbook.Name
    mypath = Workbooks(StartFile).Path
    If Right(mypath, 1) <> "\" Then mypath = mypath & "\"

    On Error Resume Next

    If ThisWorkbook.Name = "Testing.xls" Then
    ThisFile = mypath & Range("A1").Value
    'ActiveWorkbook.SaveAs Filename:=ThisFile 'note1
    ActiveWorkbook.Name = ThisFile 'note2
    End If

    'note1 Works but creates new file as well as original
    'note2 Attempt to rename existing - gives compile error
    'Cant assign to read-only property

    End Sub

    Can anyone help?

    Kenny
    Win Me and 2000
    Office 97 and 2000

  2. #2
    Registered User
    Join Date
    01-19-2005
    Posts
    4
    Does it have to be an open Workbook ?
    You can read the contents of a cell of a closed workbook and then
    use the "Name Statement" to change its name.
    Last edited by coloseum; 01-19-2005 at 08:38 AM.

  3. #3
    Registered User
    Join Date
    07-01-2004
    Posts
    8
    Alas no the file is open.
    I will be making changes to various cells then A1 is used as the filename.
    A macro in another file is used at a later stage and is dependent on the first files name.
    If I use SaveAs the file is named as I want but I am leaving too many redundant files.

    Kenny

  4. #4
    Registered User
    Join Date
    01-19-2005
    Posts
    4
    After u save the file under a new name use the "KILL" statement to delete the redundant file from your disk.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1