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?

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)

"Kirk P." <(E-Mail Removed)> wrote in message
news:3210C138-790A-40CC-8B8D-(E-Mail Removed)...
> 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?
>

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


"Kirk P." <(E-Mail Removed)> wrote in message
news:3210C138-790A-40CC-8B8D-(E-Mail Removed)...
> 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?
>