Solved

File does not close

Posted on 2014-07-24
12
169 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 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40217357
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 46

Expert Comment

by:Martin Liss
ID: 40217379
Add            

Range("B1").Select

Between lines 33 and 34.
0
 

Author Comment

by:Frank Freese
ID: 40217383
I was wondering why I was having that problem....thank you
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Closing Comment

by:Frank Freese
ID: 40217386
I appreciate that very much
Thank you
0
 

Author Comment

by:Frank Freese
ID: 40217404
ha! ha! caught you...missed End Sub. Well that's OK
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40217420
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40217431
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
ID: 40217436
So you're not hanging around till 2050 either? thanks
0
 

Author Comment

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

Expert Comment

by:Martin Liss
ID: 40217526
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
ID: 40217663
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 46

Expert Comment

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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