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


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?
 
Upvote 0 Downvote
Hi SkipVought, i want the values in column E started at E2
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hey SkipVought, want to thank you for your help. I decided to use Do statement instead and it works fine
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Are you saying that your screen has no message area, so you NEVER know if there is MORE or NOT MORE???
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
once you reach the last page and you evoke <PF8>, what happens? when does the word "TOTAL" appear?
 
Upvote 0 Downvote
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
 
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