Analysis Office Insert Product Image

In the comments of this blogpost on blogs.sap.com, Stephen Hobbs showed his idea of insert a product Image besides the crosstab in combination with the new Customize User Interface function. So I make my own thoughts how I can realize a thing like this.

My first thought was, where can I store the pictures and how can I get a product number and the picture together. The solution is very easy, I created a folder on my hard drive and put the pictures in it. The name of the pictures are the same like the product numbers.

Analysis Office Crosstab without Product Images
Analysis Office Crosstab without Product Images
Windows Explorer Images of the products
Windows Explorer Images of the products

The next step is to write the corresponding VBA code, so that the pictures are insert into a cell. Open the Visual Basic Editor (ALT + F11) and insert a new module. After we have to define serveral variables.

Dim material As String

Dim i As Integer

Dim picturePath As String

Dim rowMax As Long

Dim picture As Object

Dim t As Double

Dim l As Double

Dim w As Double

Dim h As Double

Now we have to find out, how many rows our crosstab has.

'Number of rows

rowMax = Range("SAPCrosstab1").Rows.Count

Now we can add our pictures.

For i = 5 To rowMax

'Set height for each row

Rows(i & ":" & i).RowHeight = 100

'Material

material = Sheet3.Cells(i, 3).Value

'Define Picture Path

picturePath = "U:\040 Analysis Office Workbooks\pics\" & material & ".jpg"

Set picture = ActiveSheet.Pictures.Insert(picturePath)

 

'Get size of the Picture Cell

With Range("B" & i & ":" & "B" & i)

t = .Top

l = .Left

w = .Offset(0, .Columns.Count).Left - .Left

h = .Offset(.Rows.Count, 0).Top - .Top

End With

'Format Picture into the right size

With picture

.Top = t

.Left = l

.Width = w

.Height = h

End With

Set p = Nothing

Next

The pictures are added on the left side of the crosstab. If you want to add your pictures automatically you can use the callback AfterRedisplay.

Analysis Office Crosstab with Product Images
Analysis Office Crosstab with Product Images

It is just an example what you can do. So If you have any suggestions for other examples, post them into the comments.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 € 1
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 € 1

Write a comment

Comments: 0