cancel
Showing results for 
Search instead for 
Did you mean: 

BEx and VBA

Former Member
0 Kudos

I have created a worksheet with several views of the same query inserted in it. Each query view contains the stock quantity and value of a different product brand.

For each query I would like to calc. the movements(quantity and value) and place them in the next two columns after the result area. Here is what I have done so far.

Sub SAPBEXonRefresh(queryID As String, ResultArea As Range)

Select Case queryID

Case "SAPBEXq0008"

StockValues ResultArea

Case "SAPBEXq0009"

StockValues ResultArea

Case "SAPBEXq00010"

StockValues ResultArea

Case "SAPBEXq00011"

StockValues ResultArea

Case "SAPBEXq00012"

StockValues ResultArea

Case "SAPBEXq00014"

StockValues ResultArea

End Select

End Sub

Sub StockValues(ByRef ResultArea As Range)

Dim lngVar As Long

For lngVar = 1 To ResultArea.Rows.Count - 1

If ResultArea.Cells(lngVar, 2) = "" Then

SetMovementsPost lngVar, ResultArea.Columns.Count, ResultArea

SetMovementsPre lngVar, ResultArea.Rows.Count - 1, ResultArea.Columns.Count, ResultArea

End If

Next

End Sub

Sub SetMovementsPost(vlngvar As Long, vlngColumns As Long, ByRef ResultArea As Range)

Dim lngLoop As Long

Dim lngStart As Long

Worksheets("BW Stock").Select

ResultArea.Cells(vlngvar, vlngColumns).Activate

ActiveCell.Offset(0, 1).Value = ResultArea.Cells(vlngvar, 3)

ActiveCell.Offset(0, 2).Value = ResultArea.Cells(vlngvar, 4)

lngStart = vlngvar - 1

If lngStart > 2 Then

For lngLoop = lngStart To 2 Step -1

ResultArea.Cells(lngLoop, vlngColumns).Activate

ActiveCell.Offset(0, 1).Value = ResultArea.Cells(lngLoop - 1, 3) + ResultArea.Cells(lngLoop, 3)

ActiveCell.Offset(0, 2).Value = ResultArea.Cells(lngLoop - 1, 4) + ResultArea.Cells(lngLoop, 4)

Next

End If

End Sub

Sub SetMovementsPre(vlngvar As Long, vlngRows As Long, vlngColumns As Long, ByRef ResultArea As Range)

Dim lngLoop As Long

Dim lngStart As Long

lngStart = vlngvar + 1

For lngLoop = lngStart To vlngRows

ResultArea.Cells(lngLoop, vlngColumns).Activate

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value - ResultArea.Cells(lngLoop, 3)

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value - ResultArea.Cells(lngLoop, 4)

Next

End Sub

I was under the impression that query ID's will identify each queries result area uniquely and just re-use Sub StockValues however, I can not get the VB code to display the actual movement qty/amounts.

How do I uniquely identify each queries result area or is there another way of achieving this.

PS. I am not a VB expert so please be a bit explicit in your response.

Thanks,

Milind

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Milind,

In the subroutine, SAPBEXonRefresh, the queryID uniquely identifies which query was refreshed. The variable ResultArea identifies the range that contains the query result table.

Try this:

Call StockValues(ResultArea)

or,

Run "StockValues", ResultArea

It should work.

- Pete

Former Member
0 Kudos

Hi Milind,

There is nothing wrong with your code. I apologize for posting yesterday without testing your code first.

What I believe is happening is that the SAPBEXonRefresh routine is NOT running. Try putting a message box inside the SAPBEXonRefresh routine.

Why would this happen? In Excel2002 (and later), there is an additional Macro security feature. This feature is very well hidden. To see if this is the problem, do the following:

+ in Excel menu bar, select Tools >> Options

+ in Options dialog, select the Security tab

+ in that window, select the "Macro Security" (lower right side)

+ in the Security dialog, select the "Trusted Publishers" tab

+ near the bottom of this window, click "on" Trust access to Visual Basic Project.

If my theory is correct, your VBA will now work. But only on machines of people who have also clicked "on" Trust access to Visual Basic Project.

- Pete

Former Member
0 Kudos

Hi Peter,

I already discovered the problem and marked the question as answered.

It was a silly mistake on my part by copying Case "SAPBEXq0009" and just adding 10 (for the next query) at the end hence having to many 0's e.g. "SAPBEXq00010"

Your post had usefull info that might come in handy in future though.

Thanks,

Milind

vinay_bedre2
Participant
0 Kudos

Hello Milind,

You can call the function SAPBEXgetResultRangeByID to Determining a cell area for a query in the active workbook.

for more details have a look at the following link.

http://help.sap.com/saphelp_erp2004/helpdata/en/f1/0a55f9e09411d2acb90000e829fbfe/frameset.htm

I hope its useful for you,

Regards,

Vinay