Hi,
After looking up lot of code snippets in this forum I have managed to get a connection to SAP Server from my Excel Wrokbook using VBA. But here is where I get stuck. Once connected I am still Unable to Refresh the Query in my Workbook.
Here is the code I have so far its from what I have found in posts on the forum.
'Peter K's Code.
Public Function BEXisLoaded() As Boolean
Dim vbp As Object
BEXisLoaded = False
'ensure that we have access to VB Project
On Error Resume Next
Err.Clear
Set vbp = ThisWorkbook.VBProject
If vbp Is Nothing Or Err Then
Err.Clear
MsgBox "Please set Macro Security to" & vbCrLf _
& "Trust Access to Visual Basic Project", vbExclamation, _
"Macro Security error ..."
Exit Function
End If
'see if BEx Analyzer is loaded already
For Each vbp In Application.VBE.vbprojects
If vbp.Name = "SAPBEX" Then
BEXisLoaded = True
Exit Function
End If
Next vbp
If Not BEXisLoaded Then
Err.Clear
On Error Resume Next
If Dir("C:Program FilesSAPFrontEndBwsapbex.xla") = "sapbex.xla" Then
BEXisLoaded = True
Workbooks.Open(Filename:="C:Program FilesSAPFrontEndBwsapbex.xla").RunAutoMacros _
Which:=xlAutoOpen
Else
If Err Then MsgBox Err.Description, vbInformation, "Error: " & Err.Number
BEXisLoaded = False
End If
End If
End Function
Public Function LogonToBW() As Boolean
On Error GoTo Err_LogonToBW
Dim objCon As Object
'g_IsConnected will help to keep relogging in all the time.
If g_IsConnected <> 0 Then
Exit Function
End If
LogonToBW = False
If BEXisLoaded() = True Then
With Run("sapbex.xla!sapbexGetConnection")
'.client = "100" 'Our Prod Server
'.user = "" 'ID to Connect
'.Password = "" ' Password to Connect
'.Language = "EN" ' Defaulting to
'.SystemNumber = "YOUR SYSTEM NO" '
'.ApplicationServer = "BWP"
' Can be Set to False then
'We need to supply all the Info I am including here
.UseSAPLOgonIni = True ' If you want the profile.
If .isConnected <> 1 Then
.logon 0, False ' Makes the Box Appear.
If .isConnected <> 1 Then
MsgBox "Unable to Login to BW"
LogonToBW = False
Exit Function
End If
End If
End With
Run "sapbex.xla!sapbexinitConnection" ' this will enable the connection you just
LogonToBW = True
g_IsConnected = 1
End If
Exit_LogonToBW:
Exit Function
Err_LogonToBW:
MsgBox Err.Description
Err.Clear
LogonToBW = False
Resume Exit_LogonToBW
End Function
Public Sub RefreshQueries()
If LogonToBW() Then
If Run("sapbex.xla!SAPBEXrefresh", True) <> 0 Then
MsgBox " Error in Refresh"
End If
End If
End Sub
I have a Button on sheet1 "Refresh Query" Which Calls RefreshQueries from above.
Looks like nothing happens. But I do have the toolbar added to the Workbook and only way it works now is that I have to Login again and refresh.
Any Idea's.
TIA
Datta.