HOW DO I USE THE GETSAVEASFILENAME METHOD?

[Mike]

When using the InitialFilename argument, I would like to combine the contents
of several cells and a text constant to form the InitialFilename. For
example, I want the filename to be a string consisting of: "C" + "cell1" +
"cell2". If the contents of "cell1" are '50000' and "cell2" are the text
letter 'A', then the InitialFilename argument would be: C50000A.xls. What is
the code for this in Excel 2003 VBA?

Mike

 

[Dave Peterson]

Dim myFileName As Variant

With ActiveWorkbook.Worksheets("Sheet1")
myFileName = "C:\my documents\excel\c" _
& .Range("A1").Value _
& .Range("b1").Value & ".xls"
End With

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

If myFileName = False Then
'user hit cancel
Exit Sub
End If


or maybe:

With ActiveWorkbook.Worksheets("Sheet1")
myFileName = "C:\my documents\excel\c" _
& format(.Range("A1").Value, "00000") _
& .Range("b1").Value & ".xls"
End With

if you have to format that number.

 

[Mike]

Dave,

I wrote the following code. It does bring up the "Save As" dialog box and
does have the recommended file name that I wanted, however, when I hit the
"Save" button, the file does not actually get written to the hard drive. The
dialog box simply unloads. Why is the file not actually being saved? Thanks
for your help so far.


Private Sub OKButton_Click()
Dim myname As Variant
With ActiveWorkbook.Worksheets("Summary Sheet")
myname = "C" & .Range("h12").Value & .Range("k12").Value & ".xls"
End With
Ans = MsgBox("Please save your work to the P:\ Drive under the CostSell
Subdirectory with the filename: 'C[S.O. #][Revision Letter]', i.e. C50000A.",
vbOKCancel + vbExclamation, "Save Your Work")
If Ans = vbOK Then myname =
Application.GetSaveAsFilename(InitialFileName:=myname, filefilter:="Excel
Files,*.xls")
If myname = False Then
Exit Sub
End If
Unload UserForm3
End Sub

 

[Tim Williams]

GetSaveAsFilename only prompts the user for a name and path for the saved
file: it does not save the file. The next step is to save the file using
SaveAs...

Tim.

--
Tim Williams
Palo Alto, CA

Dave,

I wrote the following code. It does bring up the "Save As" dialog box and
does have the recommended file name that I wanted, however, when I hit the
"Save" button, the file does not actually get written to the hard drive. The
dialog box simply unloads. Why is the file not actually being saved? Thanks
for your help so far.


Private Sub OKButton_Click()
Dim myname As Variant
With ActiveWorkbook.Worksheets("Summary Sheet")
myname = "C" & .Range("h12").Value & .Range("k12").Value & ".xls"
End With
Ans = MsgBox("Please save your work to the P:\ Drive under the CostSell
Subdirectory with the filename: 'C[S.O. #][Revision Letter]', i.e. C50000A.",
vbOKCancel + vbExclamation, "Save Your Work")
If Ans = vbOK Then myname =
Application.GetSaveAsFilename(InitialFileName:=myname, filefilter:="Excel
Files,*.xls")
If myname = False Then
Exit Sub
End If
Unload UserForm3
End Sub

Dim myFileName As Variant

With ActiveWorkbook.Worksheets("Sheet1")
myFileName = "C:\my documents\excel\c" _
& .Range("A1").Value _
& .Range("b1").Value & ".xls"
End With

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

If myFileName = False Then
'user hit cancel
Exit Sub
End If


or maybe:

With ActiveWorkbook.Worksheets("Sheet1")
myFileName = "C:\my documents\excel\c" _
& format(.Range("A1").Value, "00000") _
& .Range("b1").Value & ".xls"
End With

if you have to format that number.