Focus on SAP window using VBA

[Former Member]

Hi Im new here.

Can somebody help me with excel and SAP junction through VBA? I beg for help with focusing on another window during script being processed. Its SAP window. Its SAP ARP - transaction CO02 - change of order - adding an attachment. Im in the final step where small import window pops up and I just need to insert or search for the path of the file I want to add. Its kind of microsoft window - on the left are large buttons with desktop, documents and 2-3 more. But its called Import on the left upper corner. I want to use the sendkeys prompt - the path will be allways the same eg C:\File\xxx.pdf. So I thought it will be very easy just call the macro recorded in sap until the window shows, then sendkeys "C:\File\xxx.pdf" then continue with SAP macro to okay it and save - end.

Problem is when the window show and I sendkeys - it does not write anything in path box. When i run the macro until the window shows and type something on kb it write normally in the box.

Can somebody help me please with this? Ive tried an application"focus" ("microsoft") prompt (quotes are for I cant remember the exact phrase I dont have the excel and file with macro here or SAP) It does not help... maybe because its not windows window but SAP window.

Windows 7, Office 2007, work computer - so I dont have access to all OS features, but I think it does not matter. 

If somebody can tell me how the prompt is called in sap, then it will be easy: Something like: findbyID.......importwindow.input.text = the way to the file...

:

Thanks very much for your replies.

Mirek

[former_member213011 · Accepted solution]

Dear Mirek,

The import dialog shell has been known to halt scripting process for both VBA and VBS until it gets an input either manually or from sendkeys sent from a different calling script.

This matter has been discussed in length with some workaround options here: http://scn.sap.com/thread/3448546 and here: http://scn.sap.com/thread/1799453

Thanks,

Sayuti

[Former Member · Accepted solution]

Hi thanks for your reply.
From what Ive read it is possible but using second created vbs file... I dont have experience with creating vbs files but learn it.

However I just dont understand why it must be done through 2 scripts - why it couldnt be joined together? Probably why the command appactivate "Import file" just dont do the focus?  If we know the window name it should select it - and when it is open - cursor is where we need to have it - so just type... but nothing.

[Former Member · Accepted solution]

Note:

Now Im playing with it again. So now I understand that I ..just maybe... need run macro to the

session.findById("wnd[0]/shellcont/shell").selectContextMenuItem "PCATTA_CREA"

then end the original macro and run another with

AppActivate "Import file"

SendKeys "HEJ", True

It works.

So now I need: macro run - until the import file shows - set some kind of timer - 1sec to run another sub during one sec end the original macro... and then the second sub will be started... I know it is stupidly written but it is what  basicaly Scriptman done 3 years ago in that thread? Am I right?

[former_member213011 · Accepted solution]

I'm not sure whether it will work with Excel 2007, but I've already attempted that approach in Excel 2010. It doesn't work for me. Once the import dialog box is up, the script stops. Using a timer to call another sub in the same module to appactivate and sendkey will only resume to work if the dialog opened initially from the 1st sub is closed and re-open manually. Well, either that or the VBE throws an error because window not found - depending on how you code it.

For whatever technical reasons - either in SAPGUI or Windows or Excel, the only way to do this is to run the subroutines from two different process instances with the first script calling the second script before it calls the dialog box. You can use either VBA in two different Excel files, VBA and VBS, VBS and another VBS or two instances of script process of whatever scripting languages you prefer.

I believe Scriptman used 4 different VBS to do it - one to call the dialog, two to sleep for 0.1sec and 1sec and another to appactivate and sendkeys.

[Former Member · Accepted solution]

Yeah it works for me from scriptman.

But it looks like:

session.findById("wnd[0]/shellcont/shell").pressContextButton "CREATE_ATTA"

Set wshell = CreateObject("WScript.Shell")

wshell.Run "c:\temp\xxx.vbs", 1, False

session.findById("wnd[0]/shellcont/shell").selectContextMenuItem "PCATTA_CREA"

for me.

Im starting the vbs before the window pop-up. when its after pcata_crea, it wont paste.

And the *.vbs:

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("Import file")

wshell.run "c:\temp\1000.vbs",1,true

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("Import file")

if (bWindowFound) Then

Wshell.appActivate "Import file"

wshell.run "c:\temp\100.vbs",1,true

Wshell.sendkeys "{ENTER}"

Wshell.sendkeys "c:\TEMP\TZK.PDF"

Wshell.sendkeys "{ENTER}"

end If

Enter sended because it staying focused on the upper path - when enter -it focus on bottom path - then it works normaly.

Just one question -

Why are pause breaks in another files?

[script_man · Accepted solution]

Hi Mirek,

maybe I can answer the last question yourself.

The command

wshell.run "c:\temp\1000.vbs",1,true

corresponds to

wscript.sleep 1000

and e.g. in VBA with

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

    DoEvents

Wend

Sometimes, however, there are difficulties. To deal with this, this construct was used. But that does not mean that it can not work also differently.

Regards,

ScriptMan

[Former Member · Accepted solution]

Good afternoon everyone
I need help
I have carefully read several reviews on the web on this subject
I have a problem to define and create the script that need
Can you help ?
I have to attach a document to multiple clients using " PCATTA_CREA "
First I want to know how to do one with a script
Then I want to automate with vba script in a loop and attach to a different document
for each client in an Excel list
I recorded the script but do not know how to include the url of the file you want
in the " import file"
I think I've read everything but I just do not create the right script
I do not speak English and my knowledge of sap and vbs are rudimentary
Can anyone complete the script to work ?
Thanks to all
I have Excel 2010 and SAP 720
Then I show the two macros I've created
The first is the one that opens the "import file" in the transaction CIC0
The second isone that includes the attached
(both thanks to the contributions of SCRIPTMAN, SAYUTI, UMUR and others. Took a
year reading your contributions and all are very good)

Sub intentando_anexar_documentos_en_SAP()

Dim application

If Not IsObject(application) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set application = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

Set connection = application.Children(0)

End If

If Not IsObject(session) Then

Set session = connection.Children(0)

End If

If IsObject(Wscript) Then

Wscript.ConnectObject session,"on"

Wscript.ConnectObject application, "on"

End If

• session.findById("wnd[0]").maximize
• session.findById("wnd[0]/tbar[0]/okcd").Text = "/NCIC0"
• session.findById("wnd[0]").sendVKey 0
• session.findById("wnd[1]/usr/lbl[22,4]").SetFocus
• session.findById("wnd[1]/usr/lbl[22,4]").caretPosition = 3
• session.findById("wnd[1]").sendVKey 2
• session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").Text = "10591671" ‘customer number
• session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").SetFocus
• session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").caretPosition = 8
• session.findById("wnd[0]").sendVKey 0
• session.findById("wnd[1]/usr/lbl[1,3]").caretPosition = 2
• session.findById("wnd[1]").sendVKey 2
• session.findById("wnd[0]").sendVKey 0

• session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/btn[2]").press
• session.findById("wnd[0]/titl/shellcont/shell").pressButton "%GOS_TOOLBOX"
• session.findById("wnd[0]/shellcont/shell").pressContextButton "CREATE_ATTA"

• session.findById("wnd[0]/shellcont/shell").SelectContextMenuItem "PCATTA_CREA"

• session.findById("wnd[0]/shellcont").Close

'session.findById("wnd[0]/tbar[0]/btn[3]").press

Set SapGuiAuto = Nothing

Set application = Nothing

Set connection = Nothing

Set session = Nothing

End Sub

-----------------------------------------------------------------------------

Sub script_para_anexar_archivo()

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("import file")

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("import file")

If (bWindowFound) Then

• Wshell.AppActivate "import file"

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

• Wshell.SendKeys "{ENTER}"

• Wshell.SendKeys Excel.Sheets(1).Cells(31, 1).Value

• Wshell.SendKeys "{ENTER}"

End If

End Sub

[Former Member · Accepted solution]

Hi Manuel.

For me the Scriptman solution works like this:

**************************************************************

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("Import file")

wshell.run "c:\temp\1000.vbs",1,true

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("Import file")

if (bWindowFound) Then

Wshell.appActivate "Import file"

wshell.run "c:\temp\100.vbs",1,true

Wshell.sendkeys "{ENTER}" - I experimented with Enter here, without it, the path for file was inserted into browsing tab of "import file" window   

Wshell.sendkeys "c:\TEMP\TZK.PDF" - Here your URL

Wshell.sendkeys "{ENTER}"

end If

***************************************************************

This is some xxx.vbs file content and the file it must be run when the import window is active. The rest of the SAP script should be ok.

For trsctn CO02, script looks very similar like yours:

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

session.findById("wnd[0]/tbar[0]/okcd").Text = "co02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = Range("G6").Value
session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").caretPosition = 10
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/titl/shellcont/shell").pressButton "%GOS_TOOLBOX"
session.findById("wnd[0]/shellcont/shell").pressContextButton "CREATE_ATTA"
Set Wshell = CreateObject("WScript.Shell")
Wshell.Run "c:\temp\xxx.vbs", 1, False - im running before the window popup, because sometimes it went wrong, when the window wasnt fully loaded.
session.findById("wnd[0]/shellcont/shell").selectContextMenuItem "PCATTA_CREA"

session.findById("wnd[0]/shellcont").Close
session.findById("wnd[0]/tbar[0]/btn[11]").press

Kill "C:\TEMP\TZK.pdf"

[Former Member · Accepted solution]

Good afternoon Miroslav
First I want to thank you for responding
this is all very new to me
and also do not understand English, I only speak Spanish

I read your instructions and still confused
I have to do some tests
Tomorrow, 2/13/2014, I will try to test the script with the changes you
indicate
I'll tell you what happens with your instructions
Looking forward to solve this issue with the valuable support of this forum

It would be really useful for my daily job in terms of saving time
regards from a rainy Madrid

[Former Member · Accepted solution]

Good afternoon Miroslav

I followed your instructions. I have advanced a bit in my macro but I still have doubts

what I've learned and my new questions are:

have a main macro in a VBA module and have a helper script in VBS file

the macro immediately after the

"session.findById ( " wnd [0 ] / shellcont / shell " ) .pressContextButton " CREATE_ATTA "

statement calls the script to capture the URL of the file to attach to SAP

I have tried several times in succession and does not work in all cases . always does not work because the script does not select default button " Escritorio /Desktop"

Is it possible to select it somehow ?

If we solve the above I will always be attached to at least one SAP client file whose URL is written in the script. That would be a breakthrough and

But on my list in Excel I have several SAP clients and I have to attach a different file for each. I would do it in a loop

This file would have the numbers of the SAP customers in a cell and another of the same row the URL of the file you want to attach

How I can make the script supports a loop over the file SAP customers that I have on my list of Excel?

Is it possible to merge the macro instructions with the instructions in a single set script to do the loop I need?

I hope I explained well .

Thank you

Macro VBA

............

Set Wshell = CreateObject("WScript.Shell")

Wshell.Run"C:\Users\MANUEL\Desktop\Script1.vbs", 1, False

............

Script1.vbs

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("Importar fichero")

WScript.Sleep 1000

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("Importar fichero")

If (bWindowFound) Then

Wshell.AppActivate "Importar fichero"

WScript.Sleep 1000

Wshell.SendKeys"{ENTER}"

WScript.Sleep 1000

Wshell.SendKeys "C:\Users\MANUEL\Desktop\Sin titulo.png"

Wshell.SendKeys"{ENTER}"

End If

[Former Member · Accepted solution]

Hi.

I think the loop will be the last step. It will be just excel.

We need to solve it for 1 user. I dont fully understand (BTW im from Czech...we lost with Sweden) what you exactly need to add as an attachment. Why do you need to be on desktop as default? If you adding full path it doesnt matter where you actually are on the disc- desktop, C:\ ... you adding: Wshell.SendKeys "C:\Users\MANUEL\Desktop\Sin titulo.png". You want attach URL or file from disc?

Moreover I think that when I added pause like WScript.Sleep 1000 in script1.vbs, it didnt work. Do it like another file a and run it in script1.vbs like in my (scriptman) method , so we know we are on the same wave.

As I said try to remove enter before Wshell.SendKeys "C:\Users\MANUEL\Desktop\Sin titulo.png" play with it, see whats it doing during script running.

As I understand - In the end you will have user id in column A and URL link in column B. When you run vba, it will add attachment to user id in -SAP, jump on another user in col A and so on. Tomorrow I try access into trsctn you are using.

[Former Member · Accepted solution]

Hello Miroslav

I completely agree
First, we solve the macro for a single user / client SAP
The latter will fix the loop to multiple users / clients SAP

Sorry, I have not explained clearly ....
I want to add to each SAP client as an attachment is a file from disc to disc through its path
A file can be Word , Excel, Outlook, etc.

Indeed, I do not need to have the default desktop to attach a file on a disk
I just need to select the macro that continually gave me errors.
After your last statement I think the errors are related to the "{ENTER}" statement and instructions timeouts "Wscript.Sleep 1000"

But do not know how to affect the macro script1 and both the location of the instruction as
the number of times you have to include it  not know how the instructions affect waiting times
"Wscript.Sleep 1000"

I tried to include the script code in a VBA macro and run it with open "Insert file " window. The
macro has worked every time I've tried ( about 20 times ) . I used the formula given by Scriptman four " time-outs " of the type:

waitTill = Now() +
TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

The macro does not support the formula " Wscript.Sleep 1000". Gives an "error 424 runtime . An Object Required "

I have run the script individually with open "Insert File " window and almost always works . In this case I've also included four ' waiting times " with the formula" Wscript.Sleep 1000 " because the proposed" waitTill = Now ( ) + TimeValue ( " 00:00:01 " ) - While Now () < waitTill - DoEvents - Wend " fails

It can be deduced that the script is correct

The problem is when I throw the main macro. When you reach the order to drop the " wshell.Run " script C : \ Users \
MANUEL \ Desktop \ Script1.vbs ", 1, False " NOT ALWAYS includes the path expected in the field "Name " and other instructions the script tries to open another session of SAP , the macro fails and remains in suspense

I do not know what happens .

It seems the time of opening time window "Insert file" or delayed will advance when the weather wshell includes data path

Is it possible to be adjusted in time by lengthening or shortening them script ?

I think we are very close to solving this, I hope you can help me

I have a question , what does the number "1 " and the word "False" in the statement: wshell.Run "C: \ Users \ MANUEL\

Desktop \ Script1.vbs ", 1, False ?

Finally ,

Indeed , my idea is to have a customer number in column A and the file to attach in column B as in Example

I indicated the two ways I've tried the script1. The first as a macro and the second as a script activated by double-clicking in both cases the "Insert file" window was open.

You also indicated the complete macro

I appreciate the effort and time you are investing in this. Is it possible to score your contribution
to the forum, how is it done?

I take it you like ice hockey

Greetings and good weekend

MACRO to run the Script

Sub insertando_archivo_en_la_ventana_de_Importar_archivo()

Set wshell = CreateObject("WScript.Shell")

Do

bWindowFound = wshell.AppActivate("Importar fichero")

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

Loop Until bWindowFound

bWindowFound = wshell.AppActivate("Importar fichero")

If (bWindowFound) Then

wshell.AppActivate "Importar fichero"

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

wshell.SendKeys "{ENTER}"

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

wshell.SendKeys "C:\Users\MANUEL\Desktop\Sin titulo.png"

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

wshell.SendKeys "{ENTER}"

waitTill = Now() + TimeValue("00:00:01")

While Now() < waitTill

DoEvents

Wend

End If

End Sub

-------------------------------------------------------------------------------------------------------------------------------------

Script

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("Importar fichero")

wscript.sleep 1000

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("Importar fichero")

if (bWindowFound) Then

Wshell.appActivate "Importar fichero"

wscript.sleep 100

wshell.SendKeys "{ENTER}"

wscript.sleep 100

Wshell.sendkeys "C:\Users\MANUEL\Desktop\Sin titulo.png"

wscript.sleep 100

Wshell.sendkeys "{ENTER}"

end If

MY MAIN MACRO

Sub intentando_anexar_documentos_en_SAP_dos()

Dim application

If Not
IsObject(application) Then

   Set SapGuiAuto = GetObject("SAPGUI")

   Set application = SapGuiAuto.GetScriptingEngine

End If

If Not
IsObject(connection) Then

   Set connection = application.Children(0)

End If

If Not
IsObject(session) Then

   Set session = connection.Children(0)

End If

If IsObject(Wscript) Then

   Wscript.ConnectObject session, "on"

   Wscript.ConnectObject application, "on"

End If

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

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

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

session.findById("wnd[1]/usr/lbl[22,4]").SetFocus

session.findById("wnd[1]/usr/lbl[22,4]").caretPosition = 3

session.findById("wnd[1]").sendVKey2

session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").Text= "10591671"

session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").SetFocus

session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/sub/1/1/2/1/txt[1]").caretPosition = 8

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

session.findById("wnd[1]/usr/lbl[1,3]").caretPosition = 2

session.findById("wnd[1]").sendVKey 2

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

session.findById("wnd[0]/usr/sub/1/tabsCCONT_CONTROL/tabpCCONT_TAB01/ssub/1/1/2/btn[2]").press

session.findById("wnd[0]/titl/shellcont/shell").pressButton "%GOS_TOOLBOX"

session.findById("wnd[0]/shellcont/shell").pressContextButton "CREATE_ATTA"

Set wshell = CreateObject("WScript.Shell")

wshell.Run "C:\Users\MANUEL\Desktop\Script1.vbs", 1, False

session.findById("wnd[0]/shellcont/shell").SelectContextMenuItem "PCATTA_CREA"

session.findById("wnd[0]/shellcont").Close

Set SapGuiAuto = Nothing

Set application = Nothing

Set connection = Nothing

Set session = Nothing

End Sub

[Former Member · Accepted solution]

Hi.

Hockey is not for me, Ijust had fortuna ticket... and I lost...

Now, I do not have permission for transacvtion, you are using so I dont know how it exactly work.

The  False after run command is for wait to complete command I think, 1 dont know.

Now for the key problem:

As I said: Do it exactly like in my solution:

Excel Vba is correct.

Create 3  files in c:\temp -for example:

1.

Script1.vbs - inside file is:

Set Wshell = CreateObject("WScript.Shell")

Do

bWindowFound = Wshell.AppActivate("Import file")

wshell.run "c:\temp\1000.vbs",1,true

Loop Until bWindowFound

bWindowFound = Wshell.AppActivate("Import file")

if (bWindowFound) Then

Wshell.appActivate "Import file"

wshell.run "c:\temp\100.vbs",1,true

Wshell.sendkeys "{ENTER}" 

Wshell.sendkeys "c:\TEMP\TZK.PDF" - Here your document path

Wshell.sendkeys "{ENTER}"

end If

2.

1000.vbs- inside file is:

wscript.sleep 1000

3. - I think this is optional, you can use 1000 everywhere, but now do it.

100.vbs

- inside file is:

wscript.sleep 100

-The use of wait method is probably crucial here!!!

VBA:

......

session.findById("wnd[0]/shellcont/shell").pressContextButton "CREATE_ATTA"

Set wshell = CreateObject("WScript.Shell")

wshell.Run "C:\temp\Script1.vbs", 1, False

session.findById("wnd[0]/shellcont/shell").SelectContextMenuItem

.......

And THEN we can discus errors...

I have it now like - when i open excel document it creates these 3 files when opening. Before close - they are deleted.

Im pretty sure we are stuck on the time delay here.