Excel VBA Connection to SAP RFC

Question

I have the following error when trying to connect to SAP RFC from VBA in Excel:

CPIC(TCP/IP) on local host with Unicode

RFC_COMMUNICATION_FAILURE

I am using the following VBA code which seems to be the well documented way to connect:

Public Sub Connection_SAP()

Dim oFunc As SAPFunctionsOCX.SAPFunctions
Dim oConn As SAPLogonCtrl.Connection
Dim SAPConn As Integer

Set oFunc = CreateObject("SAP.Functions.Unicode")
If Not IsObject(oFunc) Then
   MsgBox "CreateObject(SAP.Functions.Unicode) failed", vbOKOnly, _
     "Error"
   Exit Sub
End If

Set oConn = oFunc.Connection()
If Not IsObject(oConn) Then
  MsgBox "SAPFunc.Connection failed", vbOKOnly, "Error"
  Exit Sub
End If

oConn.Client = "100"
oConn.User = "username"
oConn.Password = "password"
oConn.System = "prod02" ' db data - Name?
oConn.Language = "EN"
oConn.HostName = "sap01" ' db host or host data Server Name?

SAPConn = oConn.Logon(0, vbFalse)
If SAPConn <> 0 Then

  oConn.Logoff
Else
  MsgBox "Connection.Logon failed", vbOKOnly, "Error"
End If

End Sub

sap01 server is responding to ping. The rest of the connection parameters I got from SAP Business Client Menu - System - Status.

Host data

Server name

Database Data

Name

Host

The database data Host name responds to ping. Host data Server Name does not. All the variations I have tried result in the same error.

UPDATE: Needs to be a solution to work with SSO.