How to update analysis for office connection, without manually having to login

[Former Member]

Hi,

I have an Excel workbook with an analysis for office connection. I intended to create a script which will open the Excel workbook, and then use VBA to update the analysis connection. However, I cannot get the login to work, without having to manually type the password. This is the code I am currently using:

Sub SAPBWLogon()
Dim lResult As Long
lResult = Application.Run("SAPLogon", "DS_1", "010", "USER", "PASSWORD", "EN")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
End Sub

As soon as the code gets to the "SAPLogon" line, the login prompt opens and I have to type the password. We are not using SSO within our organization.
As soon as I have typed the password once and I have established the connection, I can run the same code again, without the login dialog appearing.

Using the login code above, I am not providing a server or a system, shouldn't these info be included, or are they stored with the connection already established in the workbook?
How can I avoide the manually login step?

[TammyPowlas]

SSO is the safest way to implement this; I would be worried about the security risks of embedding this in VB code