' ******************************************************
' *
' * Name:         modReadXlsFile.vbs
' *
' * Design Phase:
' *     Author:   John Miner
' *     Date:     06/30/2008
' *     Purpose:  A module to read xls files.
' *
' ******************************************************

'
' 4 - Define a module to read a xls file
'

' Declare all variables
Option Explicit


'
' Define the class
'

Class clsReadXlsFile


    ' -- Pipe Delimited Data --
    Private strOneRec
        
    Public Property Get OneRec
        OneRec = strOneRec
    End Property
    
    Public Property Let OneRec(varOneRec)
        strOneRec = varOneRec
    End Property
    

    ' -- Define the worksheet name --
    Private strSheetName
        
    Public Property Get SheetName
        SheetName = strSheetName
    End Property
    
    Public Property Let SheetName(varSheetName)
        strSheetName = varSheetName
    End Property


    ' -- Data element - Connection --
    Dim objCon

    ' -- Data element - Command --
    Dim objCmd

    ' -- Data element - Record set --
    Dim objRec

    ' -- Data element - Flags --
    Dim RECS
    DIM EOF


    '
    ' A - initialize the class
    '
    
    Private Sub Class_Initialize()
    
        ' Create the connection object
        Set objCon = CreateObject("ADODB.Connection")

        ' Create the command object
        Set objCmd = CreateObject("ADODB.Command")

        ' A brand new object
        strOneRec = ""
        RECS = 0
        EOF = False

    End Sub


    '
    ' B - destroy the class
    '
    
    Private Sub Class_Terminate()
           
        ' Nothing to do
       
    End Sub


    '
    ' Define open method
    '
    Public Sub OpenDataFile(strOpenFile)

        ' Define constants
        Const adCmdText = &H0001

        ' Missing file!
        If Not FileExists(strOpenFile) Then
            RECS = -1
            EOF = True
            Exit Sub
        End if

        ' Make up the connection string
        Dim strCon
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strOpenFile & ";Extended Properties=" & chr(34) & "Excel 8.0;HDR=YES" & chr(34)

        ' Open the connection
        objCon.ConnectionString = strCon
        objCon.Open

        ' Text command type
        objCmd.ActiveConnection = objCon
        objCmd.CommandType = adCmdText

    End Sub


    '
    ' Define Pull method
    '
    Public Sub PullData()

        ' Declare variables
        Dim intCnt
        Dim sTemp

        ' Initialize variables
        strOneRec = ""
        sTemp = ""

        ' File does not exist?
        If EOF Then
            Exit Sub
        End If

        ' Start of data?
        If RECS = 0 Then

            ' Get the data
            objCmd.CommandText = "SELECT * FROM " & strSheetName
            Set objRec = objCmd.Execute
          
        ' Middle of data?    
        Else
            objRec.MoveNext
        End If

        ' Increment counter
        RECS = RECS + 1

        ' End of data!
        If objRec.EOF or objRec.BOF Then

            ' Set flags
            EOF = True
            RECS = -1

            ' Close the record set
            objRec.close
            set objRec = nothing

            ' All done
            Exit Sub

        End If

        ' Save recordset as pipe delimited string (check fields for reserved character)
        For intCnt = 0 to objRec.Fields.Count -1
            sTemp = objRec.Fields.Item(intCnt)
            sTemp = replace(sTemp, "|", " ")

            strOneRec = strOneRec &  sTemp 
            if intCnt <> objRec.Fields.Count -1 then
                strOneRec = strOneRec & "|"
            end if
        Next  

        ' Save recordset as pipe delimited string (no reserved character check)
        ' strOneRec = objRec.GetString(,1,"|",,) 

    End Sub


    '
    ' Define close method
    '
    Public Sub CloseDataFile()

        ' If file does not exist
        On Error Resume Next

        ' Clean up cmd obj
        set objCmd = nothing
	
        ' Clean up conn obj
        objCon.Close
        set objCon = nothing

    End Sub

    '
    ' Define file exists
    '
    Private Function FileExists(strFile)

        ' Declare variables
        Dim objFSO

        ' Get the file system object
        Set objFSO = CreateObject("Scripting.FileSystemObject")

        ' Does the file exist?
        If objFSO.FileExists(strFile) Then 
            FileExists = True
        Else
            FileExists = False
        End If

        ' Release the object
        Set objFSO = nothing

    End Function

End Class