Thanks Paul, on my initial attempts I did not get it working but going to try to figure it out and get back to you.
Thanks for your help
Regards,
Steven
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Paul Schreiner
Sent: 05 November 2012 2:01 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] If Filename conains the ProjectName then run code
It sounds like you have the user start out by opening the template.
Then, when the user saves the file, you want the file saved as: ProjectName &
"_Estimate"
Once it has been saved with the ProjectName, then subsequent "saves" would
proceed normally.
Given what you already have, I would move the macro to a BeforeSave Event macro.
then use:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
WbP = ActiveWorkbook.Path
ProjectName = sheets(1).Cells(5, 6) '(include sheet name, to ensure proper
sheet is used)
If (InStr(1, ActiveWorkbook.Name, ProjectName) <= 0) Then
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
WbP = .SelectedItems(1)
End If
End With
If (Right(WbP, 1) <> "\") Then WbP = WbP & "\"
Application.Enableevents = false
'This will ensure that the "BeforeSave" event macro isn't called repeatedly.
'----------------------------------------------------------
ActiveWorkbook.SaveAs filename:=WbP & ProjectName & "_Estimate",
FileFormat:=52
'----------------------------------------------------------
Application.Enableevents = true
Cancel = true 'This keeps the file from being saved twice
End If
End Sub
Things to note here are:
Since this macro is triggered when the user hits "Save" or "SaveAs",
then if the Project Name is ADDED, then the macro does a "SaveAs" with the new
name,
FORCING the file format to .xlsm. (format 52)
Once the macro runs, then the "Save" that the user issued would normally run,
causing the file to be saved twice.
To keep this from happening, use "Cancel = true" to "cancel" the user-issued
Save.
Since this is an event macro, the Application.SaveAs function will actually
cause the
BeforeSave Event to be triggered.
To keep this from happening, use Application.EnableEvents = false
(be sure to turn it back on)
Note: If the user decides to save the TEMPLATE as a different format other than
.xlsm,
then this file will override the users choice.
Say the user opens the template and enters a ProjectName.
He then decides to save it as an Excel2003 format by using SaveAs.
This event macro will execute,
notice that the template name does not contain the Project Name,
save the file with the Project Name as a .xlsm file,
then cancel the user's request to save as Excel2003.
Now, once this file is saved with the Project Name,
THEN when the user issues a SaveAs, since the file contains the project name,
the user's request will proceed as.. er.. requested.
I just wanted to let you know that this is EXPECTED, not a "bug"..
If it's an issue, I'm sure we could figure out an alternative method.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: steven.eckley <steven.eckley@accenture.com<mailto:steven.eckley%40accenture.com>>
To: ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>
Sent: Sun, November 4, 2012 7:29:35 AM
Subject: [ExcelVBA] If Filename conains the ProjectName then run code
I have created a excel template which has a button that i click to save
automatically safe the file with a specific file name to the active directory.
Before the file is saved I'd like to check if the filename contains the
ProjectName, if it does it should continue to save the file, if not I'd like to
be prompted where to save the file to. If current save as macro is below. Any
assistance will be much appreciated.
Sub SaveAS()
' SaveAS Macro
'
Dim ProjectName As String
WbP = ActiveWorkbook.Path
ProjectName = Cells(5, 6)
'
ActiveWorkbook.SaveAS Filename:=ProjectName & "_Estimate", FileFormat:=52
End Sub
[Non-text portions of this message have been removed]
________________________________
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited.
Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.
__________________________________________________________
www.accenture.com
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar