Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    File Name from a Cell (2003 SP2)

    Is there a way to set the file name (that Excel prompts you with the first time a file is saved) to the value of a cell?

    I am sending out a read-only template to people who will choose their name inside the spreadsheet from a drop-down list. It would be great if when they saved it they were prompted with their own last name, so that I got back files that could be sorted more easily.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Name from a Cell (2003 SP2)

    Here is an idea for you:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wbName As String

    wbName = Worksheets("Sheet1").Cells(1, 1).Value

    ActiveWorkbook.SaveAs Filename:=wbName & ".xls"


    End Sub

    I am assuming that you can add something similar to your current code. I have made wbName flexible in case the user is on a different worksheet. It currently points to Cell A1 in sheet 1
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Name from a Cell (2003 SP2)

    I don't think it's easy to set the suggested filename for a workbook in the Save As dialog.
    You could put a button on the worksheet that the users fill in, with the following line in the code that is executed when the user clicks it:

    Application.Dialogs(xlDialogSaveAs).Show Worksheets("Sheet1").Range("B3") & ".xls"

    This will display the Save As dialog, with the value of cell B3 on Sheet1 as suggested filename.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    Couldn't get this to work - it didn't seem to be able to override the default name of the file (based on the name of the template).

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    Not specific enough - Jezza's suggestion didn't work.

    HansV's suggestion did work (his always do [img]/forums/images/smilies/wink.gif[/img].

    Thanks to both of you.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Name from a Cell (2003 SP2)

    Well it worked for me <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name from a Cell (2003 SP2)

    The code below, placed in the ThisWorkbook module should do what you want.

    <code>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strCurrentUser As String
    Dim vFileSpec As Variant
    If SaveAsUI Then
    strCurrentUser = Environ("Username")
    If strCurrentUser = "" Then
    strCurrentUser = InputBox("Please enter your username.")
    End If
    If strCurrentUser <> "" Then
    vFileSpec = Application.GetSaveAsFilename(strCurrentUser & ".xls", "Excel files, *.xls, All Files, *.*")
    If vFileSpec = False Then Exit Sub
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs (vFileSpec)
    Application.EnableEvents = True
    Cancel = True
    End If
    End If
    End Sub
    </code>
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    Jezza - no doubt it did work for you. But with my limited VBA skills I couldn't see that anything from the debug that could tell me why it didn't work on my machine.

    No biggie ... but I figured someone in the future might be able to learn from my experience. [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    LegareColemans' code did work the first time.

    But, in my case, it seems to have picked up some name other than mine - perhaps from proprties, or the Excel registration (I got my employers initials). So, this will only work if someone is working on the spreadsheet from their own computer.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name from a Cell (2003 SP2)

    My code picks up the user name of the user logged onto Windows. This is the user name that Windows puts into the Environment when the user logs on. What user name did you want to use?
    Legare Coleman

  11. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    Hmmm. I'm content with the button suggested by HansV, but if you want to work the bugs out of your suggestion ...

    We're getting beyond my abilities here. I don't know much about networking.

    We use a Novell Network, with XP on our desktops. When I log on to the desktop, I use my last name. What your code gathered from XP was the initials of my employer (SUU) rather than my last name. Not sure why it would have that value stored, although those initials come up at several locations in the details of the Novell Network logon parameters.

    My guess is that your code is working properly, but that my network administrators are not putting the information you expect in that location.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: File Name from a Cell (2003 SP2)

    In our network environment the application.UserName gives our "Concern Wide ID" which is the ID we log onto all our networks.

    Instead of:
    strCurrentUser = Environ("Username")

    You could try
    strCurrentUser = application.UserName

    And this would put the name they have under tools-options - general. One problem could be in a work environment is that all were installed with the company name and people did not change it.

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: File Name from a Cell (2003 SP2)

    I suspected as much, but I don't know the VBA to check this.

    Thanks for the suggestion - this in fact is the situation at my workplace.

    Unfortunately, this means that Legare's (modified) suggestion is unlikely to work: I'm on the ball enough to have the username filled in on my PC's software, but I constantly get Office documents from people around my department showing the default name SUU. So, in my case HansV's suggestion worked out the best.

    Thanks to all!

    EOM

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Name from a Cell (2003 SP2)

    You can change my code to the below, and it will always ask the user for his username:

    <code>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strCurrentUser As String
    Dim vFileSpec As Variant
    If SaveAsUI Then
    strCurrentUser = InputBox("Please enter your username.")
    If strCurrentUser <> "" Then
    vFileSpec = Application.GetSaveAsFilename(strCurrentUser & ".xls", "Excel files, *.xls, All Files, *.*")
    If vFileSpec = False Then Exit Sub
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs (vFileSpec)
    Application.EnableEvents = True
    Cancel = True
    End If
    End If
    End Sub

    </code>
    Legare Coleman

Posting Permissions

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