Skip to Content
0
Former Member
Dec 04, 2006 at 11:30 PM

Need help on VB macros

72 Views

Hello BW reporting Gurus,

I have an issue with the VB macros code.

This issue is because of the version change.

Peter(the VB macros Guru) gave the complete coding for my task.

Code is working perfectly in BW 3.5 Bex.

I am very much glad and thankful to him for his quick response and help.

But the frustrating thing is , in BI 7.0 Bex the same code is not getting recognised.

This is the piece of code

<b>Private Function BExIsLoaded() As Boolean

Dim vbp As Object

'Check if SAPBEX.xla add-in is available

BExIsLoaded = False

On Error Resume Next

Set vbp = ThisWorkbook.VBProject

If Err.Number <> 0 Then

Err.Clear

MsgBox "From Excel menu, select Tools >> Options." & vbLf _

& "On Security tab, select Macro Security." & vbLf _

& "On Trusted Publishers tab, check box for" & vbLf _

& """Trust access to Visual Basic Project""", _

vbInformation, "Please update settings for XME."

Exit Function

End If

For Each vbp In Application.VBE.VBProjects

If vbp.Name = "SAPBEX" Then

BExIsLoaded = True

Exit For

End If

Next vbp

If Not BExIsLoaded Then

MsgBox "Please load BW Analyzer.", vbCritical, _

"BW Analyzer is not loaded."

End If

End Function

Sub RefreshBoth()

On Error GoTo leave

If Not BExIsLoaded Then GoTo leave

ThisWorkbook.Activate

Run "sapbex.xla!SAPBEXrefresh", True

leave:

End Sub

Sub RefreshCurrentMonth()

Dim r As Range

On Error GoTo leave

If Not BExIsLoaded Then GoTo leave

ThisWorkbook.Activate

Set r = ThisWorkbook.Sheets("CURRENT MONTH").Range("A1")

Run "sapbex.xla!SAPBEXrefresh", False, r

leave:

End Sub

Sub Refresh12Months()

Dim r As Range

On Error GoTo leave

If Not BExIsLoaded Then GoTo leave

ThisWorkbook.Activate

Set r = ThisWorkbook.Sheets("PAST 12 MONTHS").Range("A1")

Run "sapbex.xla!SAPBEXrefresh", False, r

leave:

End Sub

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim wsName As String

Dim firstRow As Long, lastRow As Long

firstRow = resultArea.Rows(1).Row

lastRow = firstRow + resultArea.Rows.Count - 1

wsName = resultArea.Parent.Name

Select Case wsName

Case "CURRENT MONTH"

Call ConsolidateCurrentMonth(firstRow, lastRow)

Case "PAST 12 MONTHS"

Call Consolidate12Months(firstRow, lastRow)

Case Else

MsgBox "Oops"

End Select

End Sub

Sub ConsolidateCurrentMonth(firstRow As Long, lastRow As Long)

'Copy the occupied rows from the CURRENT MONTH worksheet

'to CONSOLIDATED DATA worksheet

Application.DisplayAlerts = False

TargetSheet = ActiveSheet.Name

For x = 19 To 24

Sheets("CONSOLIDATED DATA").Range("A" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("B" & x).Value = ""

Next

Sheets("CURRENT MONTH").Activate

j = 19

For i = firstRow To lastRow

If Range("A" & i).Value = "" Then

'MsgBox "The Cell is Empty"

Else

a = Range("A" & i).Value

b = Range("B" & i).Value

Sheets("CONSOLIDATED DATA").Range("A" & j).Value = a

Sheets("CONSOLIDATED DATA").Range("B" & j).Value = b

j = j + 1

End If

Next

Sheets("CONSOLIDATED DATA").Activate

MsgBox "Finished1"

End Sub

Sub Consolidate12Months(firstRow As Long, lastRow As Long)

'Copy the occupied rows from PAST 12 MONTHS WORKSHEET

'to CONSOLIDATED DATA worksheet

Application.DisplayAlerts = False

TargetSheet = ActiveSheet.Name

For x = 26 To 36

Sheets("CONSOLIDATED DATA").Range("G" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("H" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("I" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("J" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("K" & x).Value = ""

Sheets("CONSOLIDATED DATA").Range("L" & x).Value = ""

Next

Sheets("PAST 12 MONTHS").Activate

j1 = 25

For i1 = firstRow To lastRow

If Range("F" & i1).Value = "" Then

'MsgBox "The Cell is Empty"

Else

a1 = Range("A" & i1).Value

b1 = Range("B" & i1).Value

c1 = Range("C" & i1).Value

d1 = Range("D" & i1).Value

e1 = Range("E" & i1).Value

f1 = Range("F" & i1).Value

g1 = Range("G" & i1).Value

'Sheets("CONSOLIDATED DATA").Range("C" & j1).Value = a1

'Sheets("CONSOLIDATED DATA").Range("D" & j1).Value = b1

Sheets("CONSOLIDATED DATA").Range("F" & j1).Value = a1

Sheets("CONSOLIDATED DATA").Range("G" & j1).Value = b1

Sheets("CONSOLIDATED DATA").Range("H" & j1).Value = c1

Sheets("CONSOLIDATED DATA").Range("I" & j1).Value = d1

Sheets("CONSOLIDATED DATA").Range("J" & j1).Value = e1

Sheets("CONSOLIDATED DATA").Range("K" & j1).Value = f1

Sheets("CONSOLIDATED DATA").Range("L" & j1).Value = g1

j1 = j1 + 1

End If

Next

Sheets("CONSOLIDATED DATA").Activate

MsgBox "Finished2"

End Sub

</b>

When this code is executed by clicking the macros generated "Refresh" Button,its showing a message box "Please load BW Analyzer."

How to solve this problem?

Please help me out.

Thanks is Advance.

Have a nice day

Regards

Sam Mathew