SaveAs Question

[Leeray]

Attached is the following Saveas macro that I have.

Basically it does what I want. I push a button, the Save As option comes up, the Path is 99% there (Each file has 1 addtional step which must be done manually!) and I get to choose the file name.
Everything works now, no little nasty error codes Great .

However I would like to take this a step further and fill the Filename using a cell on the worksheet. I do not however want the file to automatically save as I need to go one step further in allocating the correct path (As stated earlier).

Is this possible?

I would also like to add todays date at the front of the filename.

Let me know what you think


Private Sub CommandButton2_Click()
ChDrive "S"
ChDir "S:\Activity\LONDON REGIONAL 992\ACTIVE ACCOUNTS"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> False Then
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal
ActiveWorkbook.Close

End If

End Sub

 

[Mike Blackman]

Hi,

Are you after something like this?

ActiveWorkbook.SaveAs Filename:=Range("A1") & ".xls", FileFormat:=xlNormal
ActiveWorkbook.Close False

 

[Jon von der Heyden]

You could use the SaveAs dialog and prepopulate the name. The following example will default the name to the date formatted as DDMMYY

Application.Dialogs(xlDialogSaveAs).Show (Format(Date, "DDMMYY"))

 

[Leeray]

I have now figured out the date part in excel but thank-you Jon Von der Heyden.

My code now reads

Private Sub CommandButton2_Click()
ChDrive "S"
ChDir "S:\Activity\LONDON REGIONAL 992\ACTIVE ACCOUNTS"
'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=Range("E85") & ".xls", FileFormat:=xlNormal
ActiveWorkbook.Close False

End If

End Sub


It's odd as that when I hit the command button the filename appears as the original name of the file but when I click save it saves as the name in the cell (which I want!). Is there any way to display the cell name in the display saveas option?

thanks in advance

 

[Leeray]

Any ideas anyone

Or should I give up on this one

 

[Leeray]

Nobody??

Is this acheivable or am I asking for too much

 

[Leeray]

OK looks like I've cracked it.
I've had a dabble with a different code and it seems to do what I want without any errors.

Code is:

Private Sub CommandButton2_Click()
ChDrive "S"
ChDir "S:\Activity\LONDON REGIONAL 992\ACTIVE ACCOUNTS"

With Sheet1
sFileName = .Range("E85").Text
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")
If sFileName <> "False" Then
On Error Resume Next
Application.EnableEvents = False
ThisWorkbook.SaveAs sPath & sFileName
Application.EnableEvents = True
End If

End With
ActiveWorkbook.Close False
End Sub


I push a button, it opens up the saveas menu, the saveas path is where I want it to be, the filename is populated from a cell, I click save and hey presto it saves. There are no errors what so ever. Oh and the file then closes as I wanted.

Many thanks for your help