How to make the "Save As" prompt appear with vba?

B

Bura Tino

Hi,

Is there vba for making the Save As prompt appear with a certain active
directory and a suggested file name?

Thanks,

Bura
 
F

Frank Kabel

Hi Bura
try the following
Sub foo()
Dim fname As String
Dim path As String
Dim drive As String
Dim ret_value

drive = "C:"
path = "C:\temp\"
fname = "test.xls"
ChDrive (drive)
ChDir (path)
ret_value = Application.GetSaveAsFilename(InitialFileName:=fname)
MsgBox ret_value

End Sub
 
H

Harald Staff

Hi Bura

Sub SavePrompt()
Dim S As Variant
S = Application.GetSaveAsFilename
If S <> False Then
MsgBox "He said " & S, , "hee hee"
End If
End Sub

Note that it doesn't actually DO anything.

HTH. Best wishes Harald
 
J

Jake Marx

Hi Bura,

Just to expand a bit...as Frank shows, there is no way to specify the
starting drive/path for the dialog. So you must actually change the current
directory for the user, then display the form. When you do that, it's a
good idea to put things back in place when you're done. Here's an example:

Sub ShowSaveAs()
Dim vResponse As Variant
Dim sOldPath As String

sOldPath = CurDir

ChDrive "C:"
ChDir "C:\"

vResponse = Application.GetSaveAsFilename("jake.xls", _
"Microsoft Excel Files (*.xls), *.xls")

If vResponse <> False Then
MsgBox CStr(vResponse)
End If

ChDrive sOldPath
ChDir sOldPath
End Sub

As Harald noted, this does not *do* anything per se. But it will return
either False (if the user clicked Cancel) or a String representing the full
path/filename the user selected.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
B

Bura Tino

Jake Marx said:
Hi Bura,

Just to expand a bit...as Frank shows, there is no way to specify the
starting drive/path for the dialog. So you must actually change the current
directory for the user, then display the form. When you do that, it's a
good idea to put things back in place when you're done. Here's an example:

Sub ShowSaveAs()
Dim vResponse As Variant
Dim sOldPath As String

sOldPath = CurDir

ChDrive "C:"
ChDir "C:\"

vResponse = Application.GetSaveAsFilename("jake.xls", _
"Microsoft Excel Files (*.xls), *.xls")

If vResponse <> False Then
MsgBox CStr(vResponse)
End If

ChDrive sOldPath
ChDir sOldPath
End Sub

As Harald noted, this does not *do* anything per se. But it will return
either False (if the user clicked Cancel) or a String representing the full
path/filename the user selected.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com
Click to expand...


Thank you all -- so useful!!!
[please keep replies in the newsgroup - email address unmonitored]


Bura said:
Hi,

Is there vba for making the Save As prompt appear with a certain
active directory and a suggested file name?

Thanks,

Bura
Click to expand...
Click to expand...
 
Top