on 11-04-2013 7:25 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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:
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.