locked
DLL Interface to Excel needed RRS feed

  • Question

  • I have an off-the-shelf application which allows for communication with any software that supports a DLL Interface.
    I'd like to create a COM DLL that communicates with Excel with just rudimentary function calls:

    1) Open a workbook
    2) Select a worksheet
    3) update a cell
    4) update a row from an array
    5) update a column from an array
    6) Save workbook

    Basic stuff really. How do I go about doing this ? Where is the documentation for doing this ?

    Friday, November 6, 2020 4:12 PM

Answers

  • So you need to create a DLL that can be called by your off-the-shelf software according to its specifications.

    The functions exported by that DLL and called by your off-the-shelf software can be used to automate Excel through its object model (COM objects).

    At this point the ball is in your court.  I'm not volunteering to write code for you.

    If you decide to devote the time and effort to pursue your solution I have no doubt that the community will offer guidance and suggestions to help you resolve future questions and problems as your work progresses.

    • Marked as answer by Syswizard Sunday, November 8, 2020 6:18 PM
    Saturday, November 7, 2020 6:00 PM

All replies

  • There is no shortage on the internet of examples that show how to automate Microsoft Excel using various programming languages.  Any search engine should return a mountain of links for you.

    You will probably want to read about Excel's object model on Microsoft's site.  For example https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model.  Although this material refers to VBA, there is also similar documentation for the managed code interop from C# or or VB.net.  Unmanaged C++ can also be used.

    Friday, November 6, 2020 5:30 PM
  • Hi, if I understand your question, you basically need to build a COM object that implements Automation, (formally OLE Automation), on Excel. Search for "Excel OLE Automation", this is some sample code from:

    https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/createobject-function

    Dim xlApp As Excel.Application 
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.WorkSheet
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add //add empty workbook
    Set xlSheet = xlBook.Worksheets(1)
    
    xlBook.Open(filePath) //open existing file
    
    
    
    

    You need to put your code in a class and make that class a COM object and it should be visible for "interop" or "COM Visible" and make it public, every language is different, in C# you will have to declare something like this:

    using System.Runtime.InteropServices;
    
    namespace MyExcelOleClasses 
    {
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("MyExcelLib.MyExcelOleLib")]
    
      public class MyFirstExcelOLEClass
      {
        //your code and methods here
        // .
        // .
        // .
      }
      
      
    }


    Luis

    • Proposed as answer by LazyCod3r Friday, November 6, 2020 7:49 PM
    Friday, November 6, 2020 7:44 PM
  • Thanks much guys. One final question: What's the difference between managed and unmanaged code ?
    Saturday, November 7, 2020 4:15 PM
  • Thanks much guys. One final question: What's the difference between managed and unmanaged code ?

    Generally speaking, managed code is written in languages like C# or VB.Net and depends on some version of the .Net Framework (.Net) and the Common Language Runtime (CLR).

    Unmanaged code is generally written in C or C++ and does not depend on .Net or the CLR but might have dependencies on the C/C++ runtime libraries.

    Saturday, November 7, 2020 4:33 PM
  • As far as terminology goes, what you call a COM DLL is often taken to mean an in-process COM server.  What this means is that the in-process server hosts COM objects that can be instantiated by COM client applications.

    Microsoft Excel is an out-of-process COM server (i.e., an .exe).  A COM client can instantiate the COM objects hosted in an out-of-process server without the need to create a separate in-process server.

    A "DLL interface" is often taken to mean that a DLL written in unmanaged code exports functions that can be called.  Typically, an in-process COM server (i.e., COM DLL) only exports the few functions required by COM.

    So when you use those terms its not really clear (at least not to me) exactly what you are asking for.



    • Edited by RLWA32 Saturday, November 7, 2020 5:19 PM
    Saturday, November 7, 2020 4:45 PM
  • re: "Microsoft Excel is an out-of-process COM server (i.e., an .exe).  A COM client can instantiate the COM objects hosted in an out-of-process server without the need to create a separate in-process server. A "DLL interface" is often taken to mean that a DLL written in unmanaged code exports functions that can be called. "

    For the most, that is what I am looking to do.

    Saturday, November 7, 2020 5:44 PM
  • So you need to create a DLL that can be called by your off-the-shelf software according to its specifications.

    The functions exported by that DLL and called by your off-the-shelf software can be used to automate Excel through its object model (COM objects).

    At this point the ball is in your court.  I'm not volunteering to write code for you.

    If you decide to devote the time and effort to pursue your solution I have no doubt that the community will offer guidance and suggestions to help you resolve future questions and problems as your work progresses.

    • Marked as answer by Syswizard Sunday, November 8, 2020 6:18 PM
    Saturday, November 7, 2020 6:00 PM