Change file name to name in a cell

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

[Aladin Akyurek]

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.

[Jack in the UK]

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 ]

[NateO]

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/viewtop...rum=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 ]

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:

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:

Private Sub Worksheet_Change(ByVal Target As Range) 

    If Target.Address = "$Y$1" Then 

        If Not Target.Text = "" Then 

            Application.EnableEvents = False 

            Target.Value = UCase(Target.Text)  ' change to uppercase

            If UCase(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)) = Target.Text Then 

                Exit Sub 

            Else 

                ThisWorkbook.SaveAs ThisWorkbook.Path & "" & Target.Text & ".xls" 

            End If 

            Application.EnableEvents = True 

        End If 

    End If 

End Sub 

Hope this helps,

Russell

[Russell Hauf]

Sorry, last post was mine.

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

[Ivan F Moala]

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

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

[NateO]

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