| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE AS MACRO
Below is the code that brings up the SAVE as prompt, and save to a default
location, I thought i had the correct code to automatically insert a cell reference and the current date in the file name. Can you assist please? Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long Dim Response As String Dim msg As String Dim Style As String Dim sFilename As String Dim ans 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("C1") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" DefaultFilename = DefaultFilename & ".xls" End If 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 End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
hi. this is another branch - you should stay with the original thread if
possible. try this: Dim flName As String Dim flFormat As Long Dim msg As String Dim Style As String Dim sFilename As String msg = "Are you sure you want to save the Smith quote?" Style = vbYesNo + vbInformation + vbDefaultButton2 If MsgBox(msg, Style) = vbYes Then flFormat = ActiveWorkbook.FileFormat DefaultFolder = "M:\Design\" ChDrive "M" ChDir DefaultFolder DefaultFilename = Range("C1") 'what this for idf you're asking the user to select? If Right(UCase(DefaultFilename), 3) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" End If flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") If flToSave = False Then Exit Sub End If ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat End If "Neil Holden" wrote: Below is the code that brings up the SAVE as prompt, and save to a default location, I thought i had the correct code to automatically insert a cell reference and the current date in the file name. Can you assist please? Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long Dim Response As String Dim msg As String Dim Style As String Dim sFilename As String Dim ans 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("C1") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" DefaultFilename = DefaultFilename & ".xls" End If 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 End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Hello again, same problem, nothing happens : (
Thanks for your help. "Neil Holden" wrote: Below is the code that brings up the SAVE as prompt, and save to a default location, I thought i had the correct code to automatically insert a cell reference and the current date in the file name. Can you assist please? Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long Dim Response As String Dim msg As String Dim Style As String Dim sFilename As String Dim ans 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("C1") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" DefaultFilename = DefaultFilename & ".xls" End If 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 End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
SOMETHING must happen. when you F8 through the code, does it get as far as
the filesave? at what point does it exit the sub. If it gets past teh filesave without error, then it saved the file somewhere, so what is the address? you can use ?activeworkbook.fullpath in the Immediate window. "Neil Holden" wrote: Hello again, same problem, nothing happens : ( Thanks for your help. "Neil Holden" wrote: Below is the code that brings up the SAVE as prompt, and save to a default location, I thought i had the correct code to automatically insert a cell reference and the current date in the file name. Can you assist please? Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long Dim Response As String Dim msg As String Dim Style As String Dim sFilename As String Dim ans 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("C1") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" DefaultFilename = DefaultFilename & ".xls" End If 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 End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SAVE AS MACRO
Sorry something does happen, a message prompt appears and then the save as
dailog appears but the what ever is in C1 doesnt appear in the file name, also the current date doesn't appear. "Patrick Molloy" wrote: SOMETHING must happen. when you F8 through the code, does it get as far as the filesave? at what point does it exit the sub. If it gets past teh filesave without error, then it saved the file somewhere, so what is the address? you can use ?activeworkbook.fullpath in the Immediate window. "Neil Holden" wrote: Hello again, same problem, nothing happens : ( Thanks for your help. "Neil Holden" wrote: Below is the code that brings up the SAVE as prompt, and save to a default location, I thought i had the correct code to automatically insert a cell reference and the current date in the file name. Can you assist please? Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long Dim Response As String Dim msg As String Dim Style As String Dim sFilename As String Dim ans 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("C1") If Right(UCase(DefaultFilename), 2) < "XLS" Then DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls" DefaultFilename = DefaultFilename & ".xls" End If 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 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
