| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS MACRO
Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Have you missed to copy the last few lines from Joel's code ?
Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
That is in my code, not sure what else it could be.
Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Please note the difference between the below two
ActiveWorkbook.SaveAs will save the workbook which is active ThisWorkbook.SaveAs will save the workbook in which the macro is If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
change
Dim Response As String to Dim Response As Long because vbYes is a LONG not text "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
I have tried that but still unsuccessful.
"Patrick Molloy" wrote: change Dim Response As String to Dim Response As Long because vbYes is a LONG not text "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
have you used F8 to step through the code?
"Neil Holden" wrote: I have tried that but still unsuccessful. "Patrick Molloy" wrote: change Dim Response As String to Dim Response As Long because vbYes is a LONG not text "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Yes I have, no errors were found : (
"Patrick Molloy" wrote: have you used F8 to step through the code? "Neil Holden" wrote: I have tried that but still unsuccessful. "Patrick Molloy" wrote: change Dim Response As String to Dim Response As Long because vbYes is a LONG not text "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
after thjis line...
.Show you need to actually save the file. All you did to this line was select a name and a where, you still need to execute the save. Why not use the Application method? Sub saveFile() Dim fn As String fn = Application.GetSaveAsFilename() If fn Then ActiveWorkbook.SaveAs fn End If End Sub "Neil Holden" wrote: Yes I have, no errors were found : ( "Patrick Molloy" wrote: have you used F8 to step through the code? "Neil Holden" wrote: I have tried that but still unsuccessful. "Patrick Molloy" wrote: change Dim Response As String to Dim Response As Long because vbYes is a LONG not text "Neil Holden" wrote: That is in my code, not sure what else it could be. Neil. "Jacob Skaria" wrote: Have you missed to copy the last few lines from Joel's code ? Try with this line... which save the active workbook as the filename in the variable ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Please see below, this is a button for when pressed a save as dialog appears with the name of whatever appears in C7 and the current date, but when i press ok to save nothing happens!! Please help i'm so close to finishing. 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 save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design" If Right(DefaultFolder, 1) < "\" Then DefaultFolder = DefaultFolder & "\" End If DefaultFilename = Range("C7") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" '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 |
| 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 | |||
Linear Mode
