Assign a file a name when saved

I have the following module to save a project when the user exits the workbook:
Sub ExitExcel()
'******* New Project Start *******
'ActiveWorkbook.Close
If ThisWorkbook.Name = "Consolidated.xlsm" Then
    If Application.ActiveWorkbook.Saved = False Then
        If Trim(Sheets("Consolidated").Range("R2").Value) = "" Then
            If MsgBox("You have changed the 'Consolidated' template and you have not assigned a Project Name." & vbCrLf & vbCrLf _
                        & "Click 'Yes' to assign a Project Name." & vbCrLf _
                        & "Click 'No' to close the workbook without saving the changes.", vbCritical + vbYesNo, "Template Changed") _
                    = vbYes Then
                NewProject
                Exit Sub
            Else
                Application.ActiveWorkbook.Saved = True
                ActiveWorkbook.Close SaveChanges:=False
                Exit Sub
            End If
        End If
    End If
End If
ActiveWorkbook.Close
'******* New Project End *********
End Sub

Open in new window

What I would like to be able to do is when the user exits using the exit button is to save this as “Consolidated ” & Range(“R2”) where Range (“R2”) is the name of the project. If there is a workbook  already named as “Consolidated “ & Range(“R2”) then the user has the option to replace (overwrite) old file with new.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The code in the workbook already does that. so I'm confused.
0
Frank FreeseAuthor Commented:
My fault - when it is saved I have no idea where it has been saved to? I've looked everywhere (using your Consolidate-V-3 file)
0
Martin LissOlder than dirtCommented:
Don't you get a Save As dialog that looks like this?
Save AsIn this case the original file is stored in my 'Solutions' folder so that is where the saved file is stored unless a different folder is selected in the dialog.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Frank FreeseAuthor Commented:
Sure didn't. The original file was over written - acting like a Save rather than a Save As
0
Martin LissOlder than dirtCommented:
Please attach your workbook.
0
Frank FreeseAuthor Commented:
I was thinking something like this replacing line 21?

Me.Application.Workbooks("Consolidated “ & Range(“R2”).xlsm").Close
0
Martin LissOlder than dirtCommented:
Maybe but please attach your workbook.
0
Frank FreeseAuthor Commented:
I'm using your file "Consolidated-V3" attached
Consolidated-V3.xlsm
0
Martin LissOlder than dirtCommented:
Rename the file to "Consolidated.xlsm" as I suggested in our last thread and it will behave differently.
0
Frank FreeseAuthor Commented:
Where're not on the same page yet. When I open "Consolidated-V-3", assign it a project name, click on the Exit button I have no opportunity to rename the file. "Consolidated-V-3" is simply updated.
0
Martin LissOlder than dirtCommented:
Yes, when it is named "Consolidated-V-3" it will do that. If you go to Windows explorer and change that name to "Consolidated.xlsm" it will behave differently.
0
Frank FreeseAuthor Commented:
I changed the name from "Consolidate-V-3.xlsm" to "Consolidated.xlsm". Opened the new "Consolidated.xlsm" file. I then assigned a Project Name of "Memphis". Clicked on the "Exit" command button. A dialog box appeared "Save, Don't Save, Cancel" When I select "Save" the original file "Consolidated" was saved with a Project Name of "Memphis. I never had the opportunity to rename the file or assign it to a folder. I was looking to have the new file named "Consolidated Memphis", rather than "Consolidated". The attached file is what I got.
Consolidated.xlsm
0
Frank FreeseAuthor Commented:
Here's what I have just discovered. If I have not assigned a Project Name I'm ask to assign one - everything looks to be ok. However, if I have assigned a Project Name and then save is where the problem shows.
0
Martin LissOlder than dirtCommented:
In the V3 workbook I put in a bunch of code the only worked if the name of the workbook was exactly "Consolidated.xlsm" because that was the name of the "template", and as a template I didn't allow it to be changed if the Project Name was blank.  Should I assume you don't care about that any more?
0
Frank FreeseAuthor Commented:
If the Project Name is blank and they want to Exit the workbook a Project Name must be provided or nothing is saved. If they only provide a Project Name and they want to Exit the workbook the file is saved as "Consolidated 'Project Name'.xlsm" in the folder of their choosing. Did I answer your question? I believe everything for "New Project" works OK.
0
Martin LissOlder than dirtCommented:
Sorry, no, you didn't answer my question. I was looking for a "yes" or a "no" to
In the V3 workbook I put in a bunch of code [that] only worked if the name of the workbook was exactly "Consolidated.xlsm" ....  Should I assume you don't care about that any more?
0
Frank FreeseAuthor Commented:
Yes
0
Frank FreeseAuthor Commented:
Between line 17 and 18 in above code ID: 28490056 I call modNewProject and I THINK my problem has is solved. But I'd appreciate a second opinion. What happens now is if there is a Project Name and the user Exits the File will be labeled Consolidate xxxxx. I'm going to test this some more.
0
Frank FreeseAuthor Commented:
One small problem left - The Grand Totals work but only after the workbook has been saved and reopened. Can the worksheet "Consolidatedxxxx" recalculate for Grand Totals? I'm also looking to see if that's possible.
0
Frank FreeseAuthor Commented:
Maybe add a macroRefreshData?
0
Frank FreeseAuthor Commented:
I got refresh to work!
Are you working on any of this now?
0
Martin LissOlder than dirtCommented:
Yes. Please explain what code you added, if any, to fix the grand total problem.
0
Martin LissOlder than dirtCommented:
Attached is a workbook that I believe fixes the problem you described in your first post. I also changed the internal name of the project from the default "VBAProject" to "Consolidated".

Until now this project was difficult to deal with because the special code in Workbook_BeforeClose and/or Workbook_BeforeSave would be triggered if for example I simply wanted to make a code change and then save the workbook. I've added a couple of lines of code to those subs to avoid that and you should make a note that if you or someone else wants to change the code he should go to the Immediate Window and type Application.ActiveWorkbook.Saved = True before saving or exiting.
Consolidated.xlsm
0
Frank FreeseAuthor Commented:
Fixing the Grand Total problem:
I added a form command button and assigned a macro to it called RefreshData:

Public Sub RefreshData()
      Application.Calculate
End Sub

And it worked! Imagine that...
I'm now looking at the rest of your submitted w/b...back shortly
0
Frank FreeseAuthor Commented:
I saw where you updated Grand Totals - thanks
However, the attached file is your Consolidated, after I added a project with some data. The name of the file is "Consolidated" not "Consolidated Tennessee". The next time I load "Consolidated" I get Tennessee with data.
I've NOT changed any of your code, but wanted to look at the macro named "oops" - it explained what happened to 2014 formulas :). I promise you I thought I'd screwed up, again.
Consolidated.xlsm
0
Martin LissOlder than dirtCommented:
Please describe in bulleted steps, what you did to arrive at that result.
0
Frank FreeseAuthor Commented:
1. Opened the file "Consolidated.xlsm"
2. Click on "Project Name" and assigned a name
4. Clicked "OK"
3. Clicked on "Exit"
4. The program then sent me to the Folder Name that "Consolidated.xlsm" was launched
5. When I clicked "OK" I got this message:
Path does not exist6. Click on "OK"
7. Located the folder I wanted to save the file
New path
I was expecting after Step 4 to have the file saved as "Consolidated Arkansas" and be done.
7. Click "OK"
8. This dialog box appeared
Save Dialog9. Selected "Save"
0
Martin LissOlder than dirtCommented:
Try a different folder than "Project Cost allocation\Temp\Temp\Temp\Temp (which is very unusual). In any case try this version.
Consolidated.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Well, my good man, it looks good - first thing tomorrow morning I'll complete my tests -
It looks really good.
Thanks a million - I do appreciate it. Maybe it was a little challenging.
Again, thank you and I'll close this thread and award you well earned points
0
Frank FreeseAuthor Commented:
Just an awesome job!
Thank you sir!
0
Martin LissOlder than dirtCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.