Change file name to name in a cell

G

Guest

Guest
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet.
DETAILS: The cell with the new file name is always Y1. The cell is blank initially (although it doesn't have to be).
OTHER INFO: OS= WIN 98 Excel= 2000 I'm new to VBA and macros.
EXAMPLE: If I input "STEVE C" into cell Y1, the name of the file will be STEVE C.xls
(I am not worried about the case in the file name itself. It would, however, be a benifit to have the contents of Y1 be all uppercase automatically - if that is also possible.)

Thank you for your help. This is the first time I've been here.
Steve C
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
On 2002-03-11 15:00, Anonymous wrote:
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet.
DETAILS: The cell with the new file name is always Y1. The cell is blank initially (although it doesn't have to be).
OTHER INFO: OS= WIN 98 Excel= 2000 I'm new to VBA and macros.
EXAMPLE: If I input "STEVE C" into cell Y1, the name of the file will be STEVE C.xls
(I am not worried about the case in the file name itself. It would, however, be a benifit to have the contents of Y1 be all uppercase automatically - if that is also possible.)

Thank you for your help. This is the first time I've been here.
Steve C

Did you try using UPPER?

You could say in Z1 =UPPER(Y1) and let the routine that you use look at Z1 instead of Y1.
 
Upvote 0
Hi\

I did not plan to answer but i was surfing the old questions and found this, will do the trick, click ok to comf the save will name as Y1 as you ask, hope its ok

//
Sub save_itas2()
fname = Application.GetSaveAsFilename _
(InitialFilename:=Range("Y1"), _
FileFilter:="Excel Files (*.xls),*.xls", FilterIndex:=0, Title:="Save As")
If fname<> "False" Then
ActiveWorkbook.SaveAs Filename:=fname
End If

End Sub
..........

Also if you must have file name as capitals UPPER function will do this.. xcan be added to format cell Y1 prior to saveas

If needs be loads in archive have a search i just put in saveas

This is from search in archive, by my friend Ivan F Moloa in New Zealand

Cheers Ivan



_________________
Good Luck
HTH

Rdgs
==========
Jack in the UK
This message was edited by Jack in the UK on 2002-03-11 15:24
 
Upvote 0
Jack, that code looks familiar (lol)! Good if you want a dialog, if not, you could try the following. I didn't test if y1 is blank, it will save the file as ".xls". Also, you'll get a warning if the file already exists.

Sub thNamer()
fname = WorksheetFunction.Substitute(UCase(Range("y1")), ".XLS", "")
ActiveWorkbook.SaveAs fname & ".xls"
End Sub

Hopefully this helps.

Cheers, Nate

As for always having y1 being capitalized, depends if you want it real-time or periodically. For real-time, look at the following:

http://www.mrexcel.com/board/viewtopic.php?mode=viewtopic&topic=651&forum=2&start=30

A lot of people had trouble getting this going. Post back with issues.
This message was edited by NateO on 2002-03-11 16:11
 
Upvote 0
On 2002-03-11 15:00, Anonymous wrote:
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet.
DETAILS: The cell with the new file name is always Y1. The cell is blank initially (although it doesn't have to be).
OTHER INFO: OS= WIN 98 Excel= 2000 I'm new to VBA and macros.
EXAMPLE: If I input "STEVE C" into cell Y1, the name of the file will be STEVE C.xls
(I am not worried about the case in the file name itself. It would, however, be a benifit to have the contents of Y1 be all uppercase automatically - if that is also possible.)

Thank you for your help. This is the first time I've been here.
Steve C

I'll throw my 2 cents in (I started this about an hour ago and got called away)...
Well, you could use Data Validation for the upper-case value in the cell, but since code is required for this, we'll just incorporate it there.

You could put the code in the Worksheet_Change event. What this will do is save each time the cell Y1 is changed (not sure that this is what you want, but it's a good example). To do this, open the VBE window (Alt+F11 or Tools-Macro-Visual Basic Editor). When that opens you should see a "Project" window on the left-hand side. Find your workbook and click on the + so that you "open the folder", and you should see a folder that says "Microsoft Excel Objects". Open that one, and you should see each sheet and "ThisWorkbook". Double-Click on the sheet that you want to use to rename your workbook. This should bring up a blank window on the right side. In the drop-down at the top of this window that says "(General)" (it will be on the left side), select "Worksheet". This will bring up the shell of a function, and should look like this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Change the drop-down on the right from SelectionChange to Change. You'll get a shell that looks exactly like the first, but the word "Selection" will not be there. Then you could put something like this in for your code:

<pre><font color='#000000'>
<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> Worksheet_Change(ByVal Target <font color='#000080'>As</font> Range)
<font color='#000080'>If</font> Target.Address = "$Y$1" <font color='#000080'>Then</font>
<font color='#000080'>If</font> <font color='#000080'>Not</font> Target.Text = "" <font color='#000080'>Then</font>
Application.EnableEvents = False
Target.Value = UCase(Target.Text) <font color='#008000'>' change to uppercase</font>
<font color='#000080'>If</font> UCase(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)) = Target.Text <font color='#000080'>Then</font>
<font color='#000080'>Exit</font> <font color='#000080'>Sub</font>
<font color='#000080'>Else</font>
ThisWorkbook.SaveAs ThisWorkbook.Path & "" & Target.Text & ".xls"
<font color='#000080'>End</font> <font color='#000080'>If</font>
Application.EnableEvents = True
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

Hope this helps,

Russell
 
Upvote 0
THANK YOU THANK YOU THANK YOU TO YOU ALL !
It works perfectly.

A QUESTION: How can I have the original file deleted from the same directory? (This new code creates a new copy made with the new name. The original file remains.)

Here is what I ended up with:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Path As String ' path of current worksheet
Dim ThisFileNew As String ' new file name including path
Dim Resp As Integer ' user response to overwrite query
Dim i As Integer

If Target = Range("AI1") Then
For i = 1 To Worksheets.Count
Worksheets(i).Name = Target.Value + i - 1
Next
End If
If Not Intersect(Target(1), Range("Y1")) Is Nothing Then
With Application
.EnableEvents = False
.DisplayAlerts = False
End With
On Error Resume Next
' Set cell contents (file name) to upper case
Target.Value = UCase(Target.Text)
' Get current path (empty if workbook has never been saved)
Path = ThisWorkbook.Path
If Not Path = "" Then Path = Path & ""
ThisFileNew = Path & Target.Text & ".xls"
Resp = vbOK
' Check for existing file of same name and, if present, ask whether to overwrite
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = False
.Filename = Target.Text & ".xls"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Resp = MsgBox("This file already exists. Overwrite? ", vbExclamation + vbOKCancel)
End If
End With
' Save the workbook if file does not exist, or if user wants to overwrite it
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Else
Resp = MsgBox("You will need to rename this file manually", vbInformation)
End If

On Error GoTo 0
With Application
.DisplayAlerts = True
.EnableEvents = True
End With
End If
End Sub


Thanks
SteveC
 
Upvote 0
On 2002-03-12 22:53, Anonymous wrote:
THANK YOU THANK YOU THANK YOU TO YOU ALL !
It works perfectly.

A QUESTION: How can I have the original file deleted from the same directory? (This new code creates a new copy made with the new name. The original file remains.)


Thanks
SteveC

Look up the Kill command. Just be careful how you use it
as you cannot get the file Back....you can
delete it to the recycle bin but that
involves extra API coding.


Ivan
 
Upvote 0
Ivan,
I have to plead ignorance. I didn't know anything about VBA until 4 days ago. (I even just looked up "kill" in Excel help...and of course didn't find it. lol)
Would you or anyone else know what to put where?

Thanks for any help
SteveC
 
Upvote 0
You'll need two insertions of code:

Place this code at the beginning of your procedure:

Dim fname As String
fname = ActiveWorkbook.FullName

Then change:
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Else

To:
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Kill fname
Else

Like Ivan mentioned, the old file is long gone. Hope this helps.

Cheers, Nate
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top