Connecting to the DI from MS Excel

[Former Member]

This code snippet works fine from vb.net, however, I can't get a connection from Excel. I added a reference to SAPbobsCOM. Everything is fine up until I call .connect.

-

---

Sub MyMacro()

Dim vCompany As SAPbobsCOM.Company

Set vCompany = New SAPbobsCOM.Company

'set parameters for connection

vCompany.CompanyDB = "MyCompany"

vCompany.Password = "MyPassword"

vCompany.UserName = "MyUsername"

vCompany.Server = "MyServer"

vCompany.UseTrusted = False

vCompany.DbUserName = "MyDBUsername"

vCompany.DbPassword = "MyDBPassword"

vCompany.Connect

If vCompany.Connect <> 0 Then

MsgBox "Failed to connect"

Exit Sub

End If

End Sub

-

---

Any ideas?

TIA

[Former Member · Accepted solution]

Hello TIA

From the check that i have done,

i think that you are connected to the company but you check in the condition is wrong

it should be:

If vCompany.<b>Connected</b> = False Then
MsgBox "Failed to connect"
Exit Sub
End If

Regards, Avi

[Former Member · Accepted solution]

Avi - thans for the response. The .connect method of the company object looks like it returns a Long.

In my case, it's returning a -116 which means I am not connecting.

-

---

Company.Connect Method Syntax

Public Function Connect() As Long

Returns

Returns a result value that indicates success or failure. If the method succeeds, it returns 0. Otherwise, it returns an error code.

You can retrieve the last error code and its description using the method GetLastError

-

---

If I call GetLastError, it returns a 0 indicating a no error condition.

Does anyone have a working VBA macro for excel that includes the connection?

All I did to prep was add a reference to the DI. Do I have to reference any other objects? I can run SQL queries from Excel in the standard manner.

Thanks in advance!

[Trinidad · Accepted solution]

You should call company.Connect() only once. The -116 error number is because you call it twice (The message is "Already connected to a company database")

To have the string explaining the reason of the error you must use the function company.GetLastError.

Here you have a sample of how to use it:

lResult = pCompany.Connect()

'Display the result

If (lResult = 0) Then

MsgBox("Successfully connected to the company " + pCompany.CompanyDB)

Else

pCompany.GetLastError(lResult, errStr)

MsgBox("Connect has failed (Error " + Str(lResult) + ": " + errStr + ")")

End If

You have also a sample if you consult the DI API help file, then go to the Company object.

To have the list of possible errors you can also have a look intot the DI API Help file and look for "Error Codes List".

Hope it helps

Trinidad.

[Former Member · Accepted solution]

Very interesting.

When I use these lines in Excel

vCompany.CompanyDB = "MyDB"

vCompany.Password = "MyPW"

vCompany.UserName = "MyUSER"

vCompany.Server = "MyServer"

The .connect method returns a '0' (connected) without implicitly calling the .connect method! It occurs after setting the .Server property.

I wonder why the code behaves differently in Excel than .net? In my vb.net code, the .connect method does not return a '0' until I call it.

[Trinidad · Accepted solution]

Are you talking about .Connect method or .Connected property?

The .Connect method connects you to the DI API. You must call it only once.

The .Connected property give you the information about your company, it is connected or not? You can call it whenever you want, it doesn't change anything.

Regards,

Trinidad.

[Former Member · Accepted solution]

Trinidad:

Can you tell I am new to the API?

I am working with the 30 day demo while we wait for our "real software" to arrive. So, the docs I am using are limited.

I was indeed confusing .connect and .connected

Thanks for your help!