Loading data with Windows mouse and keyboard macro software

Transactions with ALV grids and other Windows-based GUI plugins are out of reach for the traditional batch processing tools provided by SAP. If the transaction has been customized then there is a good chance that the data cannot be loaded with BAPIs or IDOCs provided by SAP.

Getting a developer to update or create tools to load legacy data into these transactions is often harder than getting developer access and permission to change or create the necessary tools. Here is where customized mouse and keyboard macros come into play as a solution of last resort.

Related content: For a much faster solution using Excel as a tool to load data directly into SAP, check out the free data automation suite of recyclable VBA code that comprises Saplicity.

Macro software for Windows simulates mouse movements, mouse clicks and keyboard strokes for the purpose of automating tasks and for testing automation. Creating custom macros as text files and launching them from SAP simulates an intern sitting at a computer entering the data, but without the whining or errors.

Finding the right Windows-based macro software

Most Windows macro solutions have proprietary file formats that cannot be edited or created in clear text. Furthermore, most do not allow for launching the program or macro from the command line or, if they do, it is only to load the macro into an editor--not to run the macro automatically.

The solution that seems to be the natural one based on functionality, features and price is Windows Script Host which is based on Visual Basic and comes free with modern versions of Microsoft Windows; it allows for very flexible keystroke emulation and mouse functions, too. This solution will be touched on first.

Keystrokes, not mouse clicks

Avoiding unreliable mouse movements and clicks by exclusively using features of the software and keyboard strokes will help to ensure that changes to screen size and resizing of windows will not cause the macros to fail. This whitepaper will not be discussing mouse commands.

Knitting the macro data together in Excel

In this example, the client gives access to neither LSMW nor MM17, so hundreds of Material Master Plant-specific Status values are updated using a keyboard macro solution. The first step is to create the beginning of the script file to include the ObjShell variable.

dim ObjShell :Set ObjShell = CreateObject("Wscript.Shell")

The ObjShell variable can be added easily enough in Excel, but it tends to be easier to put it in the top of the load file and then paste in the Excel content later. The Excel content tends to run along the lines of any other situation wherein embedded quotes in functions are doubled up and then concatenated with other steps.

For the purpose of this example, the SAPGUI is left on the initial screen of the MM02 transaction to save a couple of steps. Starting from the initial SAPGUI window is a good practice, but not necessary for simple tasks like this.

If cells A2 and B2 hold the Material Master number and the Plant respectively, then put this value in cell C2. It will trigger the macro software to focus on the MM02 SAPGUI window.

ObjShell.AppActivate("Change Material (Initial Screen)")

Unfortunately, changing focus to the SAPGUI window with the last Material Master highlighted unselects the Material Master number and moves the cursor to the end of the field making it necessary to remove the last Material Master number instead of replacing it by simply typing over it. A Ctrl-A (select all) prepares the field to be cleared by any keystrokes, so the value of cell D2 would look like this.

="ObjShell.SendKeys ""^a"&A2&"{ENTER}"""

Notice how in the field above, the Material Master number is typed immediately after the Ctrl-A and the Enter key is pressed right after that without any pause. This will make sense later.

The next keystroke is a basic Enter press and relies on the View(s) to be defaulted like this. Defaulting the view is a great way to avoid having to incorporate a lot of mouse movements and clicks in the macro.

This value would go into cell E2. It is not included in the cell D2 mentioned above because a pause will be injected between the keystrokes to allow the SAPGUI to progess to the next screen.

ObjShell.SendKeys "{ENTER}"

Next, this code is added to cell F2 so that the Plant value is pasted into the Plant field of the Organizational Levels screen. No Sales Org or Distribution Channel are required to update this MARC-MMSTA value, so none are provided.
="ObjShell.SendKeys """&B2&"{ENTER}"""

The final keystrokes in cell G2 cause the SAPGUI to tab through the Sales: General/Plant Data screen to the Plant-specific Status field, type in a 4 to replace the previous value and then click Ctrl-S to save the Material Master. No pauses are needed between any of these keystrokes and the ^s returns the SAPGUI screen to the MM02 initial screen with the current Material Master in the number field which is where the macro began.

ObjShell.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}4^s"

Carriage returns and pauses must be added between each of the script commands above. Add this value to cell D1 and be sure to include the carriage returns; they can be added by using Alt-Enter to include the carriage return and line feed (CRLF) characters instead of committing the contents of the cell and moving to another cell.

 
WScript.Sleep 7000

Knitting together each of these cells with pauses injected between is accomplished by concatenating the values with the ampersand (&) symbol in cell H2.

=D$1&C2&D$1&D2&D$1&E2&D$1&F2&D$1&G2

Now group the macro lines together in cell H1 to prevent unwanted carriage returns to be added. This will make sense when the content is pasted into Notepad.

=CONCAT(H2:H50)

Note: If the concatenated data is too long then the CONCAT function will throw a #VALUE! error. Consider breaking the loads into blocks of 50 records to get around this limitation.

Start notepad and open text file that ends with .vbs and has the ObjShell declaration mentioned above. Copy-paste the value from cell H1 to Notepad.

Because of the way that Excel puts data in the clipboard and Notepad's inability to import anything other than plain text, the pasted text will have an extra double-quote at the beginning of the pasted text and another at the end. Those extra two double quotes have to be removed and then, from the beginning of the file, a find-replace must be used to replace all double double-quotes ("") with single double-quotes (").

Alternatively, the MS Word program can be used the edit the VBS file with the ObjShell declared already. Copy-pasting the cells from the spreadsheet into MS Word doesn't add additional double-quote and can still be launched from the Command Prompt like this.

start winword test.vbs

Finally, ensure that one SAPGUI window is on the initial screen of the MM02 transaction with the Material Master number field blank and run the macro. The example above uses test.vbs as the name of the script file, so the command line to run this would look like this.

wscript test.vbs

A little experimenting may be required to get more complex macros to run. Switching between the focus between the Command Prompt and the screen's expected window title can be a great way to stop passing Enter keystrokes to the SAPGUI when one does not know how many warnings and therefore Enter keystrokes are required to complete a load on one transaction; the extra Enter keystrokes are passed harmlessly to the Command Prompt if the SAPGUI window title has changed back to some initial screen and the macro cannot focus back on it after it has focused on the Command Prompt.

While developing and testing macros using Windows Script Host, be sure to have the Task Manager running and be prepared to End Task the wscript.exe program if something goes wrong. The pauses can be made longer or shorter by changing the number of milliseconds in cell D1--they need to be longer for slower servers during peak business hours.

Using LSMW to create Macro Files

After a great deal of searching, this developer came across JitBit Macro Recorder. This product saves macros as easy-to-edit plain-text files and allows macros to be launched automatically by including the path to the macro file as a parameter of the program along with /a for automatic launch and /c to automatically close the program after the macro has finished.

JitBit wants a little too much for this software; a "Basic" version costs USD39 and the trial version does not support saving macro files or launching them from the command line without first buying a full version. This article is in no way meant to be an endorsement for Macro Recorder and the website is linked here reluctantly, but it does have the two features necessary for this task.

Starting with SAPGUI in its beginning screen of "SAP Easy Access" before running the macro is a great way to ensure that the macro software can find the window by title instead of relying on keystrokes or mouse clicks. At the end of the macro, there should be enough F3 keystrokes to ensure that SAPGUI returns to this screen so that the next time the macro is run, it can find the appropriate window without issue.

In the beginning of the macro below, the first lines make Windows focus on the "SAP Easy Access" window and then move the cursor to the Command Field. That means that, while the LSMW is running and launching the macro, another instance of SAPGUI must be open to the SAP Easy Access screen waiting for the macro software to take control.

By using the Ctrl-/ key combination, there is no need to rely on mouse movements and clicks to put the cursor in the Command Field. The transaction code includes the /n in case another program is running in the window, but it should be unnecessary.

Creating and exporting the custom macro is an easy task using an internal table and the GUI_DOWNLOAD function. Here the internal table is defined along with some work areas.

data:
 lWTY_RCLOBJ like WTY_RCLOBJ,
 lPNWTYH like PNWTYH,
 isLoaded(1) type c,
 nReturn type i.
data : begin of i_text occurs 0,
       text(1024) type c,
       end of i_text.

Populating the table starts with focusing the window on SAP Easy Access, focusing the cursor on the Command Field and then launching the transaction. Then the rest of the fields are populated in their turn.

* Check to see if the recall is already loaded.
isLoaded = ' '.
select * from PNWTYH into lPNWTYH
 where REFNO eq WTYS-REFNO_RCL.
  select * from WTY_RCLOBJ into lWTY_RCLOBJ
   where PNGUID_RCL eq lPNWTYH-PNGUID
     and RCLOBJ_LOW eq WTYS-RCLOBJ_LOW.
      isLoaded = 'X'.
  endselect.
endselect.
if isLoaded ne 'X'.  "Not loaded yet.
  refresh i_text.
  i_text = 'SWITCH TO WINDOW : SAP Easy Access : 0'. append i_text.
  i_text = 'DELAY : 200'. append i_text.
  i_text = 'Keyboard : ControlLeft : KeyDown'. append i_text.
  i_text = 'Keyboard : Divide : KeyPress'. append i_text.
  i_text = 'Keyboard : ControlLeft : KeyUp'. append i_text.
  i_text = 'DELAY : 199'. append i_text.
  i_text = 'TYPE TEXT : /nwtyrcl'. append i_text.
  i_text = 'DELAY : 100'. append i_text.
  i_text = 'Keyboard : Enter : KeyPress'. append i_text.
  i_text = 'DELAY : 1999'. append i_text.
  i_text = 'Keyboard : F4 : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Up : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Up : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Up : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Down : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Tab : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : Back : KeyPress'. append i_text.
  i_text = 'DELAY : 99'. append i_text.
  i_text = 'Keyboard : F5 : KeyPress'. append i_text. "Create document.
  i_text = 'DELAY : 1111'. append i_text.
  concatenate 'TYPE TEXT :' WTYS-REFNO_RCL into i_text
   separated by space. append i_text. "Reference recall number
  i_text = 'Keyboard : Tab : KeyPress'. append i_text.
  i_text = 'Keyboard : Tab : KeyPress'. append i_text.
  i_text = 'DELAY : 22'. append i_text.
[ Code skipped for brevity . . . ]
  i_text = 'DELAY : 22'. append i_text.
* Now F3 out of the transaction and press Enter when asked to save.
  i_text = 'Keyboard : F3 : KeyPress'. append i_text.
  i_text = 'DELAY : 22'. append i_text.
  i_text = 'Keyboard : Enter : KeyPress'. append i_text.
  i_text = 'DELAY : 1111'. append i_text.
* Press F3 twice more to return to SAP Easy Access
  i_text = 'Keyboard : F3 : KeyPress'. append i_text.
  i_text = 'DELAY : 111'. append i_text.
  i_text = 'Keyboard : F3 : KeyPress'. append i_text.

The internal table is now written out to a custom-fabricated MCR file. This will be file that the macro software loads and automatically runs.

  CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        FILENAME                        = 'C:\TEMP\SAPMACRO.MCR'
        FILETYPE                        = 'ASC'
         CODEPAGE                        = '4310'
        WRITE_FIELD_SEPARATOR = ' '
        HEADER = '00'
        TRUNC_TRAILING_BLANKS = 'X'
      TABLES
         DATA_TAB                       = i_text
      EXCEPTIONS
        FILE_WRITE_ERROR                = 1
        OTHERS                          = 2.

Finally, SAP launches a batch file that runs the macro software with the custom-fabricated macro file as a parameter. Then ABAP waits for 15 seconds to allow this macro to complete before starting the next record.

  CALL FUNCTION 'GUI_EXEC'
    EXPORTING
      COMMAND                           = 'C:\TEMP\SAPMACRO.BAT'
    IMPORTING
      RETURNCODE                        = nReturn.

  "Allow macro to complete before launching another session.
  wait up to 15 seconds.

endif. " isLoaded ne 'X'.

It is a good idea to thoroughly test the execution of the batch file from SAP using the Function Builder transaction (t-code SE37). It's a great place to let SAP know that it can launch this batch file without verification.

Here are the contents of the batch file that SAP calls in order to launch the macro. After the macro program finishes, the exit command closes the CMD/DOS Prompt.

REM ***** This is the batch file that is launched by SAP *****
"C:\Program Files (x86)\MacroRecorder\MacroRecorder.exe" C:\TEMP\SAPMACRO.MCR /a /c
exit

Learn some tricks . . .

Many SAP transactions are not stateless. For example, in MM01, if the Industry Sector and Material Type are defaulted to the same values as the Material Master that is going to be extended to a Plant, Sales Area or Warehouse then it won't throw the "Material type ready-made goods and industry Chemical industry copied from master record" warning and require an extra Enter key press to proceed.

Entering a Material Type that does not allow for Internal Number Ranges will set the default Material Type. This will keep extraneous Enter keystrokes from creating a new Material Master at the end of a MM01 transaction being used to copy values from one Material Master/Plant combination while extending a Material Master to a different Plant; this obviates the need to know how many Enter key presses are required.

Pressing F3 a few times usually gets the SAP interface back to the SAP Easy Access screen with the cursor waiting in the Command Field. It's sometimes better to start every transaction from scratch this way instead of using Ctrl-/ to move the cursor to the Command Field.

Equivalent of holding down the Enter key

Holding down the Enter key while running batches created in LSMW can be accomplished using the coffee cup method and allows each transaction to run to completion despite the random number of warnings and notifications that SAP throws. Keyboard macros can simulate the coffee cup method by continuously pressing the Enter key until a specific screen appears.

The AppActivate function returns a boolean value if it manages to focus on a window with the name passed to it as a parameter. This can be used to exit a loop used to run through a series of screens when the initial screen of the transaction appears at the end.

In this example, a Plant extension is being created by copying the values from another Material Master in the same Plant. The three quick Enter key presses gets passed the first few screens and then the Enter key is pressed every six seconds until the "Create Material (Initial Screen)" appears.

Sub wait()
	WScript.Sleep 1000
	ObjShell.SendKeys "{ENTER}"
	WScript.Sleep 1000
	ObjShell.SendKeys "{ENTER}"
	WScript.Sleep 1000
	ObjShell.SendKeys "{ENTER}"
	dim x
	do until x=22	
		if ObjShell.AppActivate("Create Material (Initial Screen)") then exit do
		ObjShell.SendKeys "{ENTER}"
		WScript.Sleep 6000
		x=x+1
	loop
End Sub

This function is called after passing in the Material Master numbers to the Initial screen, the Select View(s) screen and then passing in the Plants to the Organization Levels screen. The way to call the function is to simply add call wait() after these initial values.