-
Solved: MsgBox code to check for Existence of and Opened Files
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules