Script for saving

[oloffcrous]

Hi All

I have a client that requests multiple quotes from various people within my organisation, posing the threat of different costs going out. We have mitigated this by developing a Quote Tool that is managed by one person and each new quote needs to be done on a fresh copy of this default template.
The continuous influx of requests has prompted me to automate even the automatic creation of a PDF file in the working directory, thank you Mr Excell for that script....

I have also found a way to automatically enter the desired file name in the File Save As promt, saving another copy and paste action, thank you Mr Excell for that script... again


My last automated functions are are a difficult one:

As part of the process, we copy a new template to the required folder. This file is named "Template Dec 2016"

Problem 1 - As part of the File Save As script, I select a cell value and it populates the file name. The location it selected defaulted to My Documents, but I can select a desired folder if I wish. I would however like it to select the folder I opened the file from.

Problem 2 - I would like to save the file, overwriting the file I opened. The file is called "Template Dec 2016", the cell value pastes "Date+Site+Person" into the file name and save it as such. Can I perhaps automatically delete the file I opened after I saved it as the new file name?

I hope this makes sense
!!!

Oloff Crous
Cape Town
South Africa

 

[portews]

Try this:

Sub sbSaveExcelDialog()
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/979601-script-saving.html[/COLOR]
[COLOR=#00ff00]'modified from http://analysistabs.com/excel-vba/save-workbook-to-specific-folder[/COLOR]
Dim IntialName As String
Dim sFileSaveName As Variant
[COLOR=#00ff00]'variable for your template name.  Include extension.[/COLOR]
TemplateFileName = "Template Dec 2016.xlsm"
[COLOR=#00ff00]'call the save dialog box with the path for the initial file and the new suggested name from range A1 (change as needed).[/COLOR]
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path & "\" & Range("A1"), fileFilter:="Excel Files (*.xlsm), *.xlsm")
[COLOR=#00ff00]
'if you didn't hit Cancel[/COLOR]
If sFileSaveName <> False Then
[COLOR=#00ff00]    'save the workbook[/COLOR]
    ActiveWorkbook.SaveAs sFileSaveName
[COLOR=#00ff00]'if you did hit cancel[/COLOR]
Else
[COLOR=#00ff00]    'quit the program[/COLOR]
    End
End If


[COLOR=#00ff00]'if there is a template in this folder[/COLOR]
If (Dir(ThisWorkbook.Path & "\" & TemplateFileName) <> "") Then
[COLOR=#00ff00]    'delete it[/COLOR]
    Kill ThisWorkbook.Path & "\" & TemplateFileName
End If
End Sub

 

[oloffcrous]

Hi

Thank you for reply. My laptop is in for some upgrade at the IT Guys over the week-end. Will try on Monday and let you know. The code does seem promising!!!!!