Solved

File not closing

Posted on 2014-07-21
5
107 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
[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
  • 3
  • 2
5 Comments
 
LVL 34

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 34

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

623 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