Ojbect Defined Error

Question

Ojbect Defined Error

ptw78 (TechnicalUser)

(OP)

28 Jan 10 13:10

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

SkipVought (Programmer) · Answer 1 · 2010-01-28 13:44:06

Hi,

Is this coded in Excel VBA?

What is the value of x when the error occurs?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

ptw78 (TechnicalUser) · Answer 2 · 2010-01-28 13:46:11

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

ptw78 (TechnicalUser) · Answer 3 · 2010-01-28 13:51:03

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

ptw78 (TechnicalUser) · Answer 4 · 2010-01-28 14:01:30

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?

ptw78 (TechnicalUser) · Answer 5 · 2010-01-28 14:06:28

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

SkipVought (Programmer) · Answer 6 · 2010-01-28 14:38:59

X is empty

CODE

ws.Cells(x, 1) = acct_num

x is your sheet row number!!!!

No wonder you got an error!!!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

ptw78 (TechnicalUser) · Answer 7 · 2010-01-28 14:41:05

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

ptw78 (TechnicalUser) · Answer 8 · 2010-01-29 17:12:45

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

ptw78 (TechnicalUser) · Answer 9 · 2010-01-29 17:25:01

RE: Ojbect Defined Error

ptw78 (TechnicalUser)

(OP)

29 Jan 10 17:25

I think I have it, never mind.

SkipVought (Programmer) · Answer 10 · 2010-01-29 19:35:19

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

CODE

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

                       Do

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

ptw78 (TechnicalUser) · Answer 11 · 2010-01-29 22:13:55

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

SkipVought (Programmer) · Answer 12 · 2010-01-29 22:29:32

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,
_{Just traded in my old subtlety...
for a NUANCE!}

Come Join Us!

Posting Guidelines

Ojbect Defined Error