?
Solved

Closing a series of dynamically named workbooks without re-activating them.

Posted on 2011-05-12
7
Medium Priority
?
192 Views
Last Modified: 2012-06-21
How do I write it in the macro "Aggregate" so that each workbook closes after its data has been pasted into ThisWorkbook?  'Workbooks(MYFileArray(i)).Close Saved = True' obviously doesn't work :-)

Thanks,
John
Dim top As Range, btm As Range, rng As Range, top2 As Range
Dim Fldr As String
Dim MYFileArray() As String
Dim j As Long
~~~~~~~~~~~~~~~~>>

Sub Aggregate()
    Fldr = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\JAL\3_Working Files\SourceFiles"
    j = 0
    ListFiles Fldr, "*.xls"
    For i = 1 To UBound(MYFileArray) - 1
        myarray = Split(GetFilenameFromPath(MYFileArray(i)), ".")
        If myarray(0) Like "*Week ##" Then
            '~~> The below code till "ActiveWorkbook.Close" has not been tested
            Workbooks.Open Filename:=MYFileArray(i)
        Dim top As Range
            Set top = [A6]
            Set btm = top.End(xlDown)
            Set rng = Range(top, btm).EntireRow
            rng.Copy
            ThisWorkbook.Activate
        Dim top2 As Range
            Set top2 = [A4].End(xlDown).Offset(1, 0)
            top2.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            Workbooks(MYFileArray(i)).Close Saved = True 'I NEED SOMETHING HERE THAT WILL CLOSE THIS WORKBOOK WITHOUT ACTIVATING IT.
        End If
    Next
End Sub
~~~~~~~~~~~~~~~~>>
Public Function ListFiles(FolderPath As String, Extension As String)
    Dim i As Long
    Dim FolderName As String
    Dim DirNames() As String

    On Error Resume Next
    FolderName = Dir(FolderPath & "\" & Extension, vbDirectory)
    On Error GoTo 0
       
    Do While FolderName <> vbNullString
        j = j + 1
        ReDim Preserve MYFileArray(j)
        MYFileArray(j) = FolderPath & "\" & FolderName
        FolderName = Dir()
    Loop
End Function
~~~~~~~~~~~~~~~~>>

Function GetFilenameFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Open in new window

0
Comment
Question by:gabrielPennyback
  • 3
  • 3
7 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35751558
Workbooks(MYFileArray(i)).Close SaveChanges:=False

Kevin
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35751572
It occurs to me that another solution would be to let them all stay open and then at the end of the macro have something that says in effect: Close all workbooks beginning with "JAL"?

For that matter, the whole macro could be a lot simpler I suppose if I just removed all irrelevant workbooks from the folder, and had the macro say "Just open every book in the folder."

Thanks,
John

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35751579
Any of the above.

How about opening one, pulling the data, and then closing it before moving to the next?

Kevin
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35751627
Hi Kevin, I get a 'subscript out of range' error on that line. ???  In the meantime I googled the answer on how to close all workbooks but 'ThisWorkbook'. Still, it would be nice to know how to do it the one-at-a-time way. Failing that - and probably even better to know - I'd love to simplify the whole code now that I've learned that I can move all the extraneous workbooks to another folder.

Whatever you got! :-)

John
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 35751641
This code will close all workbooks except the workbook in which the code is running:

    Dim Workbook As Workbook
   
    For Each Workbook In Application.Workbooks
        If Not Workbook Is ThisWorkbook Then Workbook.Close SaveChanges:=False
    Next Workbook

Kevin
0
 
LVL 35

Assisted Solution

by:Norie
Norie earned 1000 total points
ID: 35751712
Why not create a reference to the workbook(s) when you open them and then use that reference when you want to close them?

You wouldn't need to activate them or worry about using the name to refer to them.

Something like this perhaps.
Dim wbOpen As Workbook

 If myarray(0) Like "*Week ##" Then

            ' open workbook and create reference to it
            Set wbOpen = Workbooks.Open(Filename:=MYFileArray(i))

            ' other code to copy from opened workbook        

            wbOpen.Close False ' close workbook opened above
 End If

Open in new window

0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 35774831
These will both work great, thanks.

- John
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

807 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