PDA

View Full Version : Solved: How do I "save as" with a predefined filename?



tadgill
09-05-2005, 12:32 PM
How do I "save as" with a predefined filename? I want to display a "save as" Dialog with a filename that comes from a cell in one of the sheets.

Thanks for any help here! http://vbaexpress.com/forum/images/smilies/banghead.gif

JonPeltier
09-05-2005, 12:36 PM
Look up GetSaveAsFilename in the help files. You can change the directory and propose a default name, then let the user browse and rename as desired.

tadgill
09-05-2005, 12:48 PM
Call me lame, but I can't seem to find anything about that in the VB help files...

mdmackillop
09-05-2005, 01:27 PM
Hi Tadgill,
Welcome to VBAX.
Try
Sub DoSave()
Application.Dialogs(xlDialogSaveAs).Show "C:\AAA\" & Sheets(1).Range("A1")
End Sub

MOS MASTER
09-05-2005, 01:34 PM
Call me lame, but I can't seem to find anything about that in the VB help files...

Welcome to VBAX! :hi:

Paste Jon's tip in the VBE code pane. Tip was: "GetSaveAsFilename"
Now put your cursor in it and press F1. You'll get all the info you need on this function.

This is what you are looking for:
Sub SaveMyWorkbook()
Dim vFile As Variant
vFile = Application.GetSaveAsFilename(InitialFileName:="My given file name", _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")
If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If

End Sub


HTH, :whistle:

MOS MASTER
09-05-2005, 01:39 PM
O I now notice you want to use a name from a cell value.

Change to:
Sub SaveMyWorkbook()
Dim vFile As Variant
Dim sName As Variant
sName = [A1] 'value from A1

vFile = Application.GetSaveAsFilename(InitialFileName:=sName, _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")
If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If

End Sub


HTH, :whistle:

tadgill
09-05-2005, 01:50 PM
Sweeet, you guys are great! :clap:

MOS MASTER
09-05-2005, 01:55 PM
Glad we could help! :beerchug:

mdmackillop
09-05-2005, 01:56 PM
Hi Tadgill,
If this is solved, please select "Mark thread solved" from the Thead Tools drop down.
Regards
MD

alexanderd
08-23-2006, 02:19 PM
i know that this is an old thread and has got me nearly all the way to resolving asticking point. in my current project.
if it is possible i would like to be able to save the file directly to a given directory i.e.
"C:\Documents and Settings\Jones\Desktop\ASDF\" how can this be achieved


Welcome to VBAX! :hi:

Paste Jon's tip in the VBE code pane. Tip was: "GetSaveAsFilename"
Now put your cursor in it and press F1. You'll get all the info you need on this function.

This is what you are looking for:
Sub SaveMyWorkbook()
Dim vFile As Variant
vFile = Application.GetSaveAsFilename(InitialFileName:="My given file name", _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")
If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If

End Sub


HTH, :whistle:

JonPeltier
08-23-2006, 02:27 PM
O I now notice you want to use a name from a cell value.

Change to:
sName = [A1] 'value from A1


Don't be lazy. You should clearly reference every object, and avoid relying on default properties.

sName = ActiveSheet.Range("A1").Value

JonPeltier
08-23-2006, 02:31 PM
i know that this is an old thread and has got me nearly all the way to resolving asticking point. in my current project.
if it is possible i would like to be able to save the file directly to a given directory i.e.
"C:\Documents and Settings\Jones\Desktop\ASDF\" how can this be achieved




Sub SaveMyWorkbook()
Dim vFile As Variant
Dim sCurrDir As String


' save current directory, and change to desired starting directory
sCurrDir = CurDir
ChDrive "C:\Documents and Settings\Jones\Desktop\ASDF\"
ChDir "C:\Documents and Settings\Jones\Desktop\ASDF\"


vFile = Application.GetSaveAsFilename(InitialFileName:="My given file name", _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")

If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If


' change back to initial current directory
ChDrive sCurrDir
ChDir sCurrDir


End Sub


How come the VBA tags don't honor spaces (indents)?

mdmackillop
08-23-2006, 02:38 PM
Hi Jon
I notice ChDrive and ChDir both refer to the same path. Is that correct and/or necessary?
Regards
MD

JonPeltier
08-23-2006, 02:40 PM
It's not really necessary, but it does work that way. Normally I wouldn't hard code the path in both lines, but instead use a constant or variable (like I did with sCurrDir at the end).

alexanderd
08-23-2006, 03:17 PM
thank you for a quick response, i am looking forward to trying it out tommorow (thursday).
it looks great.

mdmackillop
08-23-2006, 03:18 PM
Thanks Jon.

alexanderd
08-24-2006, 11:01 AM
Sub SaveMyWorkbook()
Dim vFile As Variant
Dim sCurrDir As String


' save current directory, and change to desired starting directory
sCurrDir = CurDir
ChDrive "C:\Documents and Settings\Jones\Desktop\ASDF\"
ChDir "C:\Documents and Settings\Jones\Desktop\ASDF\"


vFile = Application.GetSaveAsFilename(InitialFileName:="My given file name", _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")


If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If


' change back to initial current directory
ChDrive sCurrDir
ChDir sCurrDir


End Sub


How come the VBA tags don't honor spaces (indents)?

thank you for all your help with this .
i have alered it slightly to suit my needs, it might be of help to other so here it is

Sub SaveMyWorkbook()
Dim vFile As Variant
Dim sCurrDir As String
Dim sName As Variant
Dim strdate As String
sName = [A10] 'value from A1
strdate = Format(Now, "dd-mm-yy h-mm-ss")
' save current directory, and change to desired starting directory
sCurrDir = CurDir
ChDrive "J:\sales\ProFormas"
ChDir "J:\sales\ProFormas"

vFile = Application.GetSaveAsFilename(InitialFileName:=sName & strdate, _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="My custom save dialog")
If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If
' change back to initial current directory
ChDrive sCurrDir
ChDir sCurrDir

End Sub

JonPeltier
08-24-2006, 12:02 PM
Don't forget this advice from earlier in the thread:

http://www.vbaexpress.com/forum/showpost.php?p=72624&postcount=11