Placing two tables in Analysis for office without overlap

[Former Member]

I want to place two tables in Analysis for Office(Excel). But if the size of first table increases at run time the second one is overwritten. Is there any way to shift the second table automatically based on the size of first table?

[Former Member]

Hi Richa,

the solution you need is already available as VBA code ... which I received during the WDEAN1 course....

I tried to find it as SCN document here, but didn't.

So, if anyone knows the link please post it and I will remove the coding...

Example Solution:

1) Insert your 1st query into cell A1 and your 2nd query into cell A10.000

2) Insert coding in the VBA Editor (ALT+F11) into "ThisWorkbook"

Public Sub Workbook_SAP_Initialize()

' register callbacks

Call Application.Run("SAPExecuteCommand", "RegisterCallback",

"AfterRedisplay", "Callback_AfterRedisplay")

End Sub

3) Copy & Paste this coding into "Modul1" of the VBA Project:

Option Explicit

Public Sub Callback_AfterRedisplay()

Dim intGap As Integer

Dim intMaxrow_crosstab1 As Integer

intGap = 5

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

Application.ScreenUpdating = False

Rows("1:9995").Select

Selection.EntireRow.Hidden = False

intMaxrow_crosstab1 = intMaxrow_crosstab1 + intGap

Rows(intMaxrow_crosstab1 & ":9999").Select

Selection.EntireRow.Hidden = True

Range("A1").Activate

Application.ScreenUpdating = True

End Sub

Important: Before you can use this, you must close and re-open the workbook! Otherwise the Callback function will not be registered.

When this works you can also change the start cells for the queries and adjust the gap.

Best regards,

Martin

[Former Member]

Thanks Martin That was really helpful and worked for me.

[former_member131154]

I like to see that my participants remember the use cases from my training classes

All the best

Dirk

More information regarding WDEAN1 training mentioned can be found here: WDEAN1 - Workshop Analysis, edition for MS Office | SAP Training and Certification Shop

[former_member701516]

Hello,

I have a case where this is not working, Suppose i ran a workbook my total records coming 10 then again i refresh and ran for 1 day and total record came 3 then my second query comes on 11 row.

and if i do Alt+F8 and click run. it aligns properly.

Regards

Lalit Yadav

[torsten_wirth]

Next version for 3 Queries (not a big difference):

Option Explicit

Public Sub Callback_AfterRedisplay()

Dim intGap As Integer

Dim intMaxrow_crosstab1 As Integer

Dim intMaxrow_crosstab2 As Integer

einfach Leerzeichen eingefügt werden)

intGap = 5

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

intMaxrow_crosstab2 = Range("SAPCrosstab2").Rows.Count

Application.ScreenUpdating = False

Rows("1:995").Select

Selection.EntireRow.Hidden = False

intMaxrow_crosstab1 = intMaxrow_crosstab1 + intGap

Rows(intMaxrow_crosstab1 & ":999").Select

Selection.EntireRow.Hidden = True

Rows("1000:1995").Select

Selection.EntireRow.Hidden = False

intMaxrow_crosstab2 = intMaxrow_crosstab2 + intGap + 1000

Rows(intMaxrow_crosstab2 & ":1999").Select

Selection.EntireRow.Hidden = True

Range("A1").Activate

Application.ScreenUpdating = True

End Sub

Or another variant if you would like to sum up the last row of query one and two:

This should be placed e.g. in Row 1000 (or 10000 whatever you use) - which means you have to add minimum one row before your second query - this query will start in row 1001 then. The code is the same:

=VERWEIS(9,99E+100;F1:F995)

Which allows:

=SUMME(<result of verweis> + y)

[Former Member]

I had modified this code to adjust by Columns (in case people are searching for solutions) - It would be nice for a Before_Redisplay call.....

Public Sub Callback_AfterRedisplay()

Dim columnLetter As String
Dim intMaxrow_crosstab1 As Integer

intMaxrow_crosstab1 = Range("SAPCrosstab1").Columns.Count

Application.ScreenUpdating = False

intMaxrow_crosstab1 = intMaxrow_crosstab1 + 1

columnLetter = ConvertToLetter(intMaxrow_crosstab1)

Columns(columnLetter & ":P").Select

Selection.EntireColumn.Hidden = True

Application.ScreenUpdating = True

End Sub

'Converts a column number to Letter - Code provided by Microsoft
Public Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

[Former Member]

Great idea! Thanks for sharing...

[Former Member]

Hi Richa,

I think you could do this with some VBA. Is having VBA in your workbook an option?