Purpose
Embed diagrams from an excel worksheet

For our example, we use an Excel file that contains a number of data on product sales in different market segments. We want to display on the SAP screen the profit per segment in the yearly comparison.

Solution
- Use "callVB" in a GuiXT script to export the excel diagram to an image file
- Embed the diagram with the "image" keyword

Excel file "financial_sample.xlsx":

To do this, we first need a GuiXT script with the file and diagram names as well as the name of the image file to be generated.

GuiXT Script:

  Box    (14,0)    (30,60)  "Visualize profit per segment:"
 
InputField       (15,1)    "InputFile"         (15,20)  size=35 name="excel_input"
 
InputField       (16,1)    "Name of Diagram"       (16,20)  size=35 name="excel_diagramname"
 
InputField       (17,1)    "OutputFile"      (17,20)  size=35 name="excel_outputfile"
 
Pushbutton     (18,41)  "Create Diagram"          process="create_diagram_profit_per_segment.txt"
 
Image (19.1,0.3) (31.1,59.8)     "&V[excel_outputfile]" -noStretch -noBuffer

The InputScript then creates the diagram using the "callVB" statement.

InutScript "create_diagram_profit_per_segment.txt":

callvb tutorials.excel.saveexceldiagram _
 "&V[excel_input]" "&V[excel_diagramname]" "&V[excel_outputfile]"

Class "guixtexcel" and method "saveExcelDiagram" in VB.NET:

Imports guinet
Imports Microsoft.Office.Interop.Excel
Imports System.IO

Public Class guixtexcel

    Public Sub SaveExcelDiagram(inputFile As String, _
                                diagamName As String, _
                                outputFile As String)

        Dim oXL As New Application
        Dim g As New guixt

        If Not File.Exists((inputFile)) Then
            MsgBox("File not found: " & inputFile)
            Return
        End If
        Try

            Dim oWB As Workbook
            Dim oSheet As Worksheet
            oXL = CreateObject("Excel.Application")
            oXL.Visible = False
            oWB = oXL.Workbooks.Open(inputFile)
            oSheet = oWB.Sheets(1)
            oSheet.ChartObjects(diagamName).Chart.Export(outputFile)

        Catch ex As Exception

            MsgBox(ex.Message)
            oXL.ActiveWorkbook.Close(False)
            oXL.Quit()

        End Try

        oXL.ActiveWorkbook.Close(False)
        oXL.Quit()

    End Sub
End Class

Result:

Components
InputAssistant + Controls