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

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

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Workbooks(MYFileArray(i)).Close SaveChanges:=False

Kevin
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
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.

 
zorvek (Kevin Jones)ConsultantCommented:
Any of the above.

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

Kevin
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
These will both work great, thanks.

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.