?
Solved

File does not close

Posted on 2014-07-24
12
Medium Priority
?
174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 49

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 49

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 49

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 49

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

Industry Leaders: 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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

801 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