×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Ojbect Defined Error

Ojbect Defined Error

Ojbect Defined Error

(OP)
I'm getting this error on this piece of code

CODE

ws.Cells(x, 1) = acct_num
What I'm trying to do it get data from a queue in Extra and put it into excel. I'm not taking anything from excel to start, just going through the queue and hit alt8 to move to the next account. I'm also not sure what loop and if/else statement I will need to use. There are multiple queues some are 100 accounts, some are 5000 accounts so I'm not sure how to determine the end of the queue. Code is below. Thanks

CODE

Sub queues()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        

  Dim ws As Worksheet
   Set ws = ActiveWorkbook.Sheets("Sheet1")


       acct_num = Sess0.Screen.GetString(3, 19, 10)
       ws.Cells(x, 1) = acct_num
       
       hold_date = Sess0.Screen.GetString(21, 26, 8)
       ws.Cells(x, 2) = hold_date
       
       hold_reason = Sess0.Screen.GetString(21, 44, 1)
       ws.Cells(x, 3) = hold_reason
       
       deman_exp = Sess0.Screen.GetString(4, 16, 8)
       ws.Cells(x, 4) = demand_exp
       
     
       
       Sess0.Screen.SendKeys ("<pf8>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       
          
End Sub

RE: Ojbect Defined Error


Hi,

Is this coded in Excel VBA?

What is the value of x when the error occurs?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ojbect Defined Error

(OP)
This is what the note I get when I hit the end of the queue.
END OF SELECTION QUEUE      
So I guess I would do a Do Until Loop until I get that message???

RE: Ojbect Defined Error

(OP)
X is empty, which I though would be the empty cell in Excel. And yes it's coded in Excel VBA.

RE: Ojbect Defined Error

(OP)
I just realized I'll need to go to the next cell down in the column too or it will just keep overwriting the same cell correct?

RE: Ojbect Defined Error

(OP)
I have it kind of, I can't get it to end correctly.

CODE

Sub queues()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        

  Dim ws As Worksheet
   Set ws = ActiveWorkbook.Sheets("Sheet1")

r = 2

Do

    r = r + 1
    
       acct_num = Sess0.Screen.GetString(3, 19, 10)
       ws.Cells(r, 1) = acct_num
       
       hold_date = Sess0.Screen.GetString(21, 26, 8)
       ws.Cells(r, 2) = hold_date
       
       hold_reason = Sess0.Screen.GetString(21, 44, 1)
       ws.Cells(r, 3) = hold_reason
       
       deman_exp = Sess0.Screen.GetString(4, 16, 8)
       ws.Cells(r, 4) = demand_exp
       
       unapp_funds = Sess0.Screen.GetString(12, 55, 13)
       ws.Cells(r, 5) = unapp_funds
        
       loan_type = Sess0.Screen.GetString(12, 14, 3)
       ws.Cells(r, 6) = loan_type
      
       
       Sess0.Screen.SendKeys ("<pf8>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       
Loop Until Sess0.Screen.GetString(24, 2) = "E"
       
          
End Sub

RE: Ojbect Defined Error



X is empty

CODE

       ws.Cells(x, 1) = acct_num
x is your sheet row number!!!!

No wonder you got an error!!!

 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ojbect Defined Error

(OP)
Never mind, I think I have it all. Thanks for your help.

RE: Ojbect Defined Error

(OP)
Another question on this. I have it all running but I have a slight problem. Updated codes is below. My issue is in the nested Do Until loop. This code here I mainly is what I need help on I think.

CODE

r = 1
                            Do
                            r = r + 1
Everything runs ok, the code jumps into and out of the loops as needed. Here is what happens. The code starts a queue, runs that queue and gets the data I need and puts it in excel in the rows & columns I need. Then at the end of that queue it backs out into another queue that's what PF3 does. Now when it's getting data in that queue it puts it over the data from the previous queue. I have no idea how many rows these queues will take up, some may be 800, some may be 5000. So is there a way to code this so that it goes to the next empty row or cell down from the previous one that has data? Thanks


CODE


Sub queues()
    Application.ScreenUpdating = False
    Dim Sessions, System As Object, Sess0 As Object
    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.Sessions
    Set Sess0 = System.ActiveSession
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")

    done = Sess0.Screen.GetString(24, 2, 3)
    queue = Sess0.Screen.GetString(2, 10, 11)

    If done <> "END" And queue <> "F0009 / 11" Then
    
        x = 1
            
            Do
            
                Sess0.Screen.MoveTo 6, 67
                Sess0.Screen.SendKeys ("f000")
                Sess0.Screen.SendKeys (x)
                Sess0.Screen.MoveTo 7, 67
                Sess0.Screen.SendKeys ("001")
                Sess0.Screen.SendKeys ("<enter>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                
              
                    
                    r = 1
                    
                       Do
                    
                        r = r + 1
                            
                            
                           acct_num = Sess0.Screen.GetString(3, 19, 10)
                           ws.Cells(r, 1) = acct_num
                           
                           hold_date = Sess0.Screen.GetString(21, 26, 8)
                           ws.Cells(r, 2) = hold_date
                           
                           hold_reason = Sess0.Screen.GetString(21, 44, 1)
                           ws.Cells(r, 3) = hold_reason
                           

                           
                           done2 = Sess0.Screen.GetString(24, 2, 3)
                           
                           Sess0.Screen.SendKeys ("<pf8>")
                           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                           
                    
                        Loop Until done2 = "END"
        
            Sess0.Screen.SendKeys ("<pf3>")
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            
            x = x + 1


        Loop Until x = 9
        
    End If

End Sub

RE: Ojbect Defined Error

(OP)
I think I have it, never mind.

RE: Ojbect Defined Error



Assuming that your Excel table is contiguous with A1...

CODE


                    r = 1

                    r = ws.cells(1,1).currentregion.rows.count + 1
                    
                       Do

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ojbect Defined Error

(OP)
Hmm, I didn't think of that. Here is what I did. Can you tell me what would be the positives/negatives of your way and my way?

CODE

If done <> "END" And queue <> "F0009 / 11" Then
    
        x = 1
        r = 1  ' moved r=1 from outside the loop    
            Do
            
                Sess0.Screen.MoveTo 6, 67
                Sess0.Screen.SendKeys ("f000")
                Sess0.Screen.SendKeys (x)
                Sess0.Screen.MoveTo 7, 67
                Sess0.Screen.SendKeys ("001")
                Sess0.Screen.SendKeys ("<enter>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                
              
                    
                       Do
                    
                        r = r + 1
                            
                            
                           acct_num = Sess0.Screen.GetString(3, 19, 10)
                           ws.Cells(r, 1) = acct_num

RE: Ojbect Defined Error




That would work, too.

I often have instances where an indeterminate number or rows might be added, which is why I use the technique I posted.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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