Hi,
We will just want to describe you of what we are trying to achieve. We are trying to compile a class file to a dll which is compatible on 64 bit Operating Systems and 64 bit Processors.Further we are wanting to call a function of this class file in an Excel VBA macro.The dll function returns a value and the macro reads that value and puts it to a specific cell of an Excel File.
We are successfully able to achieve this on machines running 64 bit Operating Systems and 32 bit Office(Office 2003, Office 2007, Office 2010) and 32 bit Operating Systems. We are facing problems only with a combination of 64 bit Operating Systems and 64 bit Office.
The steps we followed to compile the dll is as follows:
I created a class library in visual studio 2008.The properties set in the Visual Studio Editor is as:-
- Application -->Assembly Information-->Make Assembly COM visible Check box Checked.
- Compile --> Register for COM interop Check box Checked(Which creates 'Sample.tlb' file)
- Signing --> Sign the Assembly.Check Box Checked(Which creates 'Sample.snk' file)
- Set the Target CPU as 'Any CPU' and Target Framework as 'Microsoft framework 2.0' Advanced Compiler Setting
As we build the solution, following files get created in the Release folder.
- Sample.Dll
- Sample.tlb
- Sample.snk
- sample.Pdb
- sample.xml
Now I place the .dll file and .tlb file in system 32 and syswow64 folder.
and then registered the dll as denoted below.-
1. by gacutil /i sample.dll
2. by RegAsm sample.dll
After Registering DLL I Open the Excel then Create a Macro and add a reference of following files-
- .tlb file
- Microsoft ActiveX 6.0 object library(for 64 bit MS Office Excel)
-'Microsoft Office 14.0 Access Database Engine Object'
- Microsoft DAO Object Library 3.6
I create an object of the dll and call the dll function via the object created.
The problem we face is an Error code "429" at the point when in the Macro we call the dll function(Note: on machines with 64 bit Operating System and 64 bit Office). We researched a lot on this issue,but still could not resolve the same. Please, kindly help us address this issue with workaround.
Thank You,
Regards,
Vaibhav Pandya