?
Solved

Assign a file a name when saved

Posted on 2014-08-04
31
Medium Priority
?
135 Views
Last Modified: 2014-08-04
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.
0
Comment
Question by:Frank Freese
  • 18
  • 13
31 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40238992
The code in the workbook already does that. so I'm confused.
0
 

Author Comment

by:Frank Freese
ID: 40239022
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239036
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Frank Freese
ID: 40239041
Sure didn't. The original file was over written - acting like a Save rather than a Save As
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239054
Please attach your workbook.
0
 

Author Comment

by:Frank Freese
ID: 40239059
I was thinking something like this replacing line 21?

Me.Application.Workbooks("Consolidated “ & Range(“R2”).xlsm").Close
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239076
Maybe but please attach your workbook.
0
 

Author Comment

by:Frank Freese
ID: 40239095
I'm using your file "Consolidated-V3" attached
Consolidated-V3.xlsm
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239107
Rename the file to "Consolidated.xlsm" as I suggested in our last thread and it will behave differently.
0
 

Author Comment

by:Frank Freese
ID: 40239120
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239149
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
 

Author Comment

by:Frank Freese
ID: 40239240
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
 

Author Comment

by:Frank Freese
ID: 40239256
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239357
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
 

Author Comment

by:Frank Freese
ID: 40239386
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239439
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
 

Author Comment

by:Frank Freese
ID: 40239460
Yes
0
 

Author Comment

by:Frank Freese
ID: 40239809
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
 

Author Comment

by:Frank Freese
ID: 40239853
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
 

Author Comment

by:Frank Freese
ID: 40239855
Maybe add a macroRefreshData?
0
 

Author Comment

by:Frank Freese
ID: 40239869
I got refresh to work!
Are you working on any of this now?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40239883
Yes. Please explain what code you added, if any, to fix the grand total problem.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40240013
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
 

Author Comment

by:Frank Freese
ID: 40240093
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
 

Author Comment

by:Frank Freese
ID: 40240164
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40240303
Please describe in bulleted steps, what you did to arrive at that result.
0
 

Author Comment

by:Frank Freese
ID: 40240333
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
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40240367
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
 

Author Comment

by:Frank Freese
ID: 40240406
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
 

Author Closing Comment

by:Frank Freese
ID: 40240409
Just an awesome job!
Thank you sir!
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40240416
You're welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question