Skip to Content
0
Former Member
Nov 28, 2006 at 09:18 PM

Loosing Connection after Login

48 Views

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.