Run "Workbook Open" once
It appears to be working. You've helped me so many times. Thanks again Dave!
"Dave Peterson" wrote:
Maybe...
Option Explicit
Private Sub Workbook_Open()
Dim Resp As String
If ThisWorkbook.Path < "" Then
Exit Sub
End If
Resp = InputBox("Type customer's name & scenario summary:")
If Trim(Resp) = "" Then
MsgBox "Not saved -- no input!"
Exit Sub
End If
Me.Worksheets(1).Range("G1").Value = Resp
On Error Resume Next
Me.SaveAs Filename:="C:\Documents and Settings\David & Aprile" _
& "\My Documents\Custom Decorators\Installation Detail Sheets\" _
& Resp & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
If Err.Number < 0 Then
Err.Clear
MsgBox "File not saved -- error on saving"
End If
On Error GoTo 0
End Sub
Remember, the test for the path really means that you're creating a new workbook
based on a template file (*.xlt), right???
David P. wrote:
Thank you Gord. I am a beginner with code. This is what I did below and it
didn't work the way intend at this point. Let's pretend that my file is
called "Calculation Template" and is saved as a template. When I open the
template the macro runs to save it as a customer's name and in a different
directory. So you'll see below the macro I have created and I inserted your
suggestion below where I thought it should be placed. It didn't work but
stopped at the beginning of the macro without allowing me to save it under a
customer's name. Hope that makes sense. Here's what I have right now:
Private Sub Workbook_Open()
If ThisWorkbook.Path < "" Then
Exit Sub
l = InputBox("Type customer's name & scenario summary:")
Range("G1") = l
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\David &
Aprile\My Documents\Custom Decorators\Installation Detail Sheets\" &
Range("G1").Value _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
On Error GoTo 0
End If
End Sub
"Gord Dibben" wrote:
I would check to see if the workbook being opened has been saved once by
checking for a path.
If not, do the deed.
Private Sub Workbook_Open()
If ThisWorkbook.Path < "" Then
Exit Sub
'do the deed
End If
End Sub
Placed in Thisworkbook module of the Template
Gord Dibben MS Excel MVP
On Wed, 7 Apr 2010 15:36:11 -0700 (PDT), Pete_UK
wrote:
If, as part of your routine, you were to write the filename to a
specific cell in your workbook before saving, then you can test that
cell to see if it is empty - if so then run the rest of your macro, if
it is not empty then terminate the macro without asking for the
filename etc.
Hope this helps.
Pete
On Apr 7, 11:27 pm, David P. <David
wrote:
I have a template that when it opens it automatically runs a macro that
allows me to type in a text box what I want to save the file as and then
saves it in the directory where I want it saved. The problem is that once it
is saved I don't want it to run the macro again when I reopen that saved
file. Should I put in the code at the end some way to deactivate the macro so
that it no longer automatically runs when opening that newly saved file?
Thank you.
David P.
.
--
Dave Peterson
.
|