Excel and nCo

[Former Member]

Hi All,

I have managed to create with nCo and VS2003 a call to RFC_READ_TABLE which fills a datagrid which is called from VS2003. What I would like to do is call this from Excel VBA and fill the cells with the returned data. I've got as far as creating the DLL and I can see the objects for the RFC from VBA ok. I thought I would just be able to create an object in the macro like sapproxy1 and call the rfc_read_table method.

The closest I can get is to create a form in my class library and add the sapProxy11 object which references the Destination1 connection as one would do if calling from VS2003. (Please forgive my descriptions as I'm still a newbie here!) In the form I've then created a public sub that calls sapProxy11.rfc_read_table as the form sub can see the rfc there.

Then I create an object in the macro like my class library form and call the public sub but I get the error "key 'Destination1.AppserverHost' does not exist in the AppSettings configuration section"

I guess somehow the connection to R/3 is not being created as it would if I made the call from VS2003?

I also can't help but think I'm going the long way round here!

I still think I should be able to call the RFC in my DLL directly without having to create a form etc. but I suppose I should then create a connection in the macro first??

I was also wondering whether it would be easier to create an Excel add-in with VS2003 and nCo and use this from Excel to achieve the desired result.

Any help greatly appreciated!

Regards,

Grant.

[Former Member]

Hi,

Not sure if I understood your problem correctly. But there are more than one choice to connect to SAP system from Excel VBA-

1. You can use the DCOM way. Where in you need to reference two Activex liabraries (found in SAP GUI installation directory). And call the methods to connect to SAP sysem.

2. OR You can expose RFCs from SAP system as webservices and can write your own proxy classes in Excel VBA to communication to SAP system over SOAP.

3. OR You can use NCo with visual studio to develop a COM Interop dll. Then reference it in Excel VBA and invoke the methods.

4. OR if your targeting only Excel 2007 then read this - http://msdn2.microsoft.com/en-us/library/bb498189.aspx

The choices depends upon many factors like security setup, perfomance, development time, application version constraints etc. etc.

Do let me if you need more information.

Regards.

[Former Member]

Hi Jitesh,

I'm trying to use option 3 - NCo with visual studio to create a COM interop dll.

I made some progess in terms of the "error" message I was getting. It relates to the generated code when one creates the form and adds the logon destination to the form. I was using the server explorer method (drag the app server onto the form) to create all the logon info and in the generated code it refers to the configured settings. I would think that I should be creating the connection from Excel VBA anyway rather than trying to reference it in the dll.

Thanks for your time though,

Regards,

Grant.