VBS code to check adjacent fields

[Stildawn]

Hi All

I am try to automate a task at work, we use a program called Millman Reflection for UNIX. It used VBS etc for macros.

In this I have this screenshot:

As you see there are three columns, I want to change the letter in the middle column depending on the data in the left and right columns, here is my draft code so far:

Dim COO 
COO = InputBox("Enter in origin that qualifies for duty free") 
  
If **FIELD TO THE LEFT** = COO And **FIELD TO THE RIGHT** > 0 
        .Transmit "Q" 
Else 
        .Transmit "N" 
End If

My question is what can I put inside the **xyz** above, to get the script to check whats in the columns to the left and right of the middle column?

In excel this would be easy using the INDIRECT("RC[-1]",0) formulas but that doesnt work here?

Thanks in advance

 

[Stildawn]

Hi All

I have pretty much solved this and have written this code:

Sub Test()
' Generated by the Reflection Macro Recorder on 03-27-2013 12:36:51.07.
' Generated by Reflection for UNIX and Digital 9.0.
    On Error GoTo ErrorHandler
    With Session
    Dim COO As String
    COO = InputBox("Origin that is duty free")
    
    Dim COM As String
    Dim Duty As String
    COM = .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4)
    Duty = .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4)
    
    If COM = COO And Duty > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    Exit Sub
ErrorHandler:
    .MsgBox Err.Description, vbExclamation + vbOKOnly
    End With
    ' Recording stopped at 12:36:59.44.
End Sub

My issue now is that I want to run the IF statement on each row one after the other changing row using the .TransmitTerminalKey rcVtDownKey command above.

However I need the Dim COM & Dim Duty to update (using their .GetText commands) on each row as the cursor position changes.

Is there a way to insert a new scope within this Sub?

So I can leave out the Dim COO variable (as that will never change after initial input by user)

But then have Dim COM & Dim Duty change each time the IF statement is performed (on a different row each time).

Cheers

 

[Stildawn]

I tried removing the Dim COM & Dim Duty altogether and replaced with this code:

With Session
    Dim COO As String
    COO = InputBox("Origin that is duty free")
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    
    If .GetText(CursorRow, CursorColumn - 5, CursorRow, CursorColumn - 4) = COO And .GetText(CursorRow, CursorColumn + 2, CursorRow, CursorColumn + 4) > 0 Then
    .Transmit "Q"
    Else
    .Transmit "N"
    End If
    
    .TransmitTerminalKey rcVtDownKey
    Exit Sub

Which as you can see is ment to just go down a row then repeat the IF statement based on the new Cursor position, however it doesnt work and it looks like its still using the same .GetText strings from the very first IF statement???
I dont understand why it is though, after the .TransmitTerminalKey rcVtDownKey the cursor is in a different position so should run to get the correct strings back?

 

[schielrn]

Assuming your cursor is in the first spot of the middle field and there is one space in between the fields, this would return the value that is in the left field and the right field. You can expand on it more for your needs, but it looks like you may have accomplished what you were after since you sent me a message:

Dim fieldLengthCurrentField As Integer, fieldLengthRightField As Integer, fieldLengthLeftField As Integer
Dim startColumnCurrentField As Integer, startColumnRightField As Integer, startColumnLeftField As Integer
Dim rightField As String, leftField As String
With Session
    .FindField .CursorRow, .CursorColumn, rcCurrent, rcAnyField
    fieldLengthCurrentField = .FoundFieldLength
    startColumnCurrentField = .FoundFieldStartColumn
    .FindField .CursorRow, .CursorColumn + fieldLengthCurrentField + 1, rcCurrent, rcAnyField
    fieldLengthRightField = .FoundFieldLength
    startColumnRightField = .FoundFieldStartColumn
    .FindField .CursorRow, .CursorColumn - 2, rcCurrent, rcAnyField
    fieldLengthLeftField = .FoundFieldLength
    startColumnLeftField = .FoundFieldStartColumn
    rightField = Trim(.GetDisplayText(.CursorRow, startColumnRightField, fieldLengthRightField))
    leftField = Trim(.GetDisplayText(.CursorRow, startColumnLeftField, fieldLengthLeftField))
    MsgBox "Right field = " & rightField & vbCrLf & "Left field = " & leftField
End With

Hope this may help some as this is what I use to find values in the column to the left or right. I have also just used the transmitterminalkey for a tab and a shift tab to just grab the values of those fields as well if that makes sense.

 

[Stildawn]

I have semi solved this with the below code:

With Session
    Dim COO As String
    COO = InputBox("Origin that is duty free")
    Dim COM As String
    Dim Duty As String
    Dim I As Integer
    For I = 0 To 13
        COM = .GetText(CursorRow + I, CursorColumn - 5, CursorRow + I, CursorColumn - 4)
        Duty = .GetText(CursorRow + I, CursorColumn + 2, CursorRow + I, CursorColumn + 4)
        If COM = COO And Duty > 0 Then
            .Transmit "Q"
        Else
            .Transmit "N"
        End If
        .TransmitTerminalKey rcVtDownKey
    Next I
    Exit Sub

This code will perform correctly for 14 rows, however I have run into another problem, on this screen in Reflection, it will only display 14 rows max on the screen, however if you down key down it will continue (depending on how many rows of data has been entered).

Of course the code above works of the displayed number of rows, so the max I can run it for is 0 to 13 (14 rows).

I have tried to add a whole bunch of down keys and then up keys at the end of this code to refresh the display (bringing up the next 14 rows and returning the cursor to the start of this next 14 row block, however if I run the above code again after this it doesn't work and just replicates the results of the first run.

This is the code I have tried that doesn't work.

With Session
    Dim COO As String
    COO = InputBox("Origin that is duty free")
    Dim COM As String
    Dim Duty As String
    Dim I As Integer
    For I = 0 To 13
        COM = .GetText(CursorRow + I, CursorColumn - 5, CursorRow + I, CursorColumn - 4)
        Duty = .GetText(CursorRow + I, CursorColumn + 2, CursorRow + I, CursorColumn + 4)
        If COM = COO And Duty > 0 Then
            .Transmit "Q"
        Else
            .Transmit "N"
        End If
        .TransmitTerminalKey rcVtDownKey
    Next I

.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey
.TransmitTerminalKey rcVtDownKey

.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey
.TransmitTerminalKey rcVtUpKey

For I = 0 To 13
        COM = .GetText(CursorRow + I, CursorColumn - 5, CursorRow + I, CursorColumn - 4)
        Duty = .GetText(CursorRow + I, CursorColumn + 2, CursorRow + I, CursorColumn + 4)
        If COM = COO And Duty > 0 Then
            .Transmit "Q"
        Else
            .Transmit "N"
        End If
        .TransmitTerminalKey rcVtDownKey
    Next I

    Exit Sub

Then doesn't work (even though I don't understand why)

Any ideas?

Thanks