SAVE AS MACRO

[Neil Holden]

Below I have code to save as when a macro is pressed.

Can someone tell me firstly, to automatically enter a file name with
reference to whatever is in C4 and to include the date, also to save in a
default location.

Private Sub CommandButton1_Click()

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat
flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel
Files (*.xls),*.xls", _
Title:="Save File As...")


If flToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

 

[Jacob Skaria]

Did you try the below options (from your previous post).

http://www.microsoft.com/communitie...136c&mid=9a4c55ea-4287-4dae-a5f1-5a1df54a136c

If this post helps click Yes

 

[Joel]

You can use a file dialog. Change the defaultfolder name as required


Private Sub CommandButton1_Click()

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "c:\temp"
If Right(DefaultFolder, 1) <> "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C4")
If Right(UCase(DefaultFilename), 2) <> "XLS" Then
DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

flToSave = .SelectedItems.Item(1)

End With

ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

End If
End Sub

 

[Neil Holden]

Superb!! Thanks. One small change if you could be so kind, how to i get it
to display the file name and the current date?

 

[Jacob Skaria]

Do you mean to add current date to the filename

DefaultFilename = DefaultFilename & Format(Date,"ddmmyyyy") & ".xls"

OR do you mean to display the file name and current date after save.

ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat
Msgbox "FileName: " & flToSave & vbcrlf & "Current Date :" & Date

 

[Neil Holden]

Thanks for that Jacob, I am now having another problem.

When the save as dialog appears and I press save, its not saving its like
the save button isnt working.

 

[Joel]

The dialog box doesn't save it only selects the filename. the SAveas
statement does the savings. I suspect the file is getting saved in a
different format than you are expecting. Open up a window explorer and sort
the files by date to see if anything is being saved.