Thursday 6 October 2011

Data selection–List of values–speed entry!

Ever had a huge list of tables or programs or something that you wanted to automatically enter into a UBE’s data selection?  I know that I have many times.  Say I run the R9898711 and then want to run the R98403 to create all of the missing tables?  Well, I have a time saving suite for you.

step 1: Run the UBE to create CSV, so it’s easy to get a list of values that you want to enter into data selection.

step 2: use this formulae to create the appropriate “sendkeys” functions:  =+CONCATENATE("objshell.sendkeys """,A3, "~","""")

step 3.  populate column 1 of a spreadsheet with the list of items, and column 2 with the formula above

image

step 4. create a file called “Dataselection.vbs” on your computer and edit that file

step 5. paste in the following header (for B9)

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  'Success = objshell.AppActivate("something.txt - notepad")
  wscript.sleep 1000
Loop

wscript.sleep 100
wscript.echo "Start data pump"
wscript.sleep 100
Success = objshell.AppActivate("List Of Values")
objshell.sendkeys "+{tab}+{tab}"

For XE

set objShell = wscript.createobject("WScript.Shell")

Do until success = True
  Success = objshell.AppActivate("List Of Values")
  wscript.sleep 1000
Loop
wscript.sleep 100

step 6. paste column B from above underneath this header

objshell.sendkeys "F00021~"

objshell.sendkeys "F0010T~"

objshell.sendkeys "F0095~"

objshell.sendkeys "F01161DW~"

step 7.  save the file and get a command window

step 8. run “wscript.exe //e:vbscript Dataselection.vbs”

step 9.  Activeate the data selection, list of values screen that you want to add your values to

step 10.  The script will fire when the window is active and will populate the list!

 

Enjoy!

4 comments:

K--->tan said...

Hi,
When I tried to run the script I am getting an error, script not correct. Pl advise.

Regards,
Ketan

Shannon Moir said...

Hi Ketan,
The scripts are really funny about where you leave the mouse / cursor after you've left the data selection screen and run the VBS script. You might need to mess around with adding a tab or a reverse tab to ensure that you are in the data entry window.

If you see you data selection window being activated, it's probably typing the values into the wrong location because of the default cursor location. I find that this can change between . releases of tools!

Good luck,
Shannon

Shane Delon said...

Thanks for this Shannon, this is awesome! One note, in 9.1.4.4 there is a bug I've found while trying this in that when you reverse(i.e. )-TAB in the data selection list of values window it "shows" the field that is only supposed to show in the "Range of Values" tab. Since it displays this field it reverse tabs and starts entering data into this field and you end up with a bunch of garbage in your "list of values" field along with some of the data you intended to enter. The fix for this is to use the following:

objshell.sendkeys "+{TAB}+{TAB}+{TAB}+{TAB}+{TAB}"

With the 5 reverse tabs (and no less) it put's you in the correct field...I'm not sure why the window won't let you forward tab when made active by the vbs script but it will let you reverse tab.

Shane said...

There was supposed to be a "shift" after the "i.e." but it was lost because of the surrounding characters I had it in.