File Save As

[austenr]

Is there a way in EXCEL97 to pull the value of a certain cell for example A1 and use it as the file name at the time of SaveAS? Thanks

[Ken Puls]

Hi Austen,

I've used the following where B6 holds the file path (C:\My Documents\), and B13 holds my file name. (Be aware of the final \ in the path part.


ActiveWorkbook.SaveAs Filename:=Worksheets("SaveInfo").Range("B6").value & Worksheets("SaveInfo").Range("B13")

HTH,

[Zack Barresse]

Hi Austen,

Not sure, but maybe you can try ...


ThisWorkbook.SaveAs Range("A1").Value & ".xls"

[austenr]

Thanks to you both!!

[austenr]

Hey firefytr that works great!! One last question I want to put a MsgBox that says "Saving file ("range name") then unload the MsgBox, where "range name" is the cell where I got the file name from. Tried a couple of ways and can't seem to get it to work. Thanks again!!!

[Ken Puls]

Hi Austen,

Does it have to be a MsgBox? I'm sure you could probably dismiss it using and "OnTime" event although I've never tried myself..

Another alternative would be to try something like the following:


'Set the StatusBar
Application.StatusBar = "Saving file as " & Range("A1").value & ".xls... Please wait"
'Save the file
ThisWorkbook.SaveAs Range("A1").value & ".xls"
'Clear the StatusBar
Application.StatusBar = False


It's not quite as obvious (which is definately a drawback,) but it is much less work to code. The effect is that it writes it in the bottom left corner of Excel's screen (where it usually says "Ready").

[Jacob Hilderbrand]

Try this macro:


Option Explicit

Sub SaveAS()
Dim Prompt As String
Dim Title As String
Dim MyResponse As VbMsgBoxResult
Dim Path As String
Dim FName As String
Path = ThisWorkbook.Path
FName = Sheets("Sheet1").Range("A1").Text
Prompt = "This workbook will now be save as: " & Path & "\" & FName
Title = "Confirm Procedure"
MyResponse = MsgBox(Prompt, vbYesNo, Title)
If MyResponse = vbYes Then
ThisWorkbook.SaveAS Filename:=Path & "\" & FName
End If
End Sub

[austenr]

thanks everyone