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
Solved

File not closing

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

790 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