Count excel worsheets in a workbook using file scripting object 2

[traveller4]

I am trying to load workbooks into a database that contain 5 required worksheets with distinct names

contract
contractorinfo
contractdetails
contractstatus
validuntil

These workbooks are supplied from outside sources. The problem is: not all the workbooks contain all 5 worksheets.

The load procedure is automated so it crashes when it encounters one of the workbooks with missing sheet(s).

My question is:

Is it possible to use the File Scripting Object to count worksheets in a workbook. If the count did not = 5 then I would copy it into a reject folder. If this is possible could someone point me in the right direction

Thanks in advance

 

[PHV]

I'd play with OLE Automation (Excel.Application object)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traveller4]

Thanks PH

It was what I was after. I was actually able to incorporate into a DTS package on SQL Server 2000.

Thanks again

 

[infinitelo]

maybe something like this.


Set objXL = CreateObject("Excel.application")
objXL.WorkBooks.open filename
if objxl.sheets.count <> 5 then
handle it
end if


if it is to be it's up to me

 

[traveller4]

Thank for the response it is similar to what I did

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.open(strFullPath)

Set oSheet = newBook.Worksheets
iSheetCount= oSheet.count

appExcel.quit

If iSheetCount <> 5 Then

f2.Copy (sBadSheetlPath)