Hi All,

I have the following code to open set of workbooks that are listed in a range specified as an input to the macro:

[VBA]Option Explicit

Sub Open_Workbooks_in_Range(rngWorkbookstoOpen As Range)

Dim rngoneCell As Range

For Each rngoneCell In rngWorkbookstoOpen

If FileFolderExists(CStr(rngoneCell.Value)) = True And WorkbookIsOpen(CStr(rngoneCell.Value)) = False Then

Workbooks.Open Filename:=CStr(rngoneCell.Value), UpdateLinks:=0

End If

Next rngoneCell

End Sub[/VBA]

This is fine, but before running this macro, I would like some code to loop through the same range and in a MsgBox Output, list the following:

1. List of Files that don't exists in the given range
2. List of Existing and Opened Files and which User (the environ variable) has them open.

I know it is a simple loop to build the above, but am not sure how to load the list in a single array which can be neatly output in the MsgBox. As such, I have a possible template for the code, but require some help to help me complete it.

[VBA]Option Explicit

Sub Code_to_Loop_and_ListFilesMsgbox(rngWorkbookstoOpen As Range)

Dim rngoneCell As Range

For Each rngoneCell In rngWorkbookstoOpen

If FileFolderExists(CStr(rngoneCell.Value)) = False Then

' code here to list all the files that don;t exist, so we
' can output them to the MsgBox in one message at the end

End If

If WorkbookIsOpen(CStr(rngoneCell.Value)) = False Then

' code here to list all the files that are open and the ENVIRON varible
' of the user who has them open
' so we can output them to the MsgBox in one message at the end

End If

Next rngoneCell

' Need code here to LIST ALL of the above files in the 2 categories in a single MsgBox
' Categories should just be separated by a Return Key in the Msgbox

End Sub[/VBA]

thanks and regards