Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save a file (2000)

    I would like to make a control button that would take text from a cell, and save the Excel sheet as that text when pressed.
    ie: if cell a1 contained "Jones Corporation" then the file name would be "Jones Corporation.xls". Any help would be appreciated <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a file (2000)

    Something like this:

    Sub Button_Click()
    Dim sPath as string
    Dim sFilename as string
    sPath="Cata"
    sFilename=Activeworkbook.Activesheet.Range("A1").V alue
    If sFilename="" then
    MsgBox "No filename in cell A1, operation cancelled"
    Exit sub
    End if
    Activeworkbook.SaveAs sPath & sFilename
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a file (2000)

    ok THAT WORKS FINE, THANKS <img src=/S/joy.gif border=0 alt=joy width=23 height=23> -- JUST ONE MORE CHANGE IF YOU WILL.... IN ADDITION TO CELL A1 I WOULD LIKE TO ADD CELL D2 TO THE FILE NAME, LOOKS SOMETHING LIKE THIS JONES CORPERATION 24L8978.XLS. JONES CORP IS IN A1, AND 24L8978 IS IN CELL D2

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

    Re: Save a file (2000)

    Pieterse is not online right now, so I'll fill in for my countryman. If you want to concatenate the text in A1 and D2 with a space in between, use

    sFilename=Activeworkbook.Activesheet.Range("A1").V alue & _
    " " & Activeworkbook.Activesheet.Range("D2").Value

    (the underscore _ is the line continuation character; it indicates that the instruction continues on the next line; there should be a space before it

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a file (2000)

    I'v got something wrong and can't seem to decide what it is. Any help?
    Following is the statement:

    Private Sub CommandButton1_Click()

    Dim sPath As String
    Dim sFilename As String
    sPath = "C:Load Test Data"
    sFilename = ActiveWorkbook.ActiveSheet.Range("D5").Value & _
    " " & ActiveWorkbook.ActiveSheet.Range("D7").Value
    If sFilename = "" Then
    MsgBox "No filename in cell D5, operation cancelled"
    Exit Sub
    End If
    ActiveWorkbook.SaveAs sPath & sFilename <-- (this line is the one indicated as being the problem)

    End Sub

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

    Re: Save a file (2000)

    The code itself is OK, so check very carefully:
    - Does the 'C:Load Test Data' folder exist?
    - Do cell D5 or D7 contain unusual characters, such as a slash / or a backslash or something like that? Some such characters are not allowed in file names.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a file (2000)

    I'v checked both suggested areas, nothing there. However, both cells were empty. When I populated the cells all worked ok. Now I'm wondering why the error message didn't show up.

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

    Re: Save a file (2000)

    No error message because you didn't update the check. If D5 and D7 are empty, sFileName will be "" & " " & "", and that evaluates to " " (a space), so it is not "".

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a file (2000)

    Much Thanks, All is well in excel.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Save a file (2000)

    This little change to the code will solve that problem, and some other potential worries with spaces in filenames where they aren't obvious:

    <pre>Private Sub CommandButton1_Click()

    Dim sPath As String
    Dim sFilename As String
    sPath = "C:Load Test Data"
    sFilename = Trim(ActiveWorkbook.ActiveSheet.Range("D5").Value & _
    " " & ActiveWorkbook.ActiveSheet.Range("D7").Value)
    If sFilename = "" Then
    MsgBox "No filename in cell D5, operation cancelled"
    Exit Sub
    End If
    ActiveWorkbook.SaveAs sPath & sFilename

    End Sub
    </pre>

    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
  •