Simple Excel to WRQ (IBM Reflection) and back....Question

JTL9161 · Dec 8, 2015

I have a simple macro that is taking a policy number from the Excel spreadsheet and copying to WRQ (IBM Emulator). After that it executes to search for the policy number in the database. Once it is found it goes in to copy the network number and bring it back to the Excel sheet. I got all that to work....my problem is that the selected cell on the spreadsheet is the policy number. I need the network number over 1 column and pasted there. Then move to the next line, previous column and get the next policy and so on till no more policies are found. I get and error when I try ActiveCell.Offset (1,0).activate, I get OBJECT REQUIRED. I have tried defining the Range at the beginning of the macro but nothing seems to want to work.

Any help would be appreciated.

James

Sub Optum3()
Dim excelApp As Object
Dim z As Integer
Set excelApp = CreateObject("Excel.Application")
Set excelApp = GetObject("C:\Users\Documents\DisCompare\MMDDYY-SGCS.XLSM")
z = excelApp.ActiveSheet.Range("B2").Copy
'excelApp.Visible = True
'Set wkBook = ExcelApp.Workbooks.Add
'wkBook.Sheets("sheet1").Activate
'z = excelApp.ActiveSheet.Range("B2").Copy
'Selection.Copy

With Session
SetMousePos 4, 4
.TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
.WaitForEvent rcEnterPos, "30", "0", 4, 4
.Paste
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 4, 4
.TransmitTerminalKey rcIBMDownKey
.TransmitTerminalKey rcIBMTabKey
.TransmitANSI "5"
.TransmitTerminalKey rcIBMEnterKey
.SetMousePos 17, 66
.TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
.WaitForEvent rcEnterPos, "30", "0", 17, 66
'.WaitForDisplayString "100.00", "30", 17, 50
.ExtendSelection rcSameRow, rcRightOneCol
.ExtendSelection rcSameRow, rcRightOneCol
.ExtendSelection rcSameRow, rcRightOneCol
.ExtendSelection rcSameRow, rcRightOneCol
.ExtendSelection rcSameRow, rcRightOneCol
.ExtendSelection rcSameRow, rcRightOneCol
.CopySelection

End With
excelApp.Sheets("sheet1").Select
ActiveCell.Offset(1, 0).Activate

OBJECT MISSING???
excelApp.Sheets("sheet1").Paste

End Sub

Jerry Sullivan · Dec 11, 2015

James, Here's some code for you to try. I've split out the terminal session steps as a separate procedure to make it easier to follow how the code loops through the values in Col B.

Code:

Sub LookupNetworkNumbers()
 Dim lRow As Long, lLastRow As Long
 Dim oMyWorkBook As Object
 Dim oMyWorkSheet As Object
 
 On Error Resume Next
 Set oMyWorkBook = GetObject("C:\Users\Documents\DisCompare\MMDDYY-SGCS.XLSM")
 Set oMyWorkSheet = oMyWorkBook.Sheets("sheet1")
 On Error GoTo 0
 
 If oMyWorkSheet Is Nothing Then
   MsgBox "Source workbook not found."
   Exit Sub
 End If

 With oMyWorkSheet
   .Activate
   
   '--find last row of data in col B. xlUp = -4162
   lLastRow = .Cells(.Rows.Count, "B").End(-4162).Row

   '--step through each row
   For lRow = 2 To lLastRow
      '--paste policy number onto clipboard
      .Cells(lRow, "B").Copy
      
      '--call procedure that will lookup corresponding network number
      '    and paste it to clipboard
      Call DoTerminalSession
      
      '--paste network number into columm C
      .Cells(lRow, "C").Select
      .Paste
   Next lRow
 End With

End Sub


Sub DoTerminalSession()
'--pastes value already on clipboard into WRQ session, then
'    lookup corresponding network number and paste it to clipboard
 With Session
   SetMousePos 4, 4
   .TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
   .WaitForEvent rcEnterPos, "30", "0", 4, 4
   .Paste
   .TransmitTerminalKey rcIBMEnterKey
   .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
   .WaitForEvent rcEnterPos, "30", "0", 4, 4
   .TransmitTerminalKey rcIBMDownKey
   .TransmitTerminalKey rcIBMTabKey
   .TransmitANSI "5"
   .TransmitTerminalKey rcIBMEnterKey
   .SetMousePos 17, 66
   .TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
   .WaitForEvent rcEnterPos, "30", "0", 17, 66
   '.WaitForDisplayString "100.00", "30", 17, 50
   .ExtendSelection rcSameRow, rcRightOneCol
   .ExtendSelection rcSameRow, rcRightOneCol
   .ExtendSelection rcSameRow, rcRightOneCol
   .ExtendSelection rcSameRow, rcRightOneCol
   .ExtendSelection rcSameRow, rcRightOneCol
   .ExtendSelection rcSameRow, rcRightOneCol
   .CopySelection
 End With
End Sub

JTL9161 · Dec 11, 2015

No problem...leaving shortly for company holiday party.

Thanks again.
James

JTL9161 · Dec 14, 2015

Jerry,

It works FANTASTIC!!! Just one last tweak. Need a line of code to know when it reaches the end of data in column "B" When there is no more policy numbers it still keeps going.

Thanks,
James

Jerry Sullivan · Dec 14, 2015

James, This line of code attempts to find the last row of data in Column B:

Code:

  '--find last row of data in col B. xlUp = -4162
   lLastRow = .Cells(.Rows.Count, "B").End(-4162).Row

You can check to see which row the code is finding as the last row by adding this Message:

Code:

  '--find last row of data in col B. xlUp = -4162
   lLastRow = .Cells(.Rows.Count, "B").End(-4162).Row
   MsbBox "Last Row: " & lLastRow

The last row the code finds may be greater than the last row of Policy Numbers if you have additional data below in Column B, or if you have formulas in Column B that evaluate to blanks (those are still considered "Data" by this method).

Let me know what you find in running a test with that message box and I can suggest a work around.

JTL9161 · Dec 15, 2015

Jerry,

Just ran a test with about 50 policies and it works great! Just needed to tweak some of the WRQ session code with timed-out issues and added a Erase End of Field to clear the policy number before pasting a new one since policies could be different length.

Thank you very much for all you help.

Take Care. Happy Holidays.
James

bklabel1 · Jul 3, 2017

I hope it is OK to wake up an old post.
I looked at the code from Jerry Sullivan. I understand the Excel part and after Session with the methods. The part I don't understand and it is not here is how to connect to the session.
Do I first use the code that looks something such as
Set Session = CreateObject(something2 for Unix)
Then
the commands that look something like:
.password
.username
.connect

I guess I don't follow how to connect and then start using session. I see examples of how to connect and then how to use session. I just don't see this together and I am missing information.

Thanks,

Kevin

Simple Excel to WRQ (IBM Reflection) and back....Question

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

Excel Facts

JTL9161

Well-known Member

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

bklabel1

Board Regular

Similar threads

Forum statistics

Share this page

Simple Excel to WRQ (IBM Reflection) and back....Question

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

Excel Facts

JTL9161

Well-known Member

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

bklabel1

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock