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 8, 2015

James,

Looks like that error is due to missing a reference to excelApp on that statement.

JTL9161 · Dec 9, 2015

Thank you for your response. Unfortunately it couldn’t be that simple. Now I am getting Run time error 424. Object doesn’t support this property or method.

excelApp.Sheets("sheet1").Select
excelApp.Sheets("sheet1").ActiveCell.Offset(0, 1).Select
excelApp.Sheets("sheet1").Paste

James

Jerry Sullivan · Dec 9, 2015

ActiveCell is a property of Application not the Sheet.

Try...

Code:

excelApp.ActiveCell.Offset(0, 1).Select

JTL9161 · Dec 9, 2015

Thanks. Now I get Runtime Error 438

excelApp.Sheets("sheet1").Select
excelApp.ActiveCell.Offset(0, 1).Select
excelApp.Sheets("sheet1").Paste

Jerry Sullivan · Dec 9, 2015

James, I was focused on the line that was throwing the error. I assumed excelApp was an Application object, but re-reading the front of your code, it appears that you subsequently assign a workbook object to that variable.

Let's step back to the big picture so that I can mock up something similar for testing.

From what application are you running this code?

Does that application's VB Project have a reference to a MS Excel Object Library?

Is an instance of Excel already running when you run the code? If so, is the workbook MMDDYY-SGCS.XLSM open or closed?

What type of object are you copying onto the Clipboard with Session.CopySelection?

JTL9161 · Dec 10, 2015

Ok like you said lets settle this down. I just thought it was so close to working I too was focused on the 1 line.

I open the Excel spreadsheet MMDDYY - SGCS. On it in column B are hundreds of policy numbers. I then open IBM Emulator WRQ Reflection. I open the macro library and run the Optum3 macro which is basically a screen scrape macro that from the start copies the 1st policy number in cell B2 of the spreadsheet and then pastes it in WRQ. Then the commands for WRQ take over and bring up that policy. Once its located it moves the cursor the network number, usually 5 or 6 characters and copies it. I am guessing to the clipboard but I am not sure about that. Now it goes back to the spreadsheet and the active cell is still B2 or the policy number. From this point I can paste the network number over the policy number, which isn't a big deal if I re-copy all the policy numbers to the "c" column. That would work but what I am trying to do is get the active cell to move down 1 to cell B3 and copy that policy and re-start the process over to WRQ again.

And then so on and so on till the end of the policy numbers. Cell B3, B4...etc.

Every time I run the macro I get some type of run time error with the ActiveCell.Offset. It doesn't matter where I put it in the macro.

Again, thanks for your help. If I can get this to work several people will be grateful because many of them have to look up policies manually to get the network numbers. I have taken all their spreadsheets and combined them into mine to try to get them the network numbers.

James

Jerry Sullivan · Dec 11, 2015

James, Your explanation helps clarify things- thanks. I see you are running this code from the VBA environment of the WRQ, and that your workbook, MMDDYY - SGCS.xlsm will already be open.

Since I don't have WRQ installed, I mocked up code to perform a copy and paste from MS Word to an Excel worksheet like the one you describe.
The worked for me to put the selected text in word into Cell C2.

Code:

Sub PasteFromWordToExcel()
 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 Session block
 '    .....your WRQ-specific code
 '    .....
 '    .....
 '    .CopySelection
 '  End With
 
 '--This line copies the MS Word selection to emulate the result of
 '    your Session block. Delete this line for use with WRQ
 Selection.Copy
 
 With oMyWorkSheet
   .Activate
   .Range("C2").Select
   .Paste
 End With

End Sub

Typically, it's better to assign values to the Value property of the Ranges directly without Activating > Selecting and Pasting to the Sheet. In this case, since we aren't sure of the data type of the Policy Number that's been copied to the Clipboard, the code above is a good next step.

Before moving on to stepping through all the Rows in Column B, I'd suggest trying to modify the code to read the text value from the Clipboard. Let me know how this works, and then I'll suggest some code that could improve upon this.

JTL9161 · Dec 11, 2015

I had to add a line of code (in red) there was no copying of the policy number to paste in WRQ.

Dim oMyWorkBook As Object
Dim oMyWorkSheet As Object
Dim z As Integer
On Error Resume Next
Set oMyWorkBook = GetObject("C:\Users\Documents\DisCompare\MMDDYY-SGCS.XLSM")
Set oMyWorkSheet = oMyWorkBook.Sheets("sheet1")
On Error GoTo 0
z = oMyWorkBook.ActiveSheet.Range("B2").Copy
If oMyWorkSheet Is Nothing Then
MsgBox "Source workbook not found."
Exit Sub
End If

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
With oMyWorkSheet
.Activate
.Range("C2").Select
.Paste
End With

This works perfectly but we are back to that nagging issue. After copying the network to "C2" I now need it to go to cell "B3" and copy the next policy...and so on and so on hence the need for the ActiveCell.Offset to keep the loop going until the end of data in column B.

Cell "B2" is the starting point but from there I need to offset 1 row after each paste to get the next policy so the loop continues until the end of data in column B.

James

Jerry Sullivan · Dec 11, 2015

JTL9161 said:
This works perfectly but we are back to that nagging issue. After copying the network to "C2" I now need it to go to cell "B3" and copy the next policy...and so on and so on hence the need for the ActiveCell.Offset to keep the loop going until the end of data in column B.

Cell "B2" is the starting point but from there I need to offset 1 row after each paste to get the next policy so the loop continues until the end of data in column B.

James, It isn't difficult to step through each row (and we don't need to use Offset to do it). I'm away from my computer at the moment, but I'll suggest some code later today.

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

JTL9161

Well-known Member

Excel Facts

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

JTL9161

Well-known Member

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

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