| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS MACRO
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Did you try the below options (from your previous post).
http://www.microsoft.com/communities...1-5a1df54a136c If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
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? "Joel" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
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 -- If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: 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? "Joel" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
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. "Jacob Skaria" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: 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? "Joel" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
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. "Neil Holden" wrote: 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. "Jacob Skaria" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: 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? "Joel" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to Save without the Save Message | Excel Discussion (Misc queries) | |||
| "Save" macro problem, still prompted to save when closing workbook (?) | Excel Programming | |||
| Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming | |||
| ASP: Open Excel File with Macro, Allow Macro to run, and then save | Excel Programming | |||
| Prompted to save changes after macro save - why? | Excel Programming | |||
Hybrid Mode
