VBA to reactivate SAP screen

[Raptor6L]

Hi Everyone

I'm creating a spreadsheet that will create and issue reservations in our SAP ERP system.

The amount of materials that will be in the created reservation is variable so I need to do an if formula or a loop of some kind, so it knows when to end and hit the save button (instead of next material button) to create the reservation. That part is not som much the problem.

Where I'm coming unstuck is after the details for the first material is populated in SAP, the macro then shifts focus back to excel to see if it should save or if it should continue, then when it shifts back to SAP the screen, it is no longer active. The SAP screen is in the foreground, and the last "cell" is highlighted, but SAP requires a mouse click in a "cell" to become active again.

I've Googled around and found some people have found a way but they don't say how, and haven't responded to PM's or replies to their posts.

Does anyone here have experience with SAP? Specifically reactivating the current screen?


Thanks
Chris

 

[Raptor6L]

No replies, so a bit more info here:


I have the T-Codes for the SAP cells that need to be filled in, So I'm just directing those to the Excel row and column and it copying those co-ordinates.
Example:


Dim App, Connection, session As Object
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
Set Connection = App.Children(0)
Set session = Connection.Children(0)


session.findById("wnd[0]").resizeWorkingPane 228, 35, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb21" 'transaction
session.findById("wnd[0]").sendVKey 0


session.findById("wnd[0]/usr/ctxtRM07M-BWART").Text = Cells(2, 4).Value 'movement type


session.findById("wnd[0]").sendVKey 8


session.findById("wnd[1]/usr/txtRKPF-WEMPF").Text = Cells(1, 6).Value 'recipient
session.findById("wnd[1]/usr/subBLOCK:SAPLKACB:1001/ctxtCOBL-KOSTL").Text = Cells(2, 2).Value 'cost centre


session.findById("wnd[0]").sendVKey 0


'Material data
session.findById("wnd[0]/usr/ctxtRESB-MATNR").Text = Cells(2, 1).Value 'material number
session.findById("wnd[0]/usr/txtRESB-ERFMG").Text = Cells(2, 3).Value 'amount
session.findById("wnd[0]/usr/ctxtRESB-LGORT").Text = "U801" 'plant
session.findById("wnd[0]/usr/ctxtRESB-LGORT").Text = "0100" 'sloc
session.findById("wnd[0]/usr/txtRESB-ABLAD").Text = Cells(1, 7).Value 'unloading point
Everything before "Material data" is a one off entry and won't be repeated.
The 5 Values after that will be repeat over and over until the last material has been reached.
That could be just the once, or it could be 24 times, or more. It's very much a variable.


On each occasion, after the "unloading point" value has been entered, Excel is looked at to see if it is the last material or not.
If it is, SAP needs to be re-activated to press the "save" button.
If it isn't, SAP needs to be reactivated to press the "next" button, which will show a new page and refill in the above 5 values with the next material's data..


I hope that makes sense.


On this forum is found this LINK
Just figured it out- Z = session.findById("wnd[0]").Text returns the Text Name of the session.
Unfortunately, plwhittington doesn't show an example of how he uses that info, and he hasn't responded to my PM's or reply post, but I know from that thread he's using AppActivate with the text name of the session which sounds like what I'm trying to achieve.


I'm not in IT, and I'm most definitely not a VBA\ Excel or SAP guru, so I'm left unsure of how to use incomplete stuff like this.
Does this look familiar to anyone?
Anyone here have experience in reactivating a SAP window?


Thanks
Chris

 

[Raptor6L]

Sorry all
I was trying to make that post a lot neater with code tags etc etc, but the site posted it up instead. and for some reason, I cannot edit it.

 

[Raptor6L]

All

Solved this problem.

Firstly, I wasn't using AppActivate correctly, but once I found how to use it correctly, I still wasn't able to activate the screen as desired.

So, the problem is mainly because I was taking over an existing SAP screen, which is what this T-code is doing:

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb21"

I found the problem with this is when SAP taking over an existing screen this way, it doesn't fully change the name of the screen with it.
So, the screen might have originally had the MIGO "Goods Movement" transaction on it. I would run the above T-code changing that screen to MB21 "Create Reservation". The screen would change to MB21 and function as MB21 but the actually name of the screen remains MIGO.

I was trying to run AppActivate (once I learned how to use it properly) to activate "Create Reservation" and of course, it wouldn't work.
For this sheet to be functional for all users, I had to find another way as I would have no idea what transaction would be on the screen that the script takes control of.

So instead of taking over an existing screen, I now open MB21 in a new window using the IE page address:

Above all subs:

Option Explicit
Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As LongPtr
Private Const NAV_CR_RES As String = "webaddresshere"

Then within sub that fires up SAP:

' make IE connection
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate NAV_CR_RES
ShowWindow IE.hWnd, vbMaximizedFocus

I've created several Private Const Strings each with a different web address for the different transactions needed and created a new Sub for each of them.

Now I know what the screen is going to be called so it can be controlled correctly.
As a bonus, even when I change transactions with the same screen, I still know what it will be called, so can use AppActiviate to call it.

The only caveat is if there is more than one instance of the same screen name open, the script tends to want to connect to the first instance.
To counter that, I have put a warning (MsgBox) at the beginning asking users to close any open instances of the transactions about to be used in the spreadsheet, and I've obviously listed those transactions for him\ her\them.


Thanks Chris - you're a legend!

Chris