Purpose
You want to export data from a SAP transaction to a Microsoft Project file.
Solution
Microsoft offers an interface for the Office products, which you can address with the GuiXT Controls.
Please note that Microsoft Project must be installed on the respective PC in this case.
You can address the interface either via JavaScript (callJS command) or via a .NET extension (callVB command). We describe both possibilities below, each of which has advantages and disadvantages.
In this example, we want to export data from SAP Project Builder (CJ20) to Microsoft Project file:
Using a JavaScript call:
Call the following InputScript, which in this example inserts the selected lines into the MS Project file through a callJS call:
GuiXT
// Variables
CreateTable V[inputtab] taskid tasktext taskstart taskend
Set V[absrow] 1 // Absolute row number
Set V[relrow] 1 // Relative row number
// Change Structure Planning: Basic dates
Screen SAPLCJTR.0230
GetTableAttribute T[table] firstVisibleRow=FVisRow _
lastVisibleRow=LVisRow lastRow=LastRow
// First row on screen?
if V[FVisRow=1]
goto new_row
endif
// scroll to first line
Enter "/scrollToLine=1" table="T[table]"
label new_screen
// Change Structure Planning: Basic dates
Screen SAPLCJTR.0230
GetTableAttribute T[table] firstVisibleRow=FVisRow _
lastVisibleRow=LVisRow lastRow=LastRow
Set V[relrow] 1
label new_row
// end of table?
if V[absrow>&V[LastRow]]
goto end_of_table
endif
// end of screen?
if V[absrow>&V[LVisRow]]
Enter "/scrollToLine=&V[absrow]" table="T[table]"
goto new_screen
endif
Set V[selected] "&cell[table,0,&V[relrow]]"
// Selected?
if V[selected=X]
createstructure V[inputtab_row] taskid tasktext taskstart taskend
Set V[inputtab_row.taskid] "&cell[table,RCWBS-IDENT,&V[relrow]]"
Set V[inputtab_row.tasktext] "&cell[table,RCWBT-PIDEN,&V[relrow]]"
Set V[inputtab_row.taskstart] "&cell[table,RCWBT-STERM,&V[relrow]]"
Set V[inputtab_row.taskend] "&cell[table,RCWBT-ETERM,&V[relrow]]"
appendrow V[inputtab_row] table="V[inputtab]"
endif
Set V[absrow] &V[absrow] + 1
Set V[relrow] &V[relrow] + 1
goto new_row
label end_of_table
// Export elements to MS Project file
callJS addtasks "C:\data\simple_project.mpp" "inputtab"
if V[r]
message "E: &V[r]" -statusline
else
message "Tasks exported to MS Project" -statusline
endif
// Back to line 1
Enter "/scrollToLine=1" table="T[Table]"
The method "addtasks" must be present in the JavaScript library specified in the GuiXT profile and looks like this:
JavaScript
Using a .NET library:
The InputScript from above just slightly changes: The JavaScript call is replaced by a call to the function in the guixt_office.dll file:
'''
''' Add a list of tasks to an existing MS Project file
'''
''' Path to MS project file
''' Name of GuiXT table variable
''' empty string if success, errortext otherwise
Public Function addtasks(filename As String,
tasktable As String) As String
Dim returnText As String = ""
Try
'Create the Application object to interact with MS project
Dim projectApplication As
Microsoft.Office.Interop.MSProject.Application =
New Microsoft.Office.Interop.MSProject.Application()
Dim mv As Object = System.Reflection.Missing.Value
' Open the MS project file
projectApplication.FileOpenEx(filename, mv, mv,
mv, mv, mv, mv,
mv, mv, mv, mv,
Microsoft.Office.Interop.MSProject.PjPoolOpen.pjPoolReadWrite,
mv, mv,
mv, mv, mv)
' Get the active project in the file
Dim project As Microsoft.Office.Interop.MSProject.Project =
projectApplication.ActiveProject
' When createTable is used in GuiXT to fill a table,
' the data is available in JSON format
' We just convert it to an object (array) here
Dim JSONString = myguixt.GetVariable(tasktable)
Dim tasks As Object = _
New JavaScriptSerializer().Deserialize(Of Object)(JSONString)
' Now we can easily loop through all rows
For Each t In tasks
' Create And add New task
Dim newTask As Microsoft.Office.Interop.MSProject.Task
newTask = project.Tasks.Add()
' Access the data in column "tasktext" in the current row
newTask.Name = t("tasktext")
' Get the data (string format) and convert to data
If t("taskstart") <> "" Then
Dim startDate As New Date
Date.TryParse(t("taskstart"), startDate)
newTask.Start = startDate
End If
If t("taskend") <> "" Then
Dim endDate As New Date
Date.TryParse(t("taskend"), endDate)
' We need to add 1 day....
endDate = endDate.AddDays(1)
newTask.Finish = endDate
End If
Next
' Close the file and automatically save
projectApplication.FileCloseAll(
Microsoft.Office.Interop.MSProject.PjSaveType.pjSave)
Catch ex As Exception
' Something went wrong: Return the message to GuiXT
' so it can be shown on the SAP transaction
'
' e.g. file was not found
returnText = ex.Message
End Try
Return returnText
End Function
Comparison between JavaScript and .NET
Using JavaScript is possible without much effort, but it has the following disadvantage: you have to figure out the methods you need and their parameters, and you have few tools or testing options available. You can therefore also create a .NET library,
have tools such as code completion available there and can first test independently of GuiXT and the SAP transaction.