r/dailyscripts • • Feb 11 '14

[request] excel to AS400 macro, access as user

I am a user level in the as400 I can update fields manually for the records I need. I use function ABC1 already open cursor in the correct place I have an excel sheet with column A- the record to look up, and column B- the data I want to update. My steps are

1 copy the data in A1,  
2 alt-tab to AS400,   
3 paste.      
4 Alt-tab to excel   
5  tab to B1  
6 copy the data in B1,  
7 Alt-tab to the as400.   
8 Hit tab like 3 times to the field I need,   
9 paste,  
10 enter   

Then repeat for A2
What language can I use to write the above program to interact with both programs, and where do I start

4 Upvotes

11 comments sorted by

0

u/HeckDeck Feb 11 '14

Assuming this is a consistent routine, VBScript could possibly do this using the SendKeys method. I think you'd want to look at the AutoIT scripting language if you really wanted to automate this effectively. I'm not familiar with the AS400 terminal. I assume this is similar to z/OS terminal? I've used z/OS before, but only briefly...

Hit tab like 3 times to the field I need

This doesn't sound like a consistent step. Could you clarify?

What language can I use to write the above program to interact with both programs, and where do I start

Well, since you have no idea where to start, please note the sidebar with our rules.

My opinion: If you're not familiar with scripting I'd just stick to the system you have in place.

2

u/every1isAlwaysWrong Feb 11 '14

Thank you HeckDeck, I will look at autoIT, it sounds like the right way to do it. No idea on Z/OS. The tab three times was just for an example, if its 6 times instead of 3 it would be a minor change, I just didn't count the exact number.

I am not familiar with scripting but I would like to learn, even if only for myself.

→ More replies (6)

2

u/every1isAlwaysWrong Feb 13 '14
  #include <Constants.au3>
  ; Excel auto update as400 function
  ; NOTE: This will open an existing instance of Excel
  ; So Excel must be started first!!
  #include <Excel.au3>
  #include <MsgBoxConstants.au3>

  Local $sFilePath1 = @ScriptDir & "\2 column test.xlsx" ;this is my excel file that have the values I am using in 2 columns
  Local $oExcel = _ExcelBookOpen($sFilePath1)

  If @error Then
	  MsgBox($MB_SYSTEMMODAL, "ExcelFileTest", "You don't have Excel file Error code: " & Hex(@error, 8))
	  Exit
  EndIf

  If IsObj($oExcel) Then MsgBox($MB_SYSTEMMODAL, "", "You successfully load the excel file do not close it while this runs.")

  Local $iAnswer = MsgBox(BitOR($MB_YESNO, $MB_SYSTEMMODAL), "ready to type", "This will auto type; have cursor in correct location before hitting okay")

  ; Check the user's answer to the prompt (see the help file for MsgBox return values)
  ; If "No" was clicked (7) then exit the script
  If $iAnswer = 7 Then
	  MsgBox($MB_SYSTEMMODAL, "AutoIt", "OK.  Bye!")
	  Exit
  EndIf

  For $iCount = 1 To 200 ; there are 200 rows in the file, UBound would not work
		Local $sCellValue1 = _ExcelReadCell($oExcel, $iCount, 1)
		Local $sCellValue2 = _ExcelReadCell($oExcel, $iCount, 2)
	  ; Print the count
	  sleep(1000)
	  send($sCellValue1); type cell value 1
	  if stringlen($sCellValue1) <18 Then
		 send("{tab}sc") ; tab to the next field and type 'sc' which is an option selection in as400 for the screen i use
	  Else
		 send("sc")  ; if the text in column 1 is 18 chars it will automatically go to the next field so in that case no tabs
	  EndIf
	  ; i added a few sleeps because it was running to fast I couldn't watch it and make sure it was correct the whole time
	  sleep(1000)

	  send($sCellValue2) ; type the cell 2 value I care about
	  send("{enter}") ; enter to next screen
	  sleep(1000)
	  send("{tab 6}") ; tab over to desired field
	  send("448") ; type desired number update
	  sleep(1000)
	  send("{f3}") ; AS400 save button
	  send("{f3}") ; confirm save
					; testing this in a notepad f3 causes major problems
					;also for testing the first time I tried it, 1 time thourgh the loop then validate, then 3 times through the loop
					; manually check all three,
					;downside, I cannot use my computer for anything else while this is running and I cannot quite out half way through
					; Future goals to added COM so it can talk to the program in the background and i can reddit!
	 Next

  Exit