Tuesday 20 December 2016

More accurate automatic data selection entry tips

I’m using R98403G with the results from R9698711 and need to create 500 or so missing tables.  I’ve spoken about this previously.

This time however, I engaged my trust “sendKeys” vbs script to pound the data selection into the win32 client, and alas, the application no longer seems to allow the send keys functions to work!  Doh…

So, I’m going to use the browser, which does not suffer from the same depreciation in functionality.

I’ve had to modify my script a little bit to ensure that web data selection entry is going to work.

So, I have a spreadsheet which has all of the values that I want to put into data selection.  I’ve created a formula which is going to make this easy to put into my script.

image

You can see that my script is creating a massive list of comma delimited and strings in double quotes “

Cell B1 = =+""""&A1&""""&","

Cell B2 = =+B1&""""&A2&""""&","

The you can drag the cell B2 to the bottom of your data that you want to add to data selection.

Mine looks like:

("F00151","F0086","F01111","F01111Z1","F03B116T","F03B116W","F03B116X","F03B117T","F0451401","F05290T","F06116T","F06116TZ","F0618T","F063951T","F07051","F0717A","F07186T","F07352T","F08117","F08118","F08119","F08120","F08121","F08122","F08123","F08124","F08210","F08211","F08212","F08214","F0901T","F0902A","F0911T","F0911Z1T","F0917S","F1217Z2","F186407D","F186407T","F31172TE","F31B35T","F3214","F3294","F32941","F32942","F32943","F32944","F32945","F3294Z","F3296","F32961","F329611","F3296T","F40051","F4009T","F40242","F40344A","F4072TEM","F4074TEM","F41001T","F4100T","F4101SRM","F4101T","F4101TT","F4102SRM","F4102TT","F4109","F42005A","F42119A","F42119B","F4211A","F4211B","F42140A","F42150A","F4215T","F42199A","F42I010","F42I013","F42I015","F42I02","F42I03","F42I06","F42I07","F42I08","F42I09","F42I141","F42I20","F42I21","F4311TA","F43121TA","F43199A","F4600T","F4600TPU","F46011T","F46091T","F4611T","F4611TPU","F4620T","F470371","F470375T","F49090","F49711","F5102","F5103","F5104","F51F100","F51F10W","F51F110","F51F115","F51F120","F51F125","F51F20W","F51F30W","F51F40W","F51F90","F51F94","F51F95","F55029","F55031","F55058","F55059","F55326A","F55326T","F55415A","F55415T","F5543008","F55UI038","F55UI121","F55UI515","F56001","F56002","F56003","F56004","F56005","F5603B01","F5698210","F5698211","F56UI006","F570041","F570101B","F570401","F570401A","F570920","F574101X","F74401","F7460","F750401A","F75A001T","F75I101A","F7608B","F7613B","F76422","F76B012","F76B209","F76B410","F76C0006","F76CUI02","F894101","F894101H","F89701","F89702","F89AD020","F89AD050","F89AD060","F89CA020","F89CA050","F89CA060","F89CA080","F89CA65A","F89CB06C","F89LU001","F89LU003","F89LU005","F89LU006","F89LU007","F89LU008","F89LUADD","F90CB072","F90CB073","F952420","F986020","F986030","F98770","F98870","F98BLOB","F98EVHDR","F99302","F993022","F993023","F99303","FD3N0914","FD3N2053","FD3N4382","FY5AF0Z1","FY5AF402","FY5AF403","FY5AF405","FY5AF430","FY5AF431","FY5AF440","FY5AF455","FY5AF501","FY5AF510","FY5AF512","FY5AF513","FY5AF515","FY5AF517","FY5AF518","FY5AF519","FY5AF520","FY5AF521","FY5AF525","FY5AF540","FY5AF544","FY5AF547","FY5AF550","FY5AF570","FY5AF600","FY5AF610","FY5AF900","FY5AF905","FY5AF915","FY5AF919","FY5AF935","FY5AF940","FY5AF945","FY5AF950","FY5AFA01","FY5AFA02","FY5AFA03","FY5AFA04","FY5AFA05","FY5AFA06","FY5AFA07","FY5AFA08","FY5AFA09","FY5AFA10","FY5AFA11","FY5AFA12","FY5AFA13","FY5AFA14","FY5AFA15","FY5AFR01","FY5AFR02","FY5AFR03","FY5AFW01","FY5AFW03","FY5AFW05","FY5AFW06","FY5AFW09","FY5AFW10","FY5AFW11","FY5AFX01","FY5AFX02","FY5AFX03","FY5AFX04","FY5AFX05","FY5AFX06","FY5AFX07","FY5AFX08","FY5AFX09","FY5AFX10","FY5AG909","FY5AG90G","FY5AG90H","FY5AG90I","FY5AG910","FY5AG911","FY5AG912","FY5AG914","FY5AG921","FY5AG925","FY5AG935","FY5AG955","FY5AG995","FY5AGS01","FY5AGS02","FY5AGS03","FY5AGS04","FY5AGS05","FY5AGS06","FY5AGS07","FY5AGS08","FY5AGS09","FY5AGS0A","FY5AGS10","FY5AGS11","FY5AGS12","FY5AGS13","FY5AGS14","FY5AGS15","FY5AGS16","FY5AGS17","FY5AGS18","FY5AGS19","FY5AGS20","FY5AGS21","FY5AGS22","FY5AGS23","FY5AGS24","FY5AGS25","FY5AGS26","FY5AGS27","FY5AGS28","FY5AGS29","FY5AGS30","FY5AGS31","FY5AGS32","FY5AGS33","FY5AGS34","FY5AGS35","FY5AGS36","FY5AGS37","FY5AGW01","FY5AGW02","FY5AGW03","FY5AGX01","FY5AGX02","FY5AGX03","FY5AGX04","FY5AGX05","FY5AGX06","FY5AGX07","FY5AGX08","FY5AH010","FY5AH011","FY5AH014","FY5AH015","FY5AH016","FY5AH017","FY5AH018","FY5AH019","FY5AH020","FY5AH030","FY5AH040","FY5AH050","FY5AH055","FY5AH060","FY5AH061","FY5AH062","FY5AH070","FY5AH071","FY5AH080","FY5AH081","FY5AH085","FY5AH090","FY5AH095","FY5AH100","FY5AH160","FY5AH161","FY5AH30V","FY5AH30X","FY5AH425","FY5AH500","FY5AH50V","FY5AH50X","FY5AH510","FY5AH520","FY5AH530","FY5AH544","FY5AH61T","FY5AH900","FY5AH905","FY5AH910","FY5AH915","FY5AH921","FY5AH925","FY5AH951","FY5AHR01","FY5AHR02","FY5AHR03","FY5AHR2B","FY5AHR2C","FY5AHW01","FY5AHW02","FY5AHW03","FY5AHW04","FY5AHW05","FY5AHW06","FY5AHW07","FY5AHW72","FY5AHX01","FY5AHX02","FY5AHX03","FY5AHX04","FY5AHX05","FY5AHX06","FY5AHX07","FY5AHX08","FY5AHX09","FY5AHX10","FY5AHX11","FY5AHX12","FY5AHX13","FY5AHX21","FY5AHX22","FY5AHX23","FY5AHX24","FY5AHX25","FY5AHX26","FY5AHX27","FY5AHX28","FY5AHX29","FY5AM060","FY5AM080","FY5AZ015","FY5AZ020","FY5AZ025","FY5AZ028","FY5AZ35G","FY5AZ35L","FY5AZ800","FY5AZ900","FY5AZ923","FY5AZ924","FY5AZ927","FY5AZ935","FY5AZW01","FY5AZW02","FY5AZW04","FY5AZW05","FY5AZW07","FY5AZW08","FY5AZW09","FY5AZW10","FY5AZW11","FY5AZW12","FY5AZW13","FY5AZW14","FY5AZW15","FY5AZW16")

Take away the last , and replace with ) and add open ( at the start.

Now, the script template:

set objShell = wscript.createobject("WScript.Shell")
rem tableList=Array("F0101","F0411","F1208")

wscript.sleep 10000

for each table in tableList
    objshell.sendkeys table
    wscript.sleep 500
    objshell.sendkeys "^%(a)"
    wscript.sleep 1000
Next

You need to create a file like “enterDataSelection.vbs” and paste in the above script

So, if you go to your web browser and get the data selection, list of values screen ready.

image

With your cursor in the text box.

The script is going to type the first entry, wait for .5 second, then press ctrl alt a and wait 1 second then start again on the next item.

You can make this line as long as you need:

tableList=Array("F0101","F0411","F1208")

Note that you just need to replace the list in brackets with what you need to type.

If you then run the script (right click open) and then give your data selection window the focus (select the text box), the script will start typing / adding the elements.

Try it out with a small amount…

Extra for experts:

AppActivate is a useful function for this work, but the title (which you can use in AppActivate) for this purpose is important.

I really should have some code like the following at the start of my script, this would prevent my script from typing into the wrong window – but I’ve been struggling to find the complete window title…

Do until success = True
  Success = objshell.AppActivate("Batch Versions - Work With Batch Versions - Available Versions - Google")
  wscript.sleep 1000
Loop
wscript.sleep 100

But, you can get the titles with the below:

C:\Users\shannonm> Tasklist /V |findstr /C:"Batch"
chrome.exe                   10356 Console                    1    194,276 K Running         MITS\ShannonM                                           0:42:27 Batch Versions - Work With Batch Versions - Available Versions - Google
ApplicationFrameHost.exe     11800 Console                    1     32,784 K Running         MITS\ShannonM                                           0:00:06 Batch Versions - Work With Batch Versions - Available Versions ?- Micros

You can see from the above that the full title between Chrome and iexplore is very different.  And AppActivate does not work really with PIDs that well (for what we want to do).  Therefore we need to use AppActivate and also use the titles from the above.  Note that the Tasklist /V is very handy to give you all of the titles.

No comments: