Connection to SAP from Excel

[rafal_s]

Hi Guys,

I am trying to connect to SAP from Excel. I have this bit of code in my macro:

Sub SAPConnect()

Dim sap

Set sap = CreateObject("SAP.Functions")

sap.Connection.User = "user"

sap.Connection.Password = "password"

sap.Connection.Client = "xxx"

sap.Connection.Systemnumber = "xx"

sap.Connection.System = "xxx"

sap.Connection.ApplicationServer = "xxxx"

sap.Connection.Language = "EN"

sap.Connection.GroupName = "xxxx"

sap.Connection.MessageServer = "xxxxxx"

Unfortunately I am not able to connect at all.

I get a run-time error "1001" Failed to return dispatch object right after Set sap = CreateObject("SAP.Functions")

I have searched the web a lot and most of the macros I could find use similar code for the connection.

This code used to work on SAP GUI 6.40 it stopped working as soon as my GUI was updated to 7.20.

Any ideas on how I need to modify the code?

[Former Member]

Hello All,

I was asked if I can integrate Excel to our SAP BI tools for our ad hoc reporting. One question, is there an interface license fee in order to connect to SAP?

Thank in advance!

Jake

[Former Member]

hi all,

win7 and 7.20

I am trying to connect SAP from Excel. I am using the following: code:

If not sap.Connection.Logon(0, False) = True Then

The login window appears. However, not all systems are listed( also the system, that i need).

But wenn i call saplogon from start, application.... i get more systems.

why I have different results?.

Thank in advance!

[michael_hobbins]

Hi Saad: you probably have 2 saplogon.ini files, and SAPLogon reads one and the object you're using in Excel the other.

Check the following folders in your computer to see if you can find them:

* C:\Users\<username>\appdata\Roaming\SAP\Common

* C:\windows\system32

The one in C:\Users\<username>\appdata\Roaming\SAP\Common should be your SAPLogon is using.

Identify the other file and substitute it with the first one.

Cheers,

Michael

[Former Member]

Hi Michael,

Thanks for the note.

saplogon.ini are the same. but I've found that in sap logon another "saplogon bw ini " is registered

Unfortunately I have no permission to change "saplogon bw ini ".

How Ican  get around this?.

[michael_hobbins]

Is this "saplogon bw ini " the ini file the object you're using in Excel is reading?

[Former Member]

Hello Michael,

yes. Excel use "saplogon bw ini .

C:\Program Files\SAP\SAPLogon

ini

C:\Program Files\SAP\SAPLogon\saplogon_bw.ini

Message was edited by: saad haba

[michael_hobbins]

OK, as far as I see it, you'll need your IT Support to help to update the saplogon_bw.ini file

Michael

[Former Member]

Hello Scriptman,

In my company we have to timesheet on weekly basis and we have to maintain a separate data in excel. for which i am exporting a lot data from SAP timesheet fro different people and then i ll have to match all the data.
for simplification i have created one excel file where we are filling the same data as SAP timesheet. but at the end of every month we again have to cross check for mismatches in two data.

Now i want to link SAP data with my excel so that mismatch will not occur.

CAN YOU PLEASE HELP ME CREATING A MACRO TO LINK THE SAP TIMESHEET WITH EXCEL.

[script_man]

Hi Rafal,

I can recommend the following link;

Regards,

ScriptMan

[rafal_s]

Hi Scriptman,

I have looked through that link and I have tried all the other examples of similar code provided, with the same result - Run-time error "1001" Failed to return dispatch object

I have also checked the "SAP: Remote Function Call: support COM 1.0 Type Library" reference like you mentioned in one of the posts and I can see it is linked to Excel.

To add some more info to my original post:

1.) I do have scripting installed and enabled. I even reinstalled the GUI just to be sure I'm not missing any component. I have also checked librfc32.dll - it is in c:\windows\system32. I even did the regsvr32.exe librfc32.dll

2.) I have enough rights on my Windows XP machine - my user is in the administrators group

3.) Macros are enabled on Excel 2007.

4.) I was able to run the same macro on the 6.4 GUI + Excel 2007. The only thing that has changed now is the GUI has been upgraded to 7.2 version.

[script_man]

Hi Rafal,

Somewhere I read that is also needed wdtlog.ocx and still wdtfuncs.ocx.

Regards,

ScriptMan

[rafal_s]

Added these as well - didn't help one bit

[script_man]

Have you ever tried it?

Set sap = CreateObject("SAP.Functions.Unicode")

wdtlogU.ocx and wdtfuncU.ocx are required.

[rafal_s]

Now there's bit of a progress

I can get through couple of lines of the code without an error message although the Excel crashes right after the check if the logon was succesful or not:

If sap.Connection.Logon(0, True) <> True Then

----

---

Crash ----

---

MsgBox "Cannot Log on to SAP :("

I don't get the logon prompt window at all instead Excel just crashes.

I did a bit of debugging and if I put a breakpoint on this line:

If sap.Connection.Logon(0, True) <> True Then

and then try and do the ? sap.ConnectionLogon in the Immediate window I get a SAP logon prompt with all the correct information.

So I guess now the problem is that the logon prompt does not display when running the script....

[script_man]

You could try also the following:

If not sap.Connection.Logon(0, False) = True Then

Thus, the login window should appear.

[rafal_s]

So now I get the logon window but Excel crashes nevertheless.It just exists completely without throwing any error (process gets terminated).

I tried to put in a wrong username and then trying to log in but that didn't change the behaviour at all. Excel just terminates without a warning.

[rafal_s]

Hi Scriptman,

Problem solved !

As it appears wdtlog.ocx file was missing from c:\Program Files\Common Files\SAP Shared\

Once I copied it there from a different machine and registered it everything started working

Thanks again for all your help.