VBA to close workbooks

[CantGetRight]

Hi there,

I have a macro which opens files based on the contents of a cell range. This cell range has the file names and the macro specifies the folder directory. The macro opens whatever cells are selected and then it closes all files.

I want it to run as is but with one minor tweak - only to close the files it opened (selected cells) and to leave the other Excel files open.

Can anyone help?

Thanks - Code Below.

Mark



Sub OpenWorkBooksandRefreshFormulas()

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'safety prompt
Dim Sure As Integer
Sure = MsgBox("This is a sample message box.", vbYesNo)
If Sure = vbYes Then

Set MasterWB = ThisWorkbook

Dim filename As String


On Error Resume Next

For Each r In Selection


Workbooks.Open filename:= _
"\\Sample File Path\2015\" & r.Value & ".xlsm", UpdateLinks:=0

Next


MasterWB.Activate

On Error Resume Next

Calculate

On Error Resume Next

Dim xWB As Workbook

On Error Resume Next

For Each xWB In Application.Workbooks
If Not (xWB Is Application.ActiveWorkbook) Then
xWB.Close savechanges:=False
End If
Next


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If

End Sub

 

[JackDanIce]

Can you give an example of:

The full file names relating to the names in the cells that are to be selected.

So I understand what's on your sheet.

I've taken those message boxes out, try:

Sub OpenWorkBooksandRefreshFormulas()

Dim r           As Range
Dim arr()       As Variant
Dim x           As Long
Dim OpenCount   As Long

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    'safety prompt
    If MsgBox("This is a sample message box.", vbYesNo) = vbYes Then
            
        On Error Resume Next
        'Put files names into an array from the selected range
        arr = Selection.Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            Workbooks.Open "\\Sample File Path\2015\" & CStr(arr(x, 1)) & ".xlsm", UpdateLinks:=0
            OpenCount = OpenCount + 1
        Next r
        On Error GoTo 0
        
        ThisWorkbook.Activate
        Calculate

        On Error Resume Next
        'close file names from array
        OpenCount = 0
        For x = LBound(arr, 1) To UBound(arr, 1)
            With Workbooks(CStr(arr(x, 1)) & ".xlsm")
                .Activate
                .Close savechanges:=False
                OpenCount = OpenCount + 1
            End With
        Next x
        On Error GoTo 0
        
    End If
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    Erase arr
    
End Sub

 

[CantGetRight]

So this latest code doesn't do anything. I was also getting the error on "next r" so I changed to x as we did before (assuming that was a typo).



The first code you provided was great - if the closing functionality can be tweaked it would be sorted.

To give some background as to what I'm trying to achieve:

I have Headcount files which track numerous data points for employees. It is a monthly file with the exact same structure each month so works perfectly for something like this. In a folder I have files (January 2015 - October 2015) all ending in .xlsm

The master workbook does an INDEX MATCH for someone's employee ID to find out information for them at multiple points in time. I just choose the column field I wish to look for such as their building/floor/city etc. and enter their ID in the row field and highlight the files names I wish to check. The macro runs, the files open and the formulas populate and all workbooks close except the master wb. This is fine but if I have any other workbooks open which are not relevant to this it becomes a problem.

Thanks,
Mark

 

[CantGetRight]

Is this something you are able to help me with?

Thanks in advance!

Mark