Renaming excel workbook

[sanilmathews]

Hi,

Can someone help me with the code to rename an excel workbook with name of the folder in which the workbook already exist.

Thanks in advance!!

 

[GTO]

So if the FullName is C:\Data\MyWB.xls, we want if to rename itself Data.xls?

 

[VoG]

Try like this

Sub saveme()
With ActiveWorkbook
    .SaveAs Filename:=.Path & Application.PathSeparator & "test.xls"
End With
End Sub

 

[sanilmathews]

Code:

Sub RenameMe()
Dim sFile As String

sFile = ThisWorkbook.FullName
ThisWorkbook.SaveAs Filename:=ThisWorkbook.path & "\" & Worksheets("Sheet1").Range("A1").Value
Kill sFile
End Sub

I have the above code working fine i.e. renaming workbook referring a cell. But now I require a code working similar. But to rename the existing workbook referring the name of the folder it is saved in.

GTO got it right!

 

[GTO]

Two questions:
Are you wanting to Kill the originally named wb?
Is the resultant wb to remain open?

I ask the last question, as if it will be closed, we could just save a copy.

Anyway, here is the first bit:

Sub SaveAsFoldername()
Dim sFolderName     As String
Dim sPath           As String
Dim sWBNewName      As String
    
    '// Get our path, the folder's name thisworkbook resides in, and see what the new   //
    '// saveas name would be.                                                           //
    sPath = ThisWorkbook.Path & Application.PathSeparator
    sFolderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, Application.PathSeparator) + 1)
    sWBNewName = sFolderName & _
                 Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, "."))
    '// Check to make sure we haven't already done this (ie - make sure the fullname    //
    '/  doesn't already exist.).                                                        //
    If Not Len(Dir(sPath & sWBNewName)) > 0 Then
        ThisWorkbook.SaveAs sPath & sWBNewName
    End If
End Sub

 

[sanilmathews]

Thanks for the effort!!

Answering your first question - Yes I need the MyWb.xls to be killed, resulting only Data.xls a new renamed workbook to be saved in the folder named "Data". I see the above code is creating a new copy of workbook with the name Data.xls while having MyWb.xls still in the folder.

Answering your second question - If I had understood your question, Yes the resultant workbook will be remained open.

 

[GTO]

Okay. At your own risk (Kill makes things go bye-bye, not to recycle), I would test a few times, but works for me.

Sub SaveAsFoldername()
Dim sFolderName     As String
Dim sPath           As String
Dim sWBNewName      As String
Dim wb              As Workbook
    
    '// Get our path, the folder's name thisworkbook resides in, and see what the new   //
    '// saveas name would be.                                                           //
    sPath = ThisWorkbook.Path & Application.PathSeparator
    sFolderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, Application.PathSeparator) + 1)
    sWBNewName = sFolderName & _
                 Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, "."))
    '// Check to make sure we haven't already done this (ie - make sure the fullname    //
    '/  doesn't already exist.).                                                        //
    If Not Len(Dir(sPath & sWBNewName)) > 0 Then
        '// SaveCopy, then open it. //
        'ThisWorkbook.SaveAs sPath & sWBNewName
        ThisWorkbook.SaveCopyAs sPath & sWBNewName
        
        Do
        Loop While Len(Dir(sPath & sWBNewName)) = 0
        
        DoEvents
        
        Set wb = Workbooks.Open(sPath & sWBNewName)
        
        DoEvents
        
        '// Original wb gets really depressed...                                        //
        With ThisWorkbook
            .ChangeFileAccess xlReadOnly
            .Saved = True
            Kill .FullName
            .Close False
        End With
        
        
    End If
End Sub

 

[sanilmathews]

Thanks again for the effort!! And am happy that it is working per my requirement.

But just curious to know...

1. Why do I get a message popup stating "Do you want to save changes before switching file status?" with "Yes" or "No" option right after I run the macro. And what is that really mean?

2. '// Original wb gets really depressed... //
With ThisWorkbook
.ChangeFileAccess xlReadOnly
.Saved = True
Kill .FullName
.Close False
End With

What is really happening in the above steps?

Asking just for my knowledge... If you dont mind please explain them in brief.

 

[GTO]

Thanks again for the effort!! And am happy that it is working...

Glad to help

1. Why do I get a message popup stating "Do you want to save changes before switching file status?" with "Yes" or "No" option right after I run the macro. And what is that really mean?

Hmmm... New one on me. Not sure if its a 2010 "improvement" or I am forgetting how to do this w/o alerts. Try:

<font face=Courier New>        <SPAN style="color:#007F00">'// Original wb gets really depressed...                                        //</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ThisWorkbook<br>            .Saved = <SPAN style="color:#00007F">True</SPAN><br>            .ChangeFileAccess xlReadOnly<br>            .Saved = <SPAN style="color:#00007F">True</SPAN><br>            Kill .FullName<br>            .Close <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        </FONT>

2. '// Original wb gets really depressed... //
With ThisWorkbook
.ChangeFileAccess xlReadOnly
.Saved = True
Kill .FullName
.Close False
End With

What is really happening in the above steps?

Asking just for my knowledge... If you dont mind please explain them in brief.

Sure. With the added line, we are first marking the wb as Saved, so we don't get that dang question about file status; then we switch file status to ReadOnly. (This is the critical point, as this 'unlocks' or 'un-reserves' the actual wb on disk, so it can be deleted.) We again mark the open wb as Saved (since we cahnged status). Kill the wb on disk, and close what is now essentially the same thing as a new unsaved wb.

If that is unclear (it was for me somewhat), try this.

In Windows Explorer.

1. Make a temp folder someplace on your flashdrive (just so we skip recycle)
2. create two new wb's in it, naming one "Owner.xlsm" and the other "ReadOnly.xlsm".
3. right-click "Read-Only.xlsm", choose properties, and mark it read-only.
4. Open both.
5. Reduce excel and look in Explorer. See how only "Owner.xlsm" gets that little funny copy ("~$Owner.xlsm") showing? It is because the read-only file is not marked by the system as in-use/reserved.
6. Now, you still have both wb's open, right? While still in Explorer, click on Read-Only.xlsm and delete it. Windows will ask if you are sure, but if you choose yes, it will delete. Now try the same thing with Owner.xlsm and Windows will tell you it cannot, the file is in use, etc...

Does that make sense?

Mark

 

[sanilmathews]

Thanks a lot for sharing your knowledge.... getting your point!!