Pulling information from an Excel Sheet

Hello everyone,

So i'm not quite sure this specific question was answered when I was browsing related questions but...

Often we get an excel sheet with item numbers and their tax codes. I need to go into Rumba and change those items to the new tax codes.

The problem is, this list can get rather large, and inputting them manually can very extremely tedious.

Also note that there will be a group of items with one tax code, then the next group will have a different one.

I wanted to see if there is a way for RUMBA to extract this info from excel, and input it accordingly.

Currently, I just a simple script to change them one at a time.

While I was going to create a rather large script by copying and pasting the same formula to do them all, I was hoping there was another way seeing as there can be hundreds of items at a time.

For example: Item 1234567 is changing to tax code DG.

Sub Main
Dim RetC as Integer

EMConnect "A"

EMWaitX 1, RetC
EMSetCursor 3, 36
EMWaitX 1, RetC
EMSENDKEY "1234567" 'Insert Item Number
EMWaitX 1, RetC
EMSENDKEY "<FIELD_PLUS>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 5, RetC
EMSetCursor 9, 34
EMWaitX 1, RetC
EMSENDKEY "BG" 'Insert Tax Category
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"

End Sub

As always, thank you in advance.

#Rumba

Page 1 / 1

Hello everyone,

So i'm not quite sure this specific question was answered when I was browsing related questions but...

Often we get an excel sheet with item numbers and their tax codes. I need to go into Rumba and change those items to the new tax codes.

The problem is, this list can get rather large, and inputting them manually can very extremely tedious.

Also note that there will be a group of items with one tax code, then the next group will have a different one.

I wanted to see if there is a way for RUMBA to extract this info from excel, and input it accordingly.

Currently, I just a simple script to change them one at a time.

While I was going to create a rather large script by copying and pasting the same formula to do them all, I was hoping there was another way seeing as there can be hundreds of items at a time.

For example: Item 1234567 is changing to tax code DG.

Sub Main
Dim RetC as Integer

EMConnect "A"

EMWaitX 1, RetC
EMSetCursor 3, 36
EMWaitX 1, RetC
EMSENDKEY "1234567" 'Insert Item Number
EMWaitX 1, RetC
EMSENDKEY "<FIELD_PLUS>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 5, RetC
EMSetCursor 9, 34
EMWaitX 1, RetC
EMSENDKEY "BG" 'Insert Tax Category
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"

End Sub

As always, thank you in advance.

#Rumba

Hello ErikAllen,
that's right this kind of questions has been already discussed in different threads.
Anyhow,
Your code snippet doesn't contain any logic to handle your described scenario.

In Rumba 9.5 you have basically two option:

Option 1: RUMBA Script : Rumba controls the Excel spreadsheet.
Option 2: RUMBA VBA-Addon: Your Excel controls Rumba.

Both options work fine ,but option 2 is easier for MS Offce VBA Coder.They have the full power of Microsoft VBA and the code is in their Excel Spreadsheet.

Find attached a simple RumbaExcel Script example

3270_5F00_to_5F00_Excel_5F00_Basic.txt

Hello everyone,

So i'm not quite sure this specific question was answered when I was browsing related questions but...

Often we get an excel sheet with item numbers and their tax codes. I need to go into Rumba and change those items to the new tax codes.

The problem is, this list can get rather large, and inputting them manually can very extremely tedious.

Also note that there will be a group of items with one tax code, then the next group will have a different one.

I wanted to see if there is a way for RUMBA to extract this info from excel, and input it accordingly.

Currently, I just a simple script to change them one at a time.

While I was going to create a rather large script by copying and pasting the same formula to do them all, I was hoping there was another way seeing as there can be hundreds of items at a time.

For example: Item 1234567 is changing to tax code DG.

Sub Main
Dim RetC as Integer

EMConnect "A"

EMWaitX 1, RetC
EMSetCursor 3, 36
EMWaitX 1, RetC
EMSENDKEY "1234567" 'Insert Item Number
EMWaitX 1, RetC
EMSENDKEY "<FIELD_PLUS>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 5, RetC
EMSetCursor 9, 34
EMWaitX 1, RetC
EMSENDKEY "BG" 'Insert Tax Category
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"

End Sub

As always, thank you in advance.

#Rumba

hello @ndre, thank you for the input.
Your right it does not contain any logic for what I would like to do, but that is because I do not know how to do it, hence why I was asking for help.

The example you put in I use for a different script that opens a new excel sheet and inputs data.
What I need to do is grab data from an already existing excel sheet and input that into my code.

Example, there are two columns in this excel sheet, one for Item Number and one for Tax Category.
I need a script to input those values from the spread sheet into the script itself until the list is empty.

Hello everyone,

So i'm not quite sure this specific question was answered when I was browsing related questions but...

Often we get an excel sheet with item numbers and their tax codes. I need to go into Rumba and change those items to the new tax codes.

The problem is, this list can get rather large, and inputting them manually can very extremely tedious.

Also note that there will be a group of items with one tax code, then the next group will have a different one.

I wanted to see if there is a way for RUMBA to extract this info from excel, and input it accordingly.

Currently, I just a simple script to change them one at a time.

While I was going to create a rather large script by copying and pasting the same formula to do them all, I was hoping there was another way seeing as there can be hundreds of items at a time.

For example: Item 1234567 is changing to tax code DG.

Sub Main
Dim RetC as Integer

EMConnect "A"

EMWaitX 1, RetC
EMSetCursor 3, 36
EMWaitX 1, RetC
EMSENDKEY "1234567" 'Insert Item Number
EMWaitX 1, RetC
EMSENDKEY "<FIELD_PLUS>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 5, RetC
EMSetCursor 9, 34
EMWaitX 1, RetC
EMSENDKEY "BG" 'Insert Tax Category
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"
EMWaitX 1, RetC
EMSENDKEY "<ENTER>"

End Sub

As always, thank you in advance.

#Rumba

Hi ErikAllen,
if you need to open an existing Excel File just modify the example by using the Workbooks.Open method.

code snippet:

Set XLApp = CreateObject("Excel.Application")   ' Create an EXCEL application
Set XLBook = XLApp.Workbooks.Open("c:\\\\tmp\\\\test.xlsx")           ' Create an EXCEL Workbook
Set XLSheet = XLBook.Worksheets("Sheet1")       ' Create an EXCEL Worksheet
XLSheet.Activate                               ' Activate the Worksheet
XLSheet.Application.Visible = True               ' Show the EXCEL sheet

To read the excel cell values or any other method please take a look into the Excel VBA reference under:
https://docs.microsoft.com/en-us/office/vba/api/overview/excel for a complete reference.

In below Rumba thread you find an example to read the value from excel cell.

https://community.microfocus.com/microfocus/mainframe_solutions/rumba/f/52f94606-8317-4e71-a3ee-137fefbc9823/11537/how-to-paste-data-excel-to-rumba-from-a-particular-excel-cell

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded