Technical Articles
A Generic Documentation method for SAP PO using VB script and MS EXCEL
Introduction
We as an SAP PO consultant sometimes want to do documentation activity while doing an enhancement project. So here I just want give a quick method to do documentation using SimpleQuery. This is not an industry standard but I will give you a quick generic method to do any kind of documentation. This blog is mainly involving more regarding automation using VB script. As sometimes we do not have luxury of java or python coding environment so I have just used VB script which can be executed in Excel.
Main
I will show you how to download the xml contents from SimpleQuery API then you guys can use other tools or Power Query of MS Excel as well to parse the xml files and get whatever information is required.
-
- Go to SimpleQuery URL: Use http://host:port/rep/support/SimpleQuery for ESR/IR object and http://host:port/dir/support/SimpleQuery for ID objects.
- Then just select whatever objects you want whether it can be communication channels or Integrated Configurations and then click on “Start query” button.
- Then you can find a table full of objects which satisfy the criteria you have provided.
- Then just go to “view page source” option of google chrome by right click on the page.
- Then search for word “WSDL” and copy everything whatever below that.
- Then paste them in an EXCEL sheet. And remove all the other tags and create 2 columns which has object name and WSDL URL.
- Then press “alt+f11” to open VBA in excel. Then create a module.
- Then put below code over there.
Function x(sURL As String, filename As String) sFilename = Application.ActiveWorkbook.Path & Application.PathSeparator & filename & ".xml" Set WinHttpReq = CreateObject("MSXML2.XMLHTTP") WinHttpReq.Open "POST", sURL, False WinHttpReq.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded" WinHttpReq.Send "j_username=XXXX&j_password=XXXX&uidPasswordLogon=Log+On" WinHttpReq.Open "GET", sURL, False WinHttpReq.Send If WinHttpReq.Status = 200 Then Set oStream = CreateObject("ADODB.Stream") oStream.Open oStream.Type = 1 oStream.Write WinHttpReq.responseBody oStream.SaveToFile sFilename, 2 oStream.Close End If End Function
- Replace XXXX with your user name and password or else you can copy the form data from developer view of google chrome as per below screenshot.
- Then go to reference in tool section of VBA and add as per following screenshot and then save the VBA.
- Then you can just use x as a function and download all the xml files of objects using MS excel and files will be stored at the same location where your MS EXCEL file was saved.
- In this way you can automate the whole process by dragging the function and it will download all the objects.
Conclusion
It’s just a simple tool to download the objects if you do not have java or python access. Sometimes client VMs does not have IDE access so you can automate using VB scripts. You can use those xml files to extract a lot of valuable information. “Be curious and keep learning”