Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

File not closing

Posted on 2014-07-21
5
Medium Priority
?
112 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 36

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 36

Accepted Solution

by:
Norie earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

569 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