Solved: Help w/auto "Save As" macro

[sbrown]

I have developed a spreadsheet to collect data in a particular format from offices in the field. Part of the standardization is to create the file name from data entered into the spreadsheet, which I have done. Now what I would like to do is to place a button on the spreadsheet that will execute a ?File:Save As? command using the name in the cell to save the file under the new name. This will allow them to use the original as a ?template? and provide the data in a consistent form to the central office.

Anyone have any idea where I can find such an ?animal??

THANKS!

[Ken Puls]

Hi there, and welcome to VBAX!

From your Control Toolbox menu, create a new CommandButton on the sheet. Right click it and choose "View Code"

In the window that opens up, enter the following:
[vba]Private Sub CommandButton1_Click()
ThisWorkbook.SaveAs ActiveSheet.Range("A1")
End Sub[/vba]

(Change A1 to the cell you want it to be)

Close the Visual Basic Editor and save your file.

Then in cell A1 (or whatever) enter the following (update to your path):
c:\temp\myfile.xls

Click the button, and away you go. (You may need to turn off design mode to do this. It's the little triangle button on your Control Toolbox toolbar.)

HTH,

[johnske]

... This will allow them to use the original as a ?template? and provide the data in a consistent form to the central office.

Anyone have any idea where I can find such an ?animal??

THANKS!

Have you considered saving the original as an actual template? ... File > Save As... and select 'Template' then name it as 'Company Workbook' (or whatever) and click "Save". The template is then available as an option whenever you click on the "New Office Document" icon

[sbrown]

Thanks, Ken! This works great! Now, at the risk of being greedy, who would I "fix" this so the same button can be used to "re-save"? When you press it a second time it provides the usual "file exists . . . want to replace" dialog but should I click "No" or "Cancel" it 'breaks'! Is there a way of trapping this response and exiting "gently"?

johnske, yes, I briefly considered that but thought it would be easier to deploy via email this way, what do you think?

[Ken Puls]

Hi there,

[vba]Private Sub CommandButton1_Click()
On Error GoTo errHandler
ThisWorkbook.SaveAs ActiveSheet.Range("A1")
Exit Sub

errHandler:
Select Case Err.Number
Case Is = 1004
'The file already exists so exit
Exit Sub
Case Else
MsgBox "An unexpected error has occured"
End Select
End Sub[/vba]

I actually agree with John, too. A template is the ideal situation. As far as installation, you just need to tell the users where to put it and you're done. I assume that you want the user to save it under your filename after you're done, though, so you still need this code if I read it correctly.

HTH,

[johnske]

Well maybe you could have the best of both worlds by incorporating something like this into what you have
[VBA]
Option Explicit

Sub SaveAsTemplate()
' 'insert your own parameters below
ActiveWorkbook.SaveAs _
Filename:=Application.TemplatesPath & "testbook.xlt", _
FileFormat:=xlTemplate, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
[/VBA]

[Ken Puls]

Hey John,

I think we're looking at this at different angles. My interpretation was that the initial file would be created and distributed as a template, with the code I provided above to save under the name as determined by sbrown's convention. (Whatever that may be.)

Being based off the template ensures that the data is in the format they need it in, and the code ensures that the file name is compliant with what they need. (eg yyyy-mm-dd data.xls, or whatever)

Of course, I could be totally off base too.

[johnske]

Hey John,

I think we're looking at this at different angles. My interpretation was that the initial file would be created and distributed as a template, with the code I provided above to save under the name as determined by sbrown's convention. (Whatever that may be.)

Being based off the template ensures that the data is in the format they need it in, and the code ensures that the file name is compliant with what they need. (eg yyyy-mm-dd data.xls, or whatever)

Of course, I could be totally off base too.

Hi Ken,

Not at all, a question was brought up regarding 'deploying (a template) via email'. The workbook (with your other code in it) could be emailed with a Workbook_Open event to install the workbook as a template if it doesn't exist.

The code to do the installation can then be removed, leaving the template workbook in the "New Office Document" dialog box so that if the other 'template' is accidentally deleted there is always a copy of the template somewhere on the PC, as in this example

[vba]
Option Explicit

Private Sub Workbook_Open()
If Dir(Application.TemplatesPath & "TestBook.xlt") = Empty Then
' 'insert your own parameters below
ActiveWorkbook.SaveAs _
Filename:=Application.TemplatesPath & "TestBook.xlt", _
FileFormat:=xlTemplate, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
RemoveThisWorkbookCode
End Sub


Private Sub RemoveThisWorkbookCode()
Dim N As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
'//.CountOfLines is ALL lines - change to suit\\
For N = 1 To .CountOfLines - 1
.DeleteLines 2
Next
End With
End Sub
[/vba]

[sbrown]

Boy, the two of you think on a much higher plane than I do but you have definitely given me some great things to think about! I thank you both for your input and suggestions, it is greatly appreciated!

This spreadsheet was supposed to be a “stop gap” measure while a new system is being developed and I’ve probably already put more time and effort into it than is warranted but I’ve learned a lot! The purpose of the spreadsheet is to collect data in an organized fashion from various offices around the state, then using Access, consolidate the information into something useful! We wanted to standardize and facilitate the collection of the data, thus the spreadsheet. We are using email to distribute the form to clerks who have little or no training (we even discovered some of the offices didn’t have Excel, so much for standardization!). Anyway, thanks again!

I’m new to this but if I can figure out how, I’ll mark this as solved. Thanks again for your interest and comments!

Steve

[Ken Puls]

This spreadsheet was supposed to be a ?stop gap? measure while a new system is being developed {snip}

They all start that way.

John, I see what you mean. I never thought about deploying it via code. I've never actually used the code removal method on a workbook yet, but I remember you'd made a couple of KB articles on it. Clever!

[lucas]

I use one or the other of Johns methods to remove code and formula's quite a bit. Lots of people I deal with do not allow macros in files sent to them so they have to go.

I incorporate the delete routine into the end of a process on one spreadsheet. It saves a copy of itself on open then you run code to browse and consolidate used sheets from all excel files in a folder then it sorts the sheets. At the end of the sort routine I call the delete code and formula routine and it deletes it all and removes the formulas leaving values only, plus it deletes sheet1 which has the button to start the process. Result is a ready to email spreadsheet.