Check if file exist based on list

[JC]

Hi Everyone,

I really appreciate your help. Thanks in advance.

I have a list of accounts in a workbook. There should be a file on the
network for each account.

I have programmed Excel to consolidate all of the files into a master
workbook. This works fine, but before I run the consolidation macro, I want
to make sure that our staff has saved all of the files are on the network.
If the account is not on the network, then advise the user that "all account
reports have not been submitted. Please save all reports to the network and
then run the macro again". Close the macro. If all the files are there, run
the macro.

Network Path = W:\Budget Monitoring\IMJ\
IMJ is a Division

The Account List is saved as
W:\Budget Monitoring\"'Account List.xls'!IMJ"

List Ex.
70435
70372
70542

This is really simplified. I've got 27 divisions and about 200 accounts.

Thanks!

 

[Joel]

there is a ReadOnly Property if yo open the workbook. If somebody else is
using a workbook the the READONLY will be TRUE. First open each file and
testt if you get READONLY. Try something like this. The column in the
Acount List file may need changes. Modify code as needed.

Sub checkIfOpen()

Folder = "W:\Budget Monitoring\IMJ\"

ChkFileName = "W:\Budget Monitoring\Account List.xls"
Set Chkfile = Workbooks.Open(Filename:=ChkFileName)
Set ChkFileSht = Chkfile.Sheets("IMJ")

With ChkFileSht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
AccountName = .Range("A" & RowCount)
AccountFileName = Folder & AccountName & ".xls"
Set AccountFile = Workbooks.Open(Filename:=AccountFileName)
If AccountFile.ReadOnly = True Then
MsgBox ("AccountName" & " : File is Oen. Please Close")
End If
AccountFile.Close savechanges:=False
RowCount = RowCount + 1
Loop
End With

 

[JC]

Thanks Joel but I don't think this is what I am looking for. I've also
slightly altered the scope of what I want to do with this.

I still want to check if a file exists based on a list, the values in a
range. If the file exists, then I want to open the file and execute a macro
within that file, if it doesn't exists then I want to move on to check the
next cell in the range. I found this code elsewhere, but I am having
problems getting it to stick.

Sub Jective()

Dim rRange As Range, rCell As Range
Dim strPath As String

strPath = "W:\Budget Monitoring\IMB\"
Set rRange = Sheets("Accounts").Range("A2", "A3")

For Each rCell In rRange
If Dir(strPath & rCell & ".xlsm") > 0 Then
Application.Run ("'W:\Budget Monitoring\IMB\" & rCell & ".xlsm'_
!ExportData")
End If
Next rCell

End Sub

Thanks for any help.