BW Work Book - Macro

[Former Member]

Hi,

I have saved 2 different BW queries view in seperate sheets of workbook (say sheet1 contain view1 and sheet2 as view2 ) .

Now I want to use two different check box in 3rd sheets (sheet3) and write a macro on it seperatley to call respectively the results of view1 and view 2 in sheet3 at different cell range.

say for example:- first check box result (view1) should appear in ( 18 - A ) of sheet3 and second check box results (view 2) should appear in ( 18 - F ) (sheet3)

can you please help me in this.

cheers

rajiv

[Former Member · Accepted solution]

Hi Rajiv,

Could you state it more clearly please.

Do you want to insert a checkmark symbol? Or kind of push or radio button?

How do you want to refresh and show queries?

Do these two queries have a result area just consisted of one cell? Or more?

What about results overlapping?

Best regards,

Eugene

[Former Member · Accepted solution]

yes,

I have save two different views (result areas) of my queries ( as jump queries ) in sheet1 and sheet2 of myworkbook.

Now, I want to insert two push button onto a new work sheet say example - push button 1 and push button 2 in worksheet no.3

when i press the push button 1 on worksheet3, it should display the result area of query view 1 (as stored in sheet1) in 3rd worksheet sheet say example at ( Row 5 - column A ). (complete result area) . Assumption is that view1 has only 2 key figures.

when i press the push button 2, it should display the result area of query view 2 (as stored in sheet2) in 3rd sheet say example at (Row 5 - Column F).

there should not be result overlapping as result areas of both the query view are stored in different cell areas.

I hope so i have stated my problem clearly.

Cheers

Rajiv

[Former Member · Accepted solution]

Hi Rajiv,

As a some first approach you can try this.

Dim Worksheet1 As Worksheet, Worksheet2 As Worksheet, Worksheet3 As Worksheet

Dim SRange As String, DRange As String

Set Worksheet1 = ThisWorkbook.Worksheets("SHEET1")

Set Worksheet2 = ThisWorkbook.Worksheets("SHEET2")

Set Worksheet3 = ThisWorkbook.Worksheets("SHEET3")

Set resultArea1 = Run("SAPBEX.XLA!SAPBEXgetResultRangeByID", “SAPBEXq0001")

Set resultArea2 = Run("SAPBEX.XLA!SAPBEXgetResultRangeByID", "SAPBEXq0002")

DRange = "$A$5"

SRange = Mid(resultArea1.Name, 9)

Worksheet1.Range(SRange).Copy Destination:=Worksheet3.Range(dRange)

DRange = "$F$5"

SRange = Mid(resultArea2.Name, 9)

Worksheet2.Range(SRange).Copy Destination:=Worksheet3.Range(DRange)

I combine all two copy actions in one peace of code linked to a command button press. You can create two command buttons and put in subs related to their press the appropriate portion of the code.

An operator like SRange = Mid(resultArea2.Name, 9)

just cut off the first part of the result area with a sheet indication.

Be carefull with a query name ("SAPBEXq0001" etc.). It may be different if you put into workbook several queries.

Hope this helps.

Best regards,

Eugene

[Former Member · Accepted solution]

Eugene,

Thanks a lot. It has resolved my issue.

Awarded the points..

Cheers

Rajiv