Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code help (2002)

    (2, 17 being the row and column number). The input is 16 digits from (2, 17) to (2, 33), being a string of 17, with a prohibited space at (2, 23)

    pcommPS.SetText (ActiveCell.Offset(0, 0)), 2, 17

    This does not work due to the prohibited single space in the "presentation space(pcommPS)".

    Therefore I need:

    pcommPS.SetText (ActiveCell.Offset(0, 0)), 2, 17 Left 6 characters of the active cell

    &

    pcommPS.SetText (ActiveCell.Offset(0, 0)), 2, 24 Right 10 characters of the active cell

    But I can't get it right. Help please.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    What is pcommPS.SetText? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    PCOMM autECLPS Automation Object 1.0 Library

    Hopefully, the attached may help.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    Does this do what you want?

    pcommPS.SetText (Left(ActiveCell, 6)), 2, 17
    pcommPS.SetText (Right(ActiveCell, 10)), 2, 24

  5. #5

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    I'm getting Run-Time Error 424 (Object required) on the following:

    Format(ActiveCell.Offset(0, 3), "#,##0.00") = pcommPS.GetText(3, 50, 11)

    Any idea's?

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    You can't assign a value to Format. Format takes a value and converts it to a formatted string. Perhaps this?

    ActiveCell.Offset(0, 3) = pcommPS.GetText(3, 50, 11)
    ActiveCell.NumberFormat = "#,##0.00"

    or

    ActiveCell.Offset(0, 3) = Format(pcommPS.GetText(3, 50, 11), "#,##.00")

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    Thanks.
    I need to create a few loops to check the correct screen is active, if not, go to the correct screen, something like:

    If pcommPS.GetText(1, 26, 19) <> "SERVICE WORK SCREEN" Then
    pcommPS.SendKeys "[f5]" 'which ****should**** select the correct screen, but if not???????
    Do
    Loop Until pcommPS.GetText(1, 26, 19) <> "SERVICE WORK SCREEN"
    End If

    Will this continuously loop? At what point can i make it exit if the correct screen cannot be located. Would it be worth adding an error msgbox?

    Thoughts?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    Only YOU can tell after how much time you should decide that the screen hasn't loaded, there is no way for others to know that.

    I would place a DoEvents instruction in the loop. This instruction gives processor time to other applications so that they have time to do something. And you could experiment with limiting the number of tries:

    Const MaxLoops = 10 ' try different values
    Dim intLoop As Integer

    For intLoop = 1 to MaxLoops
    pcommPS.SendKeys "[f5]"
    DoEvents
    If pcommPS.GetText(1, 26, 19) = "SERVICE WORK SCREEN" Then Exit For
    Next intLoop

    If intLoop = MaxLoops + 1 Then
    MsgBox "Failed to load the service work screen!", vbExclamation
    Exit Sub
    End If

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    Thanks Hans.
    I have managed to get this to work (perfectly [touchwood]) for a single item: Using active cell (account no), input to app, and retreive 2 sets of data to active cell offset 3 & 4.

    I need to be able to loop through column J, and do this for all, only where J contains a 16 digit string of numbers, returning data to M&N.
    Once "service work screen" is found, it's just the case of: input acc no, output name and balance, then move on to the next. I'm not sure how to construct this properly, so would appreciate any help. Thanks.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    Please keep in mind that we don't have the slightest idea what you're actually doing - we don't know the software you're using or the workbook. You see it in front of you, we're in the blind. Please try to explain CLEARLY what your question has to do with the code you attached.

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    Sorry if I am not clear, I understand that you do not know the software, all the code for communicating is there, it is only the loop that I am stuck on. I have marked out in the new attachment the area that I need to loop.

    something like:

    If pcommPS.GetText(1, 26, 19) = "SERVICE WORK SCREEN" Then

    For each instance of a 16 digit string in column J, from bottom-most up to J2 >

    pcommPS.SetText (Left(J***, 6)), 2, 17
    pcommPS.SetText (Right(J***, 10)), 2, 24

    pcommPS.SendKeys "[Enter]"
    pcommOIA.WaitForAppAvailable 5
    pcommOIA.WaitForInputReady

    J***.Offset(0, 3) = pcommPS.GetText(3, 50, 11) 'Get account balance
    J***.Offset(0, 4) = pcommPS.GetText(3, 2, 31) 'Get cardholder name

    pcommOIA.WaitForAppAvailable 5
    pcommOIA.WaitForInputReady

    Else
    MsgBox "Unable to access 'SERVICE WORK SCREEN', please try again.", vbExclamation
    Exit Sub
    End If
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code help (2002)

    Does this do whay you want?

    '******************************************** LOOP START (I THINK)
    Dim r As Long
    Dim m As Long
    ' Last row
    m = Range("J" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
    If pcommPS.GetText(1, 26, 19) = "SERVICE WORK SCREEN" Then
    If Len(Range("J" & r)) = 16 Then
    pcommPS.SetText (Left(Range("J" & r), 6)), 2, 17
    pcommPS.SetText (Right(Range("J" & r), 10)), 2, 24

    pcommPS.SendKeys "<!t>[Enter]<!/t>"

    pcommOIA.WaitForAppAvailable 5
    pcommOIA.WaitForInputReady

    Range("M" & r) = pcommPS.GetText(3, 50, 11)
    Range("N" & r) = pcommPS.GetText(3, 2, 31)

    pcommOIA.WaitForAppAvailable 5
    pcommOIA.WaitForInputReady
    End If
    Else
    MsgBox "Unable to access 'SERVICE WORK SCREEN', please try again.", vbExclamation
    Exit Sub
    End If
    Next r
    '************************************************* ******** LOOP END

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (2002)

    I will check tomorrow and let you know. Thanks for your help!!

    Added 16.11.08:
    Hans, it does work perfectly with a few additional timing adjustments. I am now able to both "add data to" and "get data from" the ibm software, using excel alone, which is our primary work tool. The possibilities are endless!!!!
    I could not have done it without your help. Thanks again for both your expertise and your patience!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •