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