cancel
Showing results for 
Search instead for 
Did you mean: 

Excel VBA Script for Services for Object ToolBox - the "Import File" dialog Box

former_member213011
Participant
0 Kudos

Dear Community,

I'm trying to automate the creation of attachments using Services for Object in SAP transaction IW22 via Excel VBA.

My issue is similar to what   faced 3 years ago, which was solved together with   in this thread: http://scn.sap.com/thread/1799453

From what I understand from the discussion, the solution was to start a script which loops to detect for the "Import File" window and if found send the filename string via sendkey method to the dialog box. Concurrently, run the main script to execute Services for Object create attachment i.e. the following:

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

session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_PCATTA_CREA"

(****the "Import File" dialog box pops here****)

Is there a better way to do this i.e. Is there any method or properties within the SAP Scripting API or VBA that allow to send data to the "Import File" dialog box directly?

BTW, script recording does not record the selection of files in the "Import File" dialog box or capture any path and filename string. This is because the dialog box is not an SAP screen but a native Windows screen.

Thanks in advance for any tips.

Sayuti.


Message was edited by: Sayuti Azmi

Accepted Solutions (1)

Accepted Solutions (1)

holger_khn
Contributor
0 Kudos

Hi Sayuti. In another thread I have posted an VBA coding how USER32 API can handle this (in that case for SaveAs Dialog called by SAP). For development I use Microsoft 'Spy' to get the right indicators for identifing.

Hope this give you some ideas. Br, Holger

former_member213011
Participant
0 Kudos

Dear Holger,

Thanks for the tips. I was hoping not to go into API calls, unless it is the only way to get the job done.

I'll test it and share the result later.

Thanks,

Sayuti

former_member213011
Participant
0 Kudos

Dear Holger,

Upon further inspection, the script doesn't overcome the issue of script halting when the dialog box pop up.

Once the dialog box pop up, I have to cancel it, only then the module continue and give me the 'dialog box not found' message.

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

session.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_PCATTA_CREA"

(****the "Import File" dialog box pops here, and script halted.****)

doEvents

Auto_SaveAs_SAP

If I open the dialog box manually, then the module work.

Actually, I already have a simple working code which functions similar to yours (but not as robust).

Sub pastedata()

    On Error Resume Next

    AppActivate ("Import file")

    Do While Err = 5

        Err = 0

        DoEvents

        AppActivate ("Import file")

    Loop

    SendKeys "D:\Tmp\test.txt"

    SendKeys "{ENTER}"

End Sub

What I'm trying to overcome is the halting of the script when the dialog box popup if called from another script.


Any idea?

Thanks,

Sayuti

holger_khn
Contributor
0 Kudos

I experienced the same issue. It´s not working when we have it in same instance/script. So my solution was to make an seperate Excel VBA-macroenableed file with API-calls. This run in a Loop and catch the popup Dialog module when appear and do required Action. In my sheet I have an button which can be used to Close the Loop.

former_member213011
Participant
0 Kudos

Dear Holger,

Thanks for confirming this. It seems that this is probably the only way to go.

BTW, how do your run the separate Excel file (which loop)? Manually or from the main VBA which call the SAP dialog box?

Thanks,

Sayuti

holger_khn
Contributor
0 Kudos

I start it manually and let it run as long as my main VBA-Macroenabled is running. There are a few tricks that my helptool will end automatically when my main-file is done. But this is very specific and require an customized classmodule and some specific coding which can identify open Excel-files in all Excel-instances.

former_member213011
Participant
0 Kudos

Noted.

For my project I'm going to maintain one Excel File for easier distribution.

For the purpose of uploading file using Services for Object, I'm going to use the following steps:

  1. VBA to create a VBS file with the instruction line to loop and check for the Import File dialog box. If found, activate it then sendkeys five tab keys, the fullpath string of the file to be uploaded and enter key to the dialog box. The fullpath will be stored in a string variable which takes its values from the VBS file argument send by the VBA in the next step.
  2. VBA will shell WScript to run the VBS file with the fullpath string as its argument.
  3. VBA will execute the procedure to bring up the dialog box in SAP.
  4. If everything go as planned, the file will be uploaded to SAP and VBA will kill (i.e delete) the VBS file.

For the purpose of testing steps 2 and 3, I have manually created the VBS file. It works. Now I'm working on the codes to automate the creation of the VBS file (step 1) and delete it (step 4).

The setback is, if the VBS is interrupted before it finishes executing, VBA and Excel will hang until the Import File dialog box is manually closed in SAP.

Anyway, thanks again for your replies and guidance.

Regards,

Sayuti

Former Member
0 Kudos

Hi,

Thanks for the Suggestions.

By the way is it possible to use the same function for multiple files.

Because for me the task is, each Numbers needs to be attached with the unique appropriate Attachment.

Am placing the Numbers in A column and File path in B column in excel and am trying to execute the program with similar modifications.

Can you please provide some suggestion?

Have a Nice Day!

Thanks in Advance,

Krishna E S

Former Member
0 Kudos

Hi Sayuti,

I'm very interested in knowing if you did accomplish the upload  through the services for object Dialog box.

I would also want to know if you could share some of the code to guide myself. Cause i dont yet know how to do several of the steps on the comment above.

Please let me know!

Thanks!

Rodolfo

former_member213011
Participant
0 Kudos

Dear Krishna,

Yes, it is possible using a loop procedure. However, depending on the size of your file and the speed of the connection to the SAP server, the script will be very prone to error.

I've not done any stress test on my codes since the project was halted a year ago due to a major re-org in my organization.

Sorry cant be of much help.

Regards,

Sayuti

former_member213011
Participant
0 Kudos

Dear Rodolfo,

My organization had a major re-org in the end of 2013 and I was unable to really complete my project. The file upload procedure work, but I never properly integrate it to the rest of my project as it was shelved.

For Step 1:

I put the following codes in a range in Excel sheet named 'myCache' and named the range 'myVBS'.

Set Wshell = CreateObject("WScript.Shell")
fileAdd = WScript.Arguments(0)
Do
bWindowFound = Wshell.AppActivate("Import file")
WScript.Sleep 1000
Loop Until bWindowFound
bWindowFound = Wshell.AppActivate("Import file")
If (bWindowFound) Then
Wshell.appActivate "Import file"
Wshell.Sendkeys "{tab}{tab}{tab}{tab}{tab}"
Wshell.Sendkeys fileAdd
Wshell.Sendkeys "{ENTER}"
End If

I used the following subroutine to generate the the VBS file 'attach.vbs' whenever an upload is required.

Sub createVBS()

    Dim rng As Range

    Dim myVBS As String

    myVBS = ThisWorkbook.Path & "\attach.vbs"

   

    Open myVBS For Output As #1

   

    For Each rng In myCache.Range("myVBS")

        Print #1, rng

    Next rng

   

    Close #1

End Sub

For Step 2 & 3:

I created two subroutines, one to shell the VBS and another to open the import file dialog box. These two subroutines will be called by another subroutine.

Do note that sap_IW22 is subroutine that check connection  and open sessions for a specific IW22 (Change PM Notification) transaction in SAP. If  the session does not exist, it will execute the transaction. I also used custom class module mySAP for connection and session handling.

Sub runVBS(fileFullPath As String)

    Dim scr As String

    Dim myFile As String

    myFile = Chr(34) & fileFullPath & Chr(34)

    scr = Chr(34) & ThisWorkbook.Path & "\attach.vbs" & Chr(34) & " " & myFile

    Shell "wscript " & scr

End Sub

Sub sap_IW22_Upload(myNotification As String)

    sap_IW22 (myNotification)

    mySAP.SAPSession.FindById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"

    mySAP.SAPSession.FindById("wnd[0]/titl/shellcont/shell").SelectContextMenuItem "%GOS_PCATTA_CREA"

End Sub

'The following is the subroutine to call both the above subroutines.

Sub uploadFile(ByVal filetoupload As String, ByVal myNotification As String)

    runVBS (filetoupload)

    sap_IW22_Upload (myNotification)

End Sub

'To upload, you can call the uploadFile subroutine as per following example:

Sub testUpload()

    Call uploadFile("c:\test.txt", "23011119")

End Sub

For multiple files, try looping this subroutines.

For Step 4:

Sub deleteVBS()

    Dim myVBS As String

    myVBS = ThisWorkbook.Path & "\attach.vbs"

   

    If Dir(myVBS) = "attach.vbs" Then

        Kill myVBS

    Else

        MsgBox myVBS & " not found", vbExclamation, "File Don't Exist"

    End If

End Sub

Regards,

Sayuti

Former Member
0 Kudos

I Have completed steps 1 & 2 but im having an issue with the "Sub sap_IW22_Upload(myNotification As String)" I tried to conect to my box and run the code untill it just stops and pulls  the dialog box. Not sure where the "attach" vbs file comes in. Any help? Now, i im not familiarized with the "mynotification" part, what should i change that for?

*****************Code below*******

Dim SapGuiApp As Object

Dim oConnection As Object

Dim session As Object

Dim SAPCon As Object, SAPSesi As Object

Dim SAPGUIAuto As Object, SAPApp As Object

If SapGuiApp Is Nothing Then

    Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")

End If

If oConnection Is Nothing Then

    Set oConnection = SapGuiApp.OpenConnection(" SSO", True)

End If

If SAPSesi Is Nothing Then

   Set SAPSesi = oConnection.Children(0)

End If

With SAPSesi

.FindById("wnd[0]").maximize

.FindById("wnd[0]/tbar[0]/okcd").Text = "/nudm_dispute"

.FindById("wnd[0]").sendVKey 0

.FindById("wnd[0]/shellcont/shell/shellcont[0]/shell/shellcont[1]/shell/shellcont[1]/shell").selectedNode = "          1"

.FindById("wnd[0]/shellcont/shell/shellcont[0]/shell/shellcont[1]/shell/shellcont[1]/shell").expandNode "          2"

.FindById("wnd[0]/shellcont/shell/shellcont[0]/shell/shellcont[1]/shell/shellcont[1]/shell").topNode = "          1"

.FindById("wnd[0]/shellcont/shell/shellcont[0]/shell/shellcont[1]/shell/shellcont[1]/shell").selectedNode = "          5"

.FindById("wnd[0]/shellcont/shell/shellcont[0]/shell/shellcont[1]/shell/shellcont[1]/shell").doubleClickNode "          5"

.FindById("wnd[0]/usr/cntlCLFRM_CONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[0]/shell/shellcont[0]/shell/shellcont/shell").currentCellColumn = "SEL_ICON2"

.FindById("wnd[0]/usr/cntlCLFRM_CONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[0]/shell/shellcont[0]/shell/shellcont/shell").pressButtonCurrentCell

.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = "500001024788"

.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").caretPosition = 12

.FindById("wnd[1]/tbar[0]/btn[8]").press

.FindById("wnd[0]/usr/cntlCLFRM_CONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[0]/shell/shellcont[1]/shell").pressButton "DO_QUERY"

.FindById("wnd[0]/usr/cntlCLFRM_CONTAINER/shellcont/shell/shellcont[1]/shell/shellcont[1]/shell").doubleClickCurrentCell

.FindById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"

.FindById("wnd[0]/titl/shellcont/shell").selectContextMenuItemByText "Create...|Store business document"

.FindById("wnd[1]/usr/ssubSUB110:SAPLALINK_DRAG_AND_DROP:0110/cntlSPLITTER/shellcont/shellcont/shell/shellcont[0]/shell").hierarchyHeaderWidth = 269

.FindById("wnd[1]/usr/ssubSUB110:SAPLALINK_DRAG_AND_DROP:0110/cntlSPLITTER/shellcont/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000005"

.FindById("wnd[1]/usr/ssubSUB110:SAPLALINK_DRAG_AND_DROP:0110/cntlSPLITTER/shellcont/shellcont/shell/shellcont[0]/shell").doubleClickNode "0000000005"

End With

End Sub

former_member213011
Participant
0 Kudos

The IW22 is the transaction in which I need the file to be uploaded. The variable mynotification is to take the IW22 Notification number as argument.

You can disregard this variable.and replace the subroutine SAP_IW22_Upload with your codes above.

Answers (0)