Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Change file name to name in a cell

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.

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

    Default

    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 ]

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Guest

    Default

    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:



    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



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

    Default

    Sorry, last post was mine.

  7. #7
    Guest

    Default

    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


  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Guest

    Default

    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

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

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
  •