Solved

File does not close

Posted on 2014-07-24
12
167 Views
Last Modified: 2014-07-24
Folks,
I have attached two files. The first file is the "Consolidated" which requires spreadsheets for another file "Append". Once I've added my worksheets from "Append" the "Append file does not always close.
Consolidated.xlsm
Append.xlsm
0
Comment
Question by:Frank Freese
  • 6
  • 6
12 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
It does always close but the sheets that are copied contain a "ReurnMenu" macro which during the append process gets set to '<your path>\Append.xlsm'!ReturnMenu so when you go to a sheet and click that button, Excel reloads the Append.xlsm workbook. Change the Append macro to this and that won't happen any more. (Lines 32 and 33 were added).

Sub Append()

    Dim strFileSelected As String
    Dim objOfficeDialog As Object
    Dim wbDestination As Workbook
    Dim wbSource As Workbook
    Dim sh As Worksheet
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Set objOfficeDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set wbDestination = ActiveWorkbook
    
    With objOfficeDialog
        .Title = "Select the Project Cost Allocation file"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strFileSelected = .SelectedItems(1)
    End With
Dim strSuffix As String
If strFileSelected <> "" Then
        strSuffix = InputBox("Please enter text to append to tab names")
        Set wbSource = Workbooks.Open(strFileSelected)
        
        For Each sh In wbSource.Sheets
            sh.Copy After:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            wbDestination.Worksheets(wbDestination.Worksheets.Count).Name = sh.Name & " " & strSuffix
            ActiveSheet.Shapes.Range(Array("Button 1")).Select
            Selection.OnAction = "ReturnMenu"
        Next sh
        
       ' wbSource.Close False
         wbSource.Close True
    End If
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Add            

Range("B1").Select

Between lines 33 and 34.
0
 

Author Comment

by:Frank Freese
Comment Utility
I was wondering why I was having that problem....thank you
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
I appreciate that very much
Thank you
0
 

Author Comment

by:Frank Freese
Comment Utility
ha! ha! caught you...missed End Sub. Well that's OK
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Here's a freebie. Currently the user is asked to input a four-digit year (which should be validated)  and then later they are asked to "enter text to append to tab names". If the second one should always be the two-digit year of the first on then that second inputbox can be eliminated if you do this.

At the top of modAddYear add Public gstrYear As String

In the AddYear macro add

    gstrYear = Right$(Yearly, 2)

after

the strYr = " " & Right$(Yearly, 2) line

And finally in the Append macro change

strSuffix = InputBox("Please enter text to append to tab names")

to

strSuffix = gstrYear
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
In addition to the previous freebie, here's validation for the four-digit year if you want to use it.

After the Yearly = InputBox("Please enter a 4 digit year to append to the added rows") line add

    If Yearly < "2014" Or Yearly > "2050" Then
        MsgBox "Invalid Year please try again"
        Exit Sub
    End If

Open in new window


That only allows years 2014 to 2050 inclusive.
0
 

Author Comment

by:Frank Freese
Comment Utility
So you're not hanging around till 2050 either? thanks
0
 

Author Comment

by:Frank Freese
Comment Utility
everything changed
I'm glad when this project is done...I want to get back to my Statistics
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When the user wants to append a file will the file always be the current year? Or perhaps the current year or next year? In other words say it's now 2015. What really are the years that should be allowed to be entered?
0
 

Author Comment

by:Frank Freese
Comment Utility
Each workbook represents a project for any given year between 2014 - 2050. It will be up to them to be careful not to overwrite a year they've already done. They're not going back in time, so I've been told. If they do then I'll explain to them how to change the date range. I'm not going forward with appending additional tabs and yearly data to the Consolidated workbook. They think all you've got to do is copy and paste. My response has been "Then copy and paste" and look to someone else to automate whatever else you want.

I'm waiting for final approval of what has been delivered. When I get that then I'll send the final file that has the changes you brought forward on closing the Append file as well as the date routine. The I am done.

What I see happening is that someone will get a hold of these workbooks soon promising things only to making it worse. Just don't call me.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Understood but the way the validation is now an input of 2014 would be OK in any current year up to 2050 so they could "go back in time". If you change this

If Yearly < "2014" Or Yearly > "2050" Then

to

If Yearly < Year(Now) Or Yearly > Year(Now) + 2 Then

they would only be able to add the current year to the current year plus 2, no matter what the current year is. Of course you could widen or narrow the range by changing the "+2".

You could also check to see if any year entered already exists in the workbook and if so don't allow it, but that would be the subject of a new question.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now