×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

please help: Object variable or With block variable not set

please help: Object variable or With block variable not set

please help: Object variable or With block variable not set

(OP)
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

CODE -->

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 

RE: please help: Object variable or With block variable not set



Global variable declarations

is not a statement. It is a comment.

RE: please help: Object variable or With block variable not set



Furthermore, this loop will write all the values to one cell.

CODE

'
        For i = 10 To 21
            obj.Worksheets("Input").Range("E2").Value = ts.Sess0.Screen.GetString(i, 10, 8)
        Next i 

do you want all the values in column E or all in row 2?

RE: please help: Object variable or With block variable not set

CODE

dim obj as object 

RE: please help: Object variable or With block variable not set

(OP)
Hi SkipVought, i want the values in column E started at E2

RE: please help: Object variable or With block variable not set

CODE

'
        Dim j as integer
        j = 0
        For i = 10 To 21
            obj.Worksheets("Input").Range("E2").Offset(j).Value = ts.Sess0.Screen.GetString(i, 10, 8)
            j = j + 1
        Next i 

RE: please help: Object variable or With block variable not set

(OP)
This is good SkipVought, thank you, but why doesnt it scroll through next pages and capture that data aswell. F8 key goes to next page and there are multiple pages

RE: please help: Object variable or With block variable not set

Initialize the j variable outside the While...Wend loop

RE: please help: Object variable or With block variable not set

(OP)
I tried that and it goes through the first page and scroll's to bottom and only captures the last page

could it be because of this line?
Set ts = fso.CreateTextFile("C:\temp\" & strCBName & ".txt", True)

i really dont want to create anything. Im just working on the current worksheet where the code is. If i comment that line, i get error message Object doesnt support this property or method on below line

Dim j as integer
j = 0
For i = 10 To 21
obj.Worksheets("Input").Range("E2").Offset(j).Value = ts.Sess0.Screen.GetString(i, 10, 8)
j = j + 1
Next i

RE: please help: Object variable or With block variable not set

(OP)
Hello SkipVought, this is the code i have so far, that code you provided did help but for some reason, it only captures 1 page and does not scroll down and capture other pages

CODE -->

' 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 obj As Object
    Dim i
    Dim strCBName
    Dim blnHasMoreLines
    Set obj = GetObject("C:\Documents and Settings\My Files\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 = GetObject("C:\Documents and Settings\Harjinder_Chahal\Desktop\Projects\Book1.xlsm")
    
    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 = " ")
        
        Dim A As Integer
        A = 0
        For i = 10 To 21
            obj.Worksheets("Input").Range("A5").Offset(A).Value = Sess0.Screen.GetString(i, 10, 9)
            A = A + 1
        Next i
        
        Dim B As Integer
        B = 0
          For i = 10 To 21
            obj.Worksheets("Input").Range("B5").Offset(B).Value = Sess0.Screen.GetString(i, 20, 3)
            B = B + 1
        Next i
        
        Dim C As Integer
        C = 0
          For i = 10 To 21
            obj.Worksheets("Input").Range("C5").Offset(C).Value = Sess0.Screen.GetString(i, 28, 10)
            C = C + 1
        Next i
        
        Dim D As Integer
        D = 0
          For i = 10 To 21
            obj.Worksheets("Input").Range("D5").Offset(D).Value = Sess0.Screen.GetString(i, 49, 10)
            D = D + 1
        Next i
        
        Dim E As Integer
        E = 0
          For i = 10 To 21
            obj.Worksheets("Input").Range("E5").Offset(E).Value = Sess0.Screen.GetString(i, 59, 10)
            E = E + 1
        Next i
        
        Dim F As Integer
        F = 0
          For i = 10 To 21
            obj.Worksheets("Input").Range("F5").Offset(F).Value = Sess0.Screen.GetString(i, 69, 11)
            F = F + 1
        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 

RE: please help: Object variable or With block variable not set

Okay, you changed my j variable to A.

Move the A variable initialization above the While...Wend

CODE

dim A as integer
A = 0
While....


Wend 

RE: please help: Object variable or With block variable not set

(OP)
i did what you told me and it still does not goto next page and scrape

CODE -->

Dim A As Integer
        A = 0
    While blnHasMoreLines
        blnHasMoreLines = (Sess0.Screen.Search("TOTALS").Value = "TOTALS")
        
       
        For i = 10 To 21
            obj.Worksheets("Input").Range("A5").Offset(A).Value = Sess0.Screen.GetString(i, 10, 9)
            A = A + 1
        Next i
        
     
        
        'send the next page command
    Sess0.Screen.SendKeys ("<Pf8>")
    Sess0.Screen.WaitHostQuiet (0)
    Wend 

RE: please help: Object variable or With block variable not set

(OP)
Hey SkipVought, want to thank you for your help. I decided to use Do statement instead and it works fine

RE: please help: Object variable or With block variable not set

(OP)
Hi SkipVought, im having an issue. Maybe you can help me out here. When i scrape the data. The very last page with the last payment gets captured twice and also captures other stuff that i dont need

here is an example. The values in red i dont really need.

03/11/15 XC 180.00- 71.20- 108.80- 15,069.78 <----From 2nd last page

03/11/15 XC 180.00- 71.20- 108.80- 15,069.78 <----Last page duplicate

2,434.77- 15,069.78

INTEREST OR PYMT BY STUDENT .52 F: 52.49 P: 27.03
OF CHARGE 0
AYOUT .30 PAYABL LE ON 04/16 /15
INT., ON MTH IN T., MONTHS 0.21 68.47 1
MENT DUE ATE /15



using this code

CODE -->

Dim A As Integer
    A = 0
    Dim B As Integer
    B = 0
    Dim C As Integer
    C = 0
    Dim D As Integer
    D = 0
    Dim E As Integer
    E = 0
    Dim F As Integer
    F = 0
    
      
       
'====Scrape Data from T328 screen====

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

RE: please help: Object variable or With block variable not set

This is why I asked you in some post, that you FIRST establish the screen navigation logic, like knowing what the THERE'S MORE message and the NO MORE message, and what to do under each of these conditions.

RE: please help: Object variable or With block variable not set

(OP)
The only navigation logic is pf7 and pf8. I just dont know how to capture the payments only screen on the last page without to extra stuff at the bottom.

RE: please help: Object variable or With block variable not set

Are you saying that your screen has no message area, so you NEVER know if there is MORE or NOT MORE???

RE: please help: Object variable or With block variable not set

CODE

Dim A As Integer
    A = 0
    
      
       
'====Scrape Data from T328 screen====

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

RE: please help: Object variable or With block variable not set

once you reach the last page and you evoke <PF8>, what happens? when does the word "TOTAL" appear?

RE: please help: Object variable or With block variable not set

(OP)
It appears on the last page for every customer. It does exit the loop when total appears, however, it also captures the data after Total which i dont need

RE: please help: Object variable or With block variable not set

Please post a screen copy of your last page and identify the rows you want.

RE: please help: Object variable or With block variable not set

(OP)

CODE -->

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 

I only want the row upto Totals including Interest and Principal. This is the last page. Totals is on line 12/2 in attachmate

RE: please help: Object variable or With block variable not set

So in the 'column' where you are getting the PROCESS DATE, when the value is TOTALS, get out of the write-to-Excel loop.

RE: please help: Object variable or With block variable not set

(OP)
yes. just to let you know that the "process date" row is in all the pages

RE: please help: Object variable or With block variable not set

If it were NOT on every page, I would be surprised!

RE: please help: Object variable or With block variable not set

(OP)
True. So do u have an idea what is required?

RE: please help: Object variable or With block variable not set

Quote:

So in the 'column' where you are getting the PROCESS DATE, when the value is TOTALS, get out of the write-to-Excel loop.

RE: please help: Object variable or With block variable not set

(OP)
doesnt the code above already do that?
am i missing code? cuz it still captures the other data below Totals which i dont want

RE: please help: Object variable or With block variable not set

No, your code read thru rows 10 - 21 ALWAYS, regardless if any one of those rows contains TOTALS in that column!

RE: please help: Object variable or With block variable not set

(OP)
ok i understand, but the pages previous require rows from 10-21

RE: please help: Object variable or With block variable not set

So are there TOTALS in the previous pages?

RE: please help: Object variable or With block variable not set

(OP)
No

RE: please help: Object variable or With block variable not set

what happens if you move

CODE

If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do 
to right after Do instead of after Next i

RE: please help: Object variable or With block variable not set

@remy, he would never read any of the last page.

Rather than SEARCH, he need to test the value in the For...Next loop FIRST and Exit fro that point.

RE: please help: Object variable or With block variable not set

(OP)
this seems to work somewhat, however, the value date shows up on its on at the bottom.

CODE -->

VALUE DATE	TYPE	AMOUNT	INTEREST	PRINCIPAL	PRINCIPAL OUTSTANDING

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

RE: please help: Object variable or With block variable not set

(OP)
current code so far

CODE -->

' 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 obj As Object
    Dim i
    Dim strCBName
    Dim blnHasMoreLines
    Set obj = GetObject("C:\Documents and Settings\MyFiles\Desktop\Projects\Ont Calc v1.0.xlsm") 'File is already open
 
    obj.Worksheets("Input").Cells(2, "A").Value = Sess0.Screen.GetString(4, 11, 1)  'DIV
    obj.Worksheets("Input").Cells(2, "B").Value = Sess0.Screen.GetString(4, 18, 6)  'LOAN
    obj.Worksheets("Input").Cells(2, "C").Value = Sess0.Screen.GetString(4, 34, 11) 'SIN
    obj.Worksheets("Input").Cells(2, "D").Value = Sess0.Screen.GetString(5, 2, 27)  'NAME
     
    Dim A As Integer
    A = 0
    Dim B As Integer
    B = 0
    Dim C As Integer
    C = 0
    Dim D As Integer
    D = 0
    Dim E As Integer
    E = 0
    Dim F As Integer
    F = 0
    
      
       
'====Scrape Data from T328 screen====

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

'Remove Dash from SIN
Sheets("Input").Range("C2").Replace "-", ""

'Hardcode SIN and Loan #
Sheets("Temp").Range("I3").Copy
Sheets("Temp").Range("I3").PasteSpecial Paste:=xlPasteValues
Sheets("Temp").Range("I4").Copy
Sheets("Temp").Range("I4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


End Sub 

RE: please help: Object variable or With block variable not set

First off, use ONLY ONE For...Next, as in my former example.

The first statement after For, must evaluate the TOTALS in the PROCESS DATE column. If it is TRUE, then exit The For and Do loops.

RE: please help: Object variable or With block variable not set

@Skip, I was thinking that if "TOTALS" was found, then it has to be the last page. then the script can do one final screenscraping, then exit. maybe something like this:

CODE

If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then 
        For i = 10 To 21
            obj.Worksheets("Input").Range("A5").Offset(A).Value = Sess0.Screen.GetString(i, 10, 9)
            obj.Worksheets("Input").Range("B5").Offset(A).Value = Sess0.Screen.GetString(i, 20, 7)
            obj.Worksheets("Input").Range("C5").Offset(A).Value = Sess0.Screen.GetString(i, 27, 11)
            obj.Worksheets("Input").Range("D5").Offset(A).Value = Sess0.Screen.GetString(i, 48, 11)
            obj.Worksheets("Input").Range("E5").Offset(A).Value = Sess0.Screen.GetString(i, 58, 11)
            obj.Worksheets("Input").Range("F5").Offset(A).Value = Sess0.Screen.GetString(i, 69, 11)
            A = A + 1
        Next I
exit sub 
end if 

but it that fails, I would grab the entire row and compare it to the previous row of data that was scraped.

RE: please help: Object variable or With block variable not set

But there's a problem there. The TOTALS, it appears, can be in rows 11-21.

So each row MUST be tested in the PROCESS DATE 'column.'

RE: please help: Object variable or With block variable not set

(OP)
Hey guys, this seems to work but the last payment gets captured twice in excel

CODE -->

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




CODE -->

Do

        For i = 10 To 21
            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
    MsgBox "Done" & strCBName
    Sess0.Screen.WaitHostQuiet (0) 

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

Okay?

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

(OP)
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

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

(OP)
because i have no knowledge of other ways.

RE: please help: Object variable or With block variable not set

Please read my request again and respond accordingly.

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

(OP)
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.

RE: please help: Object variable or With block variable not set

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.

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

How does TEXT beling in a DATE COLUMN???

RE: please help: Object variable or With block variable not set

(OP)
cuz thats the bottom of the last page

RE: please help: Object variable or With block variable not set

"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?

CODE

'
    Do

        For i = 10 To 21
            '
            If Trim(Sess0.Screen.GetString(i, 10, 9)) = "TOTALS" Then Exit For
            '
            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 

RE: please help: Object variable or With block variable not set

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.

CODE

'
    Do

        For i = 10 To 21
            '
            If Trim(Sess0.Screen.GetString(i, 10, 9)) = "TOTALS" Then Exit For
            '
            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
        
        'Searches for "TOTAL" on screen and exits loop
        If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do
        
        'Sends the next page command
        Sess0.Screen.SendKeys ("<Pf8>")
        Sess0.Screen.WaitHostQuiet (0)
   Loop 

RE: please help: Object variable or With block variable not set

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

RE: please help: Object variable or With block variable not set

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

So here's your screenshot of the last page
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 
 

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

RE: please help: Object variable or With block variable not set

(OP)
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.

RE: please help: Object variable or With block variable not set

I well then the example you posted is in error and I used your example as YRUE, so no wonder it does not work!

RE: please help: Object variable or With block variable not set

(OP)
Hey Skip, this code seems to be working fine

CODE -->

Do

'If Sess0.Screen.Search("TOTAL") = ("TOTAL")

        For i = 10 To 21
            If Sess0.Screen.GetString(11, 2, 5) = ("     ") Then Exit Do
             If Trim(Sess0.Screen.GetString(i, 2, 7)) = "TOTALS" Then Exit For
            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
           
            A = A + 1
        Next i
        If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do
            'Sends the next page command
            Sess0.Screen.SendKeys ("<Pf8>")
            Sess0.Screen.WaitHostQuiet (0)
   Loop
    MsgBox "Done" & strCBName
    Sess0.Screen.WaitHostQuiet (0) 

RE: please help: Object variable or With block variable not set

Wonderful!

RE: please help: Object variable or With block variable not set

(OP)
Hey Skip, thanks for your help. Really appreciate it. Just one question though. How can i scroll to top of page. I don't see any unique identifer

RE: please help: Object variable or With block variable not set

Are you saying that the page has more rows than the screen displays?

RE: please help: Object variable or With block variable not set

(OP)
yes

RE: please help: Object variable or With block variable not set

Wow!

Over a period of 40 years, at LTV, Northrup Grumman, Lockheed Martin and Bell/Textron, they ALL had at one time IBM Mainframe systems and used 3270 terminal emulators (Attachmate!Extra). They ALL used professionally programmed transaction based screens that painted 24 rows and F8 to get the next 24 rows, each screen had a MESSAGE AREA, that described the status of data on the screen.

So I have no idea what scrolling is all about on your screen. Your expert programmers ought to have included such information to the user community. Your system or your description of your system (I can't figure out which) is really funky! So I'm about tapped out.

RE: please help: Object variable or With block variable not set

(OP)
lol yea this is a old system. Anyways, thanks again

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close