Start Free Trial

asked on

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.

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

ASKER

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)

Don't you get a Save As dialog that looks like this?

User generated image

In 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.

ASKER

Sure didn't. The original file was over written - acting like a Save rather than a Save As

Please attach your workbook.

ASKER

I was thinking something like this replacing line 21?

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

Maybe but please attach your workbook.

ASKER

I'm using your file "Consolidated-V3" attached
Consolidated-V3.xlsm

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

ASKER

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.

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.

ASKER

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

ASKER

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.

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?

ASKER

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.

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?

ASKER

Yes

ASKER

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.

ASKER

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.

ASKER

Maybe add a macroRefreshData?

ASKER

I got refresh to work!
Are you working on any of this now?

Yes. Please explain what code you added, if any, to fix the grand total problem.

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

ASKER

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

ASKER

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

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

ASKER

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:

User generated image

6. Click on "OK"
7. Located the folder I wanted to save the file

User generated image

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

User generated image

9. Selected "Save"

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

Just an awesome job!
Thank you sir!

You're welcome.