How do I get this >ReadLine to work in the code below

[joe mina]

I am new to vb. I am working on this and need help in getting it to work.
Below is how my code looks like. I am trying to read data from an excel spreadsheet on a floppy disk. I am trying to display it based on my search criteria on a reults form. I am frustrated after trying for a whole week and still cannot get it to work.

It keeps erroring at the .ReadLine, the error says -
[object does'nt support this property or method]
Could somebody look in the code to see what I am doing wrong.

I will appreciate help--thanks
-----------------------------------------------------

MAIN FORM CODE

Option Explicit
Public gblnCancel As Boolean

Private Sub cmdCancel_click()
gblnCancel = True
Unload frmPastDueAmt
End Sub

Private Sub cmdGetFile_Click()
'cmd will allow user to browse for a file using the common dialog control'
With dlgGetFile
.CancelError = True
.ShowOpen
txtFileToPrint.Text = .FileName
End With
End Sub

Private Sub cmdOk_Click()
frmResults.Show vbModal
frmResults.PrintFile txtFileToPrint.Text
End Sub

Private Sub Form_Load()
frmPastDueAmt.Show vbModal
If frmPastDueAmt.gblnCancel = True Then
Unload Me
End If
End Sub


Private Sub Form_Unload(Cancel As Integer)
Dim iAnswer As Integer

iAnswer = MsgBox("Are you sure you want to cancel?", vbYesNo)
If iAnswer = vbNo Then
Cancel = True
Else
Unload Me
End If
End Sub
+++++++++++++++++++++++++++++++++++++++++++++

RESULTS FORM CODE

Option Explicit

Public Sub PrintFile(strFileName As String)
Dim fsoFileSystem As FileSystemObject 'the file system
Dim tsFileToPrint As TextStream 'the file we will open

Set fsoFileSystem = New FileSystemObject
Set tsFileToPrint = fsoFileSystem.OpenTextFile(strFileName)

With tsFileToPrint
While Not .AtEndOfStream 'loop until the end of file
Print .ReadLine 'read a line and print it
Wend 'loop until the end of file
End With

Set tsFileToPrint = Nothing 'deallocate the text stream
Set fsoFileSystem = Nothing 'deallocate the reference to the file
End SUb

[Muddy]

I dont use the FSO, but I am pretty sure you cant use it to read Excel files.

You are going to have to initiate an instance of excel by;
1)early binding (set project reference to excel then "dim objExcel as Excel.Application")
2) late binding ( no reference needed .. "CreateObject("Excel.Application")"

Ill try to find an example of such and will come back to post a link ...

[Muddy]

here you are:

http://www.vbforums.com/showthread.p...threadid=74210

[kmani_r]

HI,

It is better to use Excel Application Object with VBA to search through Excel (Work sheet) file . Use Work Sheet object, so you can browse through sheet (Rows/Columns).

'This code is to Try for text format files for better executoin and results

    Public Sub Get_String_Search(strToSearch As String, strFileName)
    Dim objFileSysObj As New FileSystemObject 'FSO Object
    Dim objFile As File 'File Object
    Dim streamText As TextStream 'Text Stream from File
    Dim StrLineRead As String 'For string line from Text Stream method from File
    Dim blnIsFileExits As Boolean 'True if File Exits

          
    On Error GoTo Err_Handler
    
    blnIsFileExits = objFileSysObj.FileExists(strFileName) 'Provide File Name
    
    If blnIsFileExits Then
        'If File Exits
        Set objFile = objFileSysObj.GetFile(strFileName) 'Get File object
             
        ' Read the contents of the file.
        Set streamText = objFile.OpenAsTextStream(ForReading)
        
        'Loop through full file
         Do While streamText.AtEndOfLine <> True
            'Write your logic to search string in line

            'After you can go to next line and use your search logic
            streamText.SkipLine
        Loop

        'Close file 
        streamText.Close

    Else
        'File missing handling code
        
    End If
    
    'Exit Sub
    Exit Sub
    
Err_Handler:
    If Err.Number <> 0 Then 'Any other error No. to Handle
        'Write Error Handling Code
    End If
End Sub