Assign a file a name when saved

Frank Freese
Frank Freese used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The code in the workbook already does that. so I'm confused.

Author

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)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
Sure didn't. The original file was over written - acting like a Save rather than a Save As
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please attach your workbook.

Author

Commented:
I was thinking something like this replacing line 21?

Me.Application.Workbooks("Consolidated “ & Range(“R2”).xlsm").Close
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Maybe but please attach your workbook.

Author

Commented:
I'm using your file "Consolidated-V3" attached
Consolidated-V3.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Rename the file to "Consolidated.xlsm" as I suggested in our last thread and it will behave differently.

Author

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.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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

Author

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.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

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.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:
Yes

Author

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.

Author

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.

Author

Commented:
Maybe add a macroRefreshData?

Author

Commented:
I got refresh to work!
Are you working on any of this now?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes. Please explain what code you added, if any, to fix the grand total problem.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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

Author

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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please describe in bulleted steps, what you did to arrive at that result.

Author

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"
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try a different folder than "Project Cost allocation\Temp\Temp\Temp\Temp (which is very unusual). In any case try this version.
Consolidated.xlsm

Author

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

Author

Commented:
Just an awesome job!
Thank you sir!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial