Read Excel File

[CrystalVis]

I'm desinging a DTS package in SQL Server 2000 to extract data and the pump the result out to an excel file. I'm stuck at a step where I have to read the excel file to determine who the file will be sent to. For example, if there are records in the file, then email the file to John Doe. If there is no record, send the file to Jane Doe. I'm using ActiveX Script Task to send the email with the excel file as an attachment. Can you help me with the script to read the excel file something like:

if excel.rowcount = 0 then send email to Jan Doe
else if excel.rowcount > 0 then send mail to John Doe
end if

I don't know vbscript. If you can give me and example that would be greatly appreciated.

 

[dilettante]

This turns out to be a weird one via ADO. If using an Excel automation object ("Excel.Application&quot does the trick, maybe that'll be easier.

Here's an ADO example though in a WSH script:

IsWSEmpty.wsf

<job id = &quot;IsWSEmpty&quot;>
  <reference object = &quot;ADODB.Connection&quot;/>
  <script language = &quot;VBScript&quot; src= &quot;XLEmptySheet.vbs&quot;/>
  <script language = &quot;VBScript&quot;>
    Dim strWB, strSheet

    strWB = &quot;WorkBook.xls&quot;
    strSheet = &quot;Sheet1&quot;
    MsgBox strWB & &quot; (&quot; & strSheet & &quot;) Empty sheet = &quot; _
         & CStr(XLEmptySheet(strWB, strSheet))
  </script>
</job>

XLEmptySheet.vbs

Function XLEmptySheet(ByVal strWBName, ByVal strSName)
  'Test for empty Excel worksheet.
  '
  'strWBName - Path and file name of Excel workbook.
  'strSName - Sheet name within the workbook.
  '
  'Return value - True empty worksheet else False.

  Dim objConn, objRS, lngNulls

  Set objConn = CreateObject("ADODB.Connection")
  objConn.CursorLocation = adUseClient
  objConn.Open _
      "Provider = 'Microsoft.Jet.OLEDB.4.0';" _
    & "Data Source = '" & strWBName & "';" _
    & "Extended Properties = 'Excel 8.0;Hdr=No';"
  Set objRS = _
    objConn.Execute("[" & strSName & "$];", Null, adCmdTable)
  XLEmptySheet = (objRS.RecordCount = 0)
  If objRS.RecordCount = 2 Then
    'Special case: empty sheet or sheet with only A1 or A2
    'filled returns 2 rows, either or both of which may be Null!
    objRS.MoveFirst
    If IsNull(objRS(0)) Then lngNulls = lngNulls + 1
    objRS.MoveNext
    If IsNull(objRS(0)) Then lngNulls = lngNulls + 1
    If lngNulls = 2 Then XLEmptySheet = True
  End If
  MsgBox CStr(objRS.RecordCount)
  objRS.Close
  Set objRS = Nothing
  objConn.Close
  Set objConn = Nothing
End Function

If you are using another script host or you can't use a WSF file for the main script, you'll need to make minor changes. You will have to define the ADO constants and you'll have to put the Function inline in the main script.

See the code for some weirdness! I had to see it to believe it myself. I tried sheets that were:
[ul][li]empty,
[li]that had only cell A1 filled,
[li]only cell A2 filled,
[li]only cell A3 filled,
[li]both A1 and A2 filled,
[li]A1 through A3 filled,
[li]only cell B1 filled,
[li]only B2 filled, ...[/ul]
Where there was stuff in column B or later all seemed normal. When there was an empty sheet or only something in A1 or A2 I always get back a rowcount of 2.

Weird, weird, weird. Can anybody explain it?