please help: Object variable or With block variable not set

[Biznez]

Getting this error mesage but not sure y. Can someone please help. Im trying to scrape multiple row payments from Attachmate and input it into column E but i keep getting this error message

Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()

'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")   ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
        Stop
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 3000     ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 Is Nothing) Then
        MsgBox "Could not create the Session object.  Stopping macro playback."
        Stop
    End If
    If Not Sess0.Visible Then Sess0.Visible = True
    Sess0.Screen.WaitHostQuiet (0)
    
    Dim fso As Object
    Dim ts As Object
    Dim i
    Dim strCBName
    Dim blnHasMoreLines
    Set obj = GetObject("C:\Documents and Settings\Harjinder_Chahal\Desktop\Projects\Book1.xlsm") 'File is already open

    'create the file system object
    'Set fso = CreateObject("Scripting.FileSystemObject")
 
    'get the copybook name
    strCBName = "T328"
 
    'create the copybook file

    'Set ts = fso.CreateTextFile("C:\temp\" & strCBName & ".txt", True)
    
    blnHasMoreLines = True
    
    ' record the loan information & column headers once
    obj.Worksheets("Input").Cells(2, "A").Value = Sess0.Screen.GetString(4, 11, 1)
    obj.Worksheets("Input").Cells(2, "B").Value = Sess0.Screen.GetString(4, 18, 6)
    obj.Worksheets("Input").Cells(2, "C").Value = Sess0.Screen.GetString(4, 34, 11)
    obj.Worksheets("Input").Cells(2, "D").Value = Sess0.Screen.GetString(5, 2, 27)
     
    'copy all lines on the screen except the header until there are no more lines
    While blnHasMoreLines
        blnHasMoreLines = (Sess0.Screen.Search("NEXT PAYMENT DUE DATE").Value = "")

        For i = 10 To 21
            [COLOR="#FF0000"]obj.Worksheets("Input").Range("E2").Value = ts.Sess0.Screen.GetString(i, 10, 8)[/COLOR]
        Next i
        
        'send the next page command
    Sess0.Screen.SendKeys ("<Pf8>")
    Sess0.Screen.WaitHostQuiet (0)
    Wend


    'close and destroy objects
    ts.Close
   Set ts = Nothing
    'Set fso = Nothing

    MsgBox "Done recording " & strCBName
    
    Sess0.Screen.WaitHostQuiet (0)

    System.TimeoutValue = OldSystemTimeout
End Sub

 

[SkipVought]

Well i gave you the solution, but you ignored it, so the duplication!

 

[Biznez]

Hey SkipVought, for the process date column, there is a blank line between last process date and Totals.

 

[SkipVought]

Okay?

 

[Biznez]

Hey Skip, how can i find blank cell in column A starting from A5 and clear 10 rows after that blank cell?

 

[Biznez]

i tried this but it only clears values in column A after blank cell and not entire rows

Lastrow = Sheets("Input").Cells(65536, 1).End(xlUp).Row
Sheets("Input").Range("A" & Lastrow - 7 & ":" & "A" & Lastrow).Resize(9).Clear

 

[SkipVought]

WHAT is it that you need to do, rather than HOW you think it oufgt to be done. For instance WHY dp you need to clear stuff on the sheet

 

[Biznez]

because i have no knowledge of other ways.

 

[SkipVought]

Please read my request again and respond accordingly.

 

[Biznez]

how can i find blank cell in column A starting from A5 and clear 10 rows after that blank cell?

 

[SkipVought]

Why is there an empty cell in A and data in the other cell where A is empty?

 

[Biznez]

i have dates in column starting at A5. I would like to delete last 10 ROWS after there are no more dates in column A.

In column A5 i have this data

01/01/15
02/01/15
03/01/15
04/01/15 <--Last date posted
'Blank Cell'
First
Last
Who
where
Why
when
candy
house


Data in red, i want those ROWS cleared.

 

[SkipVought]

This does not seem to be data that belongs in that column. So why not delete all that data manually and forget about it.

If you want to reset your sheet turn on your macro recorder an record deleting everything from row 5 to the bottom of the sheet.

Then observe your recorded code, modify as needed on your procedure.

 

[Biznez]

That red data does belong in that column and i wanted a macro to remove those rows

 

[SkipVought]

How does TEXT beling in a DATE COLUMN???

 

[Biznez]

cuz thats the bottom of the last page

 

[SkipVought]

cuz thats the bottom of the last page"

THAT is why the screen navigation logic should be the FIRST matter of design and coding importance, as I previously urged you to consider. Kind of painful, is it not, to have arrived at this point by such a circuitous route?

'
    Do

        For i = 10 To 21
            '[b]
            If Trim(Sess0.Screen.GetString(i, 10, 9)) = "TOTALS" Then Exit For
            '[/b]
            obj.Worksheets("Input").Range("A5").Offset(A).Value = Sess0.Screen.GetString(i, 10, 9)  'Value Date
            obj.Worksheets("Input").Range("B5").Offset(A).Value = Sess0.Screen.GetString(i, 20, 7)  'Type
            obj.Worksheets("Input").Range("C5").Offset(A).Value = Sess0.Screen.GetString(i, 27, 11) 'Amount
            obj.Worksheets("Input").Range("D5").Offset(A).Value = Sess0.Screen.GetString(i, 48, 11) 'Interest
            obj.Worksheets("Input").Range("E5").Offset(A).Value = Sess0.Screen.GetString(i, 58, 11) 'Principal
            obj.Worksheets("Input").Range("F5").Offset(A).Value = Sess0.Screen.GetString(i, 69, 11) 'Principal Oustanding
            
            'Searches for "TOTAL" on screen and exits loop
            If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do
            A = A + 1
        Next i
        'Sends the next page command
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (0)
   Loop

 

[SkipVought]

sorry I missed moving the Exit Do

First you look for the TOTALS string in the rows that could contain the dates, as it appears from your posts that that happens, to exit the For...Next loop

Then, once rows 10-21 are processed, you search for TOTALS to exit the Do..Loop.

'
    Do

        For i = 10 To 21
            '[b]
            If Trim(Sess0.Screen.GetString(i, 10, 9)) = "TOTALS" Then Exit For
            '[/b]
            obj.Worksheets("Input").Range("A5").Offset(A).Value = Sess0.Screen.GetString(i, 10, 9)  'Value Date
            obj.Worksheets("Input").Range("B5").Offset(A).Value = Sess0.Screen.GetString(i, 20, 7)  'Type
            obj.Worksheets("Input").Range("C5").Offset(A).Value = Sess0.Screen.GetString(i, 27, 11) 'Amount
            obj.Worksheets("Input").Range("D5").Offset(A).Value = Sess0.Screen.GetString(i, 48, 11) 'Interest
            obj.Worksheets("Input").Range("E5").Offset(A).Value = Sess0.Screen.GetString(i, 58, 11) 'Principal
            obj.Worksheets("Input").Range("F5").Offset(A).Value = Sess0.Screen.GetString(i, 69, 11) 'Principal Oustanding
            
            A = A + 1
        Next i
        [b]
        'Searches for "TOTAL" on screen and exits loop
        If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do
        [/b]
        'Sends the next page command
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (0)
   Loop

 

[Biznez]

thanks Skip but that did not work. im still getting data at bottom from last page

 

[SkipVought]

Then there's some other condition that we don't know about!

So here's your screenshot of the last page
[pre]
PROCESS VALUE TYPE CTR AMOUNT CHARGE INTEREST PRINCIPAL PRINCIPAL
DATE DATE REF OUTSTANDING

03/31/15 03/31/15 PA 331.48- 0.00 0.68- 330.80- 36.96

TOTALS 5,577.95- 36.96

ACCRUED INTEREST FOR PYMT BY STUDENT 0.09 F: 0.08 P: 0.01
BALANCE OF CHARGES 0.00
AMT OF PAYOUT 37.05 PAYABLE ON 04/17/15
PER DIEM INT., ONE MTH INT., MONTHS 0.01 0.16 1
NEXT PAYMENT DUE DATE 04/30/15
[/pre]

So I assume that on row 10 column 10 you have 03/31/15 and on row 12 column 10 you have TOTALS. correct?

 

[Biznez]

03/31/15 is on 10,11 and TOTALS is on 12,2. However, sometimes depending on transactions, when i scroll bottom of page totals can be shown twice. so basically 03/31/15 can be on 11,11 and TOTALS can be on 13,2.