Excel connect to SAP with login

[JackDanIce]

Hi,

I'm trying to create a spreadsheet for a client where a user providers there name and password to connect to and download data from SAP.

First problem: I do not have SAP to test with, so can only do so when I teleconference with the client.

Code I've tried to put together, with errors on red line, ~ connection type is wrong or invalid child index (I forgot to note error specific wording). Parts in blue, removed for readability:

Sub CCRefresh()

Dim SAPGUIAUTO      As Object
Dim SapTest         As Object
Dim SAPCon          As Object
Dim session         As Object
Dim x               As Long
Dim i               As Long

'Some code to create Userform for User to input Username and password

x = 1
While x < 10 And SAPGUIAUTO Is Nothing
    x = x + 1
    If Not IsObject(SAPGUIAUTO) Then
        Set SAPGUIAuto = GetObject("SAPGUI")
        Set SapTest = SAPGUIAUTO.GetScriptingEngine
        If SapTest.Connections.Count > 0 Then
            For i = 0 To SapTest.Connections.Count - 1
                Set SAPCon = SapTest.Children(i)
                If Not IsObject(session) Then Set session = SAPCon.Children(i)
                If SAPCon.Description() = "PRD" Then
                    Set SAPCon = SapTest.OpenConenction("PRD")
                    Exit For
                End If
            Next i
        End If
    End If
Wend

If Not IsObject(Connection) Then Set SAPCon = SapTest.Children(i)

If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject SapTest, "on"
End If

 'VBS Script code, recorded from SAP and works fine, removed from here

    Set SAPGUIAUTO = Nothing
    Set SapTest = Nothing
    Set SAPCon = Nothing
    Set session = Nothing

End Sub

Any suggestions to correct or existing/preferred code to connect from Excel to SAP?

TIA,
Jack

 

[JackDanIce]

bump