Custom save as pathname and filename
This may be a little late for your purposes, but this worked for me.
Sub ResAlertForm_SaveAs()
Application.ScreenUpdating = False
On Error GoTo ErrRoutine
Dim MyPath As String
Dim MyDirName As String
Dim SuggName As String
Dim NewDir As String
' customize this row with the path you want to scan
MyPath = "Z:\Agent Forms\Reservation Alert Forms"
MyDirName = Sheets("Reservation Alert Form").Range("H8") 'name of resort
' the next intruction tries to create a new directory.
' If a directory by the specified name already exists, it
' returns an error, number 75. This error is managed by
' the ErrRoutine block.
MkDir (MyPath & "\" & MyDirName)
NewDir = MyPath & "\" & MyDirName
' creates the file name (dd_mm_yyyy_xxxxxxRCNA.xls)
SuggName = Sheets("Reservation Alert Form").Range("D13") _
& ("_") & Sheets("Reservation Alert Form").Range("F13") _
& ("_") & Sheets("Reservation Alert Form").Range("H13") _
& ("_") & Sheets("Reservation Alert Form").Range("D21") _
& ".XLS"
'Changes Current Directory
ChDrive NewDir
ChDir NewDir
' Saves the copy of the form to the
ActiveWorkbook.SaveAs (NewDir & "\" & SuggName)
ExitRoutine:
Call ResAlertForm_Email
Exit Sub
ErrRoutine:
' run-time error 75 - Path/File Access error
If Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Exit Sub
End If
End Sub
"Aaron Howe" wrote:
I have been trying to enter a code into my sheet which will save the file to
a directory on the network using a pre-defined file name. My filename is a
declared string, and is working properly. I can get as far as:
* Getting the Save As dialogue in the right folder, but with no filename
* Getting the Save As dialogue to show a name, not the right one, in the
last place I saved
* Getting the Save As dialogue to show the completely wrong name in the
right folder.
Assuming I wanted to do this using the GetSaveAsFilename option, how would I
do it? And how would it differ if I wanted an automatic save where the user
didn't have the prompt? The end result would have to be:
\\server\folder\subfolder\filenamefromstring.xls
Whichever way it was done...!
|