Save a file (2000)

[ramrod]

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>

[pieterse]

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

[ramrod]

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

[HansV]

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

[ramrod]

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

[HansV]

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.

[ramrod]

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.

[HansV]

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 "".

[ramrod]

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

[LegareColeman]

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>