Solved

File not closing

Posted on 2014-07-21
5
100 Views
Last Modified: 2014-07-21
Folks,
Let me explain the below code. The user is ask to locate a workbook that is to append to my workbook. The user is ask to enter in a 2 digit #. What happens is that each appended tab will now have Jan 14, Feb 14, etc.
My problem is that after all is done the Append workbook does not close. I've attached the two file needed
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
        Next sh
        
        wbSource.Close False
    End If
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
    
End Sub

Open in new window

Consolidated.xlsm
Append.xlsm
0
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40209812
When I run the code from the VBA editor the opened workbook closes just fine for me.

How are you running it?
0
 

Author Comment

by:Frank Freese
ID: 40209973
imnorie,
The workbook is not opened by its. It is called from a module in another workbook "Consolidated". That's why I uploaded two files. From "Consolidated" the user click on "Add Year", enters a four digit year then is ask to locate the file. In this case it has to be Append.xlsm. From there they enter a 2 digit # to appended to the name of the tabs. Once that is done new worksheets are added to "Consolidated" with the correct tab labels. However the Append file remains open. I think I found the problem in line 34  above that is set to "wbSource.Close False" rather than true. Can you affirm please
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40210057
I didn't just open the Append file, I downloaded both files, opened Consolidated, opened the VBE, ran the sub named Append, picked the Append.xlsm file from the dialog, entered a 2 digit number in the input box etc and the Append.xlsm workbook was closed after all the worksheets had been imported and renamed.

The True/False argument of the Close method tells VBA whether or not to save changes, it won't stop the workbook being closed.
0
 

Author Comment

by:Frank Freese
ID: 40210079
See, all I need to ask someone and these problems disappear. Magic! Thanks for looking into this. I don't have a problem except maybe me.
0
 

Author Closing Comment

by:Frank Freese
ID: 40210084
thank you...I appreciate it!
0

Featured Post

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

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

20 Experts available now in Live!

Get 1:1 Help Now