HOW DO I USE THE GETSAVEASFILENAME METHOD?

M

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
 
D

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

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
 
T

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


Mike said:
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

Dave Peterson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top