Check File Name Existence in Array

[Kirk P.]

I've got this code, which works as expected:

Sub ProcessCTSWorkbooks()
Dim bk As Workbook
Dim sPath As String
Dim v As Variant
Dim i As Long
Dim sh As Worksheet

On Error GoTo ErrHandle

sPath = "\\oprdgv1\depart\Finance\_Budget\Current Year ePlanning Loads\"
v = Array("CTS_700_HPP_EPlanning_Load.xls",
"CTS_747_Education_Eplanning_Load.xls", _
"CTS_750_TRS_Eplanning_Load.xls", "CTS_751_CRS_Eplanning_Load.xls", _
"CTS_752_CHD_Eplanning_Load.xls", "CTS_753_NRS_Eplanning_Load.xls", _
"CTS_754_RRS_Eplanning_Load.xls", "CTS_755_BRS_Eplanning_Load.xls", _
"CTS_756_KRS_Eplanning_Load.xls", "CTS_759_MTP_Eplanning_Load.xls", _
"CTS_760_SPR_Eplanning_Load.xls",
"CTS_771_Comprehensive_Cancer_Eplanning_Load.xls", _
"CTS_772_Pregnancy_Childbirth_Eplanning_Load.xls",
"CTS_790_Case_Management_Eplanning_Load.xls", _
"CTS_999_OH_Eplanning_Load.xls")
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(sPath & v(i), UpdateLinks:=0)
For Each sh In bk.Worksheets
sh.UsedRange.Formula = sh.UsedRange.Value
Next
bk.Close SaveChanges:=True
Next

ErrHandle:
MsgBox "Error #: " & Err.Number & ": " & Err.Description & vbCrLf
Exit Sub

End Sub

Right now, it processes each one as the file is found. Is there a good way
to check to make sure all the file names in the array actually exist BEFORE
the processing starts?

 

[Bob Phillips]

For i = LBound(v) To UBound(v)
if Dir(v(i)) "" Then
MsgBox "Not all present, " & v(i)
Exit For
End If
Next i

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RB Smissaert]

Yes, something like this:

Function bFileExists(ByVal sFile As String) As Boolean
Dim lAttr As Long
On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0
End Function

Then in your processing Sub something like this:

For i = 0 To UBound(v)
If bFileExists(sPath & v(i)) = False then
Msgbox "This can't work, missing file:" & vbcrlf & _
sPath & v(i)
exit sub
end if
next i


RBS