Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

please help: Object variable or With block variable not set

Status
Not open for further replies.
Biznez

Biznez

Technical User
Apr 9, 2015
106
CA
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
So in the 'column' where you are getting the PROCESS DATE, when the value is TOTALS, get out of the write-to-Excel loop.
 
Upvote 0 Downvote
yes. just to let you know that the "process date" row is in all the pages
 
Upvote 0 Downvote
True. So do u have an idea what is required?
 
Upvote 0 Downvote
doesnt the code above already do that?
am i missing code? cuz it still captures the other data below Totals which i dont want
 
Upvote 0 Downvote
No, your code read thru rows 10 - 21 ALWAYS, regardless if any one of those rows contains TOTALS in that column!
 
Upvote 0 Downvote
ok i understand, but the pages previous require rows from 10-21
 
Upvote 0 Downvote
what happens if you move
Code: 
If Sess0.Screen.Search("TOTAL") = ("TOTAL") Then Exit Do
to right after Do instead of after Next i
 
Upvote 0 Downvote
@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.
 
Upvote 0 Downvote
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
         	       	           	           	           	           
	       	           	           	           	           
[COLOR=#EF2929] 03/31/15[/color]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
@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.

 
Upvote 0 Downvote
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.'
 
Upvote 0 Downvote
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
         	       	           	           	           	           
          	       	           	           	           	           
[COLOR=#EF2929] 03/31/15	PA     	    331.48-	     0.68- 	    330.80-	36.96[/color]




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)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
391
SkipVought
S
Suresh02
  • Locked
  • Question
Automation error 'Specified module could not be found&quot;
Replies
0
Views
89
Suresh02
Suresh02
jeaneshe
  • Locked
  • Question
How do I select different Excel cells using a variable column loop?
Replies
10
Views
208
SkipVought
S
dhsd
  • Locked
  • Question
VBA code works fine in debug mode but not when I run it
Replies
5
Views
190
SkipVought
S
MarcMeg
  • Locked
  • Question
Object Error
Replies
8
Views
165
jeaneshe
jeaneshe

Part and Inventory Search

Sponsor

Back
Top