Databases
StackOverflow2013
Postsdbo
POExporting C function from C# and using it in VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExporting C function from C# and using it in VBA
    primarykey
    data
    text
    <p>I use the solution from Robert Giesecke <a href="http://sites.google.com/site/robertgiesecke/Home/uploads/unmanagedexports" rel="nofollow">http://sites.google.com/site/robertgiesecke/Home/uploads/unmanagedexports</a> to export functions from managed code to unmanaged code. The solution works pretty good, but there is a problem using the solution with office (excel).</p> <p>I tried to develop a DLL which</p> <ul> <li>connects to SQLServer </li> <li>by using SQLAuthentication</li> <li>passing the name of the database</li> <li>passing the SQL-Statement</li> <li>and returning the result</li> </ul> <p>so the user of the DLL cannot see the password, I know it can be done by using special tools. This way to do it is sufficient for our requirement.</p> <p>The code in C#:</p> <pre><code>using System; using System.Collections.Generic; using System.Text; using RGiesecke.DllExport; using ADODB; using System.Xml; using System.IO; using System.Security.Cryptography; using System.Runtime.InteropServices; using System.Windows.Forms; namespace SqlConRVT { public static class SqlConRVT { [DllExport("SqlConRVT", CallingConvention = CallingConvention.StdCall)] [return: MarshalAs(UnmanagedType.IDispatch)] public static Object OpenRecordset ([MarshalAs(UnmanagedType.AnsiBStr)] string databaseName, [MarshalAs(UnmanagedType.AnsiBStr)] string commandText) { if (String.IsNullOrEmpty( databaseName)) throw new ArgumentNullException("databaseName"); if (String.IsNullOrEmpty( commandText)) throw new ArgumentNullException("commandText"); try { var connection = new ADODB.Connection(); var intConnectionMode = (int) ConnectModeEnum.adModeUnknown; var username = Crypto.DecryptMessage("XEj0PC2lMIs=", "FinON"); var password = Crypto.DecryptMessage("7YIDPO7eBoFAhskAX6JGAg==", "FinON"); connection.Open("Provider='SQLOLEDB';Data Source='PETER-PC\\SQLEXPRESS'; Initial Catalog='" + databaseName + "';", username, password, intConnectionMode); var rs = new Recordset(); rs.Open(commandText, connection, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic, -1); return rs; } catch (Exception ex) { // an exception in a DLL will most likely kill the excel process // we really dont want that to happen MessageBox.Show(ex.Message, ex.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } } } public partial class Crypto { public static string DecryptMessage(string encryptedBase64, string password) { TripleDESCryptoServiceProvider des = new TripleDESCryptoServiceProvider(); des.IV = new byte[8]; PasswordDeriveBytes pdb = new PasswordDeriveBytes(password, new byte[0]); des.Key = pdb.CryptDeriveKey("RC2", "MD5", 128, new byte[8]); byte[] encryptedBytes = Convert.FromBase64String(encryptedBase64); MemoryStream ms = new MemoryStream(encryptedBase64.Length); CryptoStream decStream = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write); decStream.Write(encryptedBytes, 0, encryptedBytes.Length); decStream.FlushFinalBlock(); byte[] plainBytes = new byte[ms.Length]; ms.Position = 0; ms.Read(plainBytes, 0, (int)ms.Length); decStream.Close(); return Encoding.UTF8.GetString(plainBytes); } } } </code></pre> <p>My Code in VBA:</p> <pre><code>Declare Function SqlConRVT Lib _ "C:\Users\Administrator\Documents\Visual Studio 2008\Projects\SqlConRVT\SqlConRVT\bin\Debug\x86 \SqlConRVT.dll" (ByVal databaseName As String, ByVal commandText As String) As Object Sub SQLCon() Dim x As Object x = SqlConRVT("Adressen", "Select * from tblAdressen") End Sub </code></pre> <p>In the C# DLL and in all client applications I reference "Microsoft ActiveX Data Object 2.8 Library".</p> <p>I tried to use the exported 64bit DLL with C#, works fine. I tried to use the exported 64bit DLL as a static class with C#, works fine. I tried to use the exported 32bit DLL with VB6, the application crashes. I tried to use the exported 32bit DLL with VBA (Excel), the application crashes.</p> <p>I checked the existence of the exported function in the 32bit DLL with dependency walker. </p> <p>Why can't I use the 32bit DLL with office (Excel)?</p> <hr> <p>Of course I have 32-bit Office!</p> <p>Your "simplified example" works fine, the class is given back correctly!</p> <p>I reduced my example:</p> <pre><code>using System; using System.Collections.Generic; using System.Text; using RGiesecke.DllExport; using ADODB; using System.Xml; using System.IO; using System.Security.Cryptography; using System.Runtime.InteropServices; using System.Windows.Forms; [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)] static class SqlConRVT { [DllExport(CallingConvention = CallingConvention.StdCall)] [return: MarshalAs(UnmanagedType.IDispatch)] //[return: MarshalAs(UnmanagedType.I4)] //[return: MarshalAs(UnmanagedType.AnsiBStr)] static Object GetNewObject([MarshalAs(UnmanagedType.AnsiBStr)] String databaseName, [MarshalAs(UnmanagedType.AnsiBStr)] String commandText) { var test = new StreamReader("C:\\lxbu.log"); return test; //var rs = new Recordset(); //return rs; //int A = 1; //return A; //String A = commandText; //return A; } } </code></pre> <p>My Code in VBA:</p> <pre><code>Declare Function GetNewObject Lib "C:\Users\Administrator\Documents\Visual Studio 2008\Projects\An\An\bin\Debug\x86\An.dll" (ByVal databaseName As String, ByVal commandText As String) As Object Sub An1() Dim x As Object Set x = GetNewObject("Adressen", "Select * from tblAdressen") End Sub </code></pre> <p>If I try to return an int-value -> works correct! If I try to return a string-value -> works correct! If I try to return an object (e.g a recordset-object or a streamreader-object) Excel crashes? There must be a stupid little error!</p> <hr> <p>Thank you Robert - as everytime your code is perfect! I can see the content of the streamreader object if I use the following code in VBA</p> <pre><code>MsgBox instance.ReadtoEnd() </code></pre> <p>and the result is:</p> <p>"abc Äö ~éêè @dkfjf -> Added fro VBA"</p> <p>The problem is definitly the ADODB.connection!!!!!</p> <pre><code> [DllExport(CallingConvention = CallingConvention.StdCall)] [return: MarshalAs(UnmanagedType.IDispatch)] static Object GetNewObject([MarshalAs(UnmanagedType.LPStr)] String databaseName, [MarshalAs(UnmanagedType.LPStr)] String commandText) { //if (String.IsNullOrEmpty(databaseName)) throw new ArgumentNullException("databaseName"); //if (String.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText"); { var connection = new ADODB.Connection(); //var rs = new Recordset(); StreamReader sr = new StreamReader("C:\\lxbu.log"); //var intConnectionMode = (int)ConnectModeEnum.adModeUnknown; //var username = "..."; //var password = "........."; //connection.Open("Provider='SQLOLEDB';Data Source='PETER-PC\\SQLEXPRESS'; Initial Catalog='" + databaseName + "';", username, password, intConnectionMode); //rs.Open(commandText, connection, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic, -1); return sr; } } </code></pre> <p>If I use "var connection = new ADODB.Connection();" Excel crashes. The problem is using ADODB in 32bit DLL (C# and using the 64bit-DLL no problem). There's no problem (!!!) with your solution!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeter Sapl
    1. USPeter Sapl
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExporting C function from C# and using it in VBA
      1. USRobert Giesecke
      1. VTFavorite
    1. COWhy don't you use VSTO or COM. Taking a trip into unmanaged land makes things way more complicated than you need.
      singulars
      1. POExporting C function from C# and using it in VBA
      1. USDavid Heffernan
    2. CO@David: COM is unmanaged, but yeah for accessing C# libraries from VBA I definitely would advise the COM route.
      singulars
      1. POExporting C function from C# and using it in VBA
      1. USBen Voigt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload