excel file saves

  • Thread starter Cell reference as the file name field
  • Start date
C

Cell reference as the file name field

I've created a fill and print form in Excel. The form contains a name field
that we would like to use as the file name. Basically when we select save as
it would go to the name box field and autofill the name on the save line.

Example;

P:/Work/Equipment/Jim Jones.xls
P:/Work/Equipment/Robert Brown.xls
 
P

Peter Ellis

You might want to try creating a macro called something like SpecialSave that
contains the following code line

ThisWorkbook.SaveAs ("a1")

where A1 contains the text you want to use as the file name. Needless to
say, you will need to use the same cell for the file name, unless you want to
use more code for the macro.

You could also then add the macro to a custom menu for easier access.
 
D

Dave Peterson

I think you have a couple of choices.

You could just show the File|SaveAs dialog and let the user do their own save:

Option Explicit
Sub specialsave()

Dim myStr As String
myStr = Worksheets("sheet1").Range("a1").Value
'or
myStr = "P:/Work/Equipment/Jim Jones.xls"
Application.Dialogs(xlDialogSaveAs).Show arg1:=myStr

End Sub

Or you could ask the user for the name and then have the code save the workbook.


Option Explicit
Sub specialsave2()

Dim myFileName As Variant
Dim myStr As String
myStr = Worksheets("sheet1").Range("a1").Value
'or
myStr = "P:/Work/Equipment/Jim Jones.xls"

myFileName = Application.GetSaveAsFilename(InitialFileName:=myStr, _
filefilter:="Excel Files, *.xls")

If myFileName = False Then
Exit Sub
End If

ActiveWorkbook.SaveAs Filename:=myFileName

End Sub
 
Top