SaveAs Question

Leeray

Board Regular
Joined
Apr 2, 2009
Messages
64
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 :biggrin:.

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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

Are you after something like this?

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

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
You could use the SaveAs dialog and prepopulate the name. The following example will default the name to the date formatted as DDMMYY

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

Leeray

Board Regular
Joined
Apr 2, 2009
Messages
64
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

Board Regular
Joined
Apr 2, 2009
Messages
64

ADVERTISEMENT

Any ideas anyone :confused:

Or should I give up on this one
 

Leeray

Board Regular
Joined
Apr 2, 2009
Messages
64
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top