Solved

File not closing

Posted on 2014-07-21
5
101 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 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

16 Experts available now in Live!

Get 1:1 Help Now