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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Ojbect Defined Error

Status
Not open for further replies.
ptw78

ptw78

Technical User
Mar 5, 2009
155
US
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
 
Sort by date Sort by votes

Hi,

Is this coded in Excel VBA?

What is the value of x when the error occurs?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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???
 
Upvote 0 Downvote
X is empty, which I though would be the empty cell in Excel. And yes it's coded in Excel VBA.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


[red]X is empty[/red]
Code: 
       ws.Cells(x, 1) = acct_num
x is your sheet row number!!!!

No wonder you got an error!!!



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Never mind, I think I have it all. Thanks for your help.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


Assuming that your Excel table is contiguous with A1...
Code: 
[s]
                    r = 1[/s]
                    r = ws.cells(1,1).currentregion.rows.count + 1
                    
                       Do

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Suresh02
  • Locked
  • Question
Automation error 'Specified module could not be found&quot;
Replies
0
Views
143
Suresh02
Suresh02
jschmidt1
  • Locked
  • Question
Extra! Basic Error Disk Full Error 1
Replies
17
Views
370
SkipVought
S
MarcMeg
  • Locked
  • Question
Object Error
Replies
8
Views
274
jeaneshe
jeaneshe
Eaglboy1
  • Locked
  • Question
Macro Compile Error
Replies
0
Views
101
Eaglboy1
Eaglboy1
VictorRavagnani
  • Locked
  • Question
Reduce error in screen copy due common asynchronous host connection using WaitHostQuiet(0) or other
Replies
5
Views
156
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top