Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending a program instance to Excel (Excel 97 and greater)

    I am considering developing a program which uses Excel for some calculations. The program I imagine would open excel and use it via automation. Is there a way to pass an instance of a class to Excel? I would have some partially processed data that Excel would need to access via VBA. What I could do is make the data into an ActiveX exe and then Excel's VBA could use GETOBJECT(, "Object.class") to get a handle on the running Exe. The problem is that there could be multiple processes like this on a machine and I would not be able to know if the GETOBJECT is getting the instance that I want it to get. So what I really want is to pass a handle to Excel so I could use a particular instance of an object. Does anyone know how to do this? Thanks in advance.
    Wayne

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending a program instance to Excel (Excel 97 and greater)

    This sounds a bit overdone to me. Couldn't you just leave all code in your application?

    This is a rather silly example in Word VBA that uses XL to use it's AVERAGE worksheet function:

    Option Explicit

    Sub TestSomethingWithXL()
    Dim oXL As Excel.Application
    Dim dArray(1 To 10) As Double
    Dim dResult As Double
    Dim iCount As Integer
    Set oXL = New Excel.Application
    For iCount = 1 To 10
    dArray(iCount) = iCount ^ iCount
    Next
    dResult = oXL.Application.WorksheetFunction.Average(dArray)
    MsgBox dResult
    oXL.Quit
    Set oXL = Nothing
    End Sub
    To make it work, the project needs a reference to the Excel object model (Tools, references)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quite a bit more complicated

    Jan, thank you for taking the time to reply. I am thinking of something more complicated then what you are imagining so let me give more of the story.

    Business need:
    My goal is to create a standard calculation program for my industry. While I can imagine the most common 80% of the situations and provide common parameters, I need to be able to account for the other 20%. In the past, this has meant that I would create custom compiled components for a particular client. What I want to do instead is to allow the people with the specific client business knowledge to be able to define the calculation in Excel. My users are all well versed in Excel and some would even dare to use VBA.

    Proposed solution:
    I imagine a program that would read in the data source which would have both standard and client specific data. Then it would perform some parameterized calculations and other calculations specified in Excel. What I want to do is to allow the user to get access to all of the data that I have read into memory as well as access to the parameters in the 'intermediate step' calculations that have been parameterized. They could then perform their calculations in Excel and return the information to the common in-memory database for continued parameterized or other calculations. So I need more than Excel to have the ability to make an object that I design. I need it to accept a particular instance of an object and use it for it's needs.

    The issues that I see with this are:
    1. Excel scalability - I see this as a 'throw hardware at the problem' kind of thing
    2. Sending an instance of an object ot Excel - this is the question I am asking about. I need to send both the data to Excel, receive data back (have the data altered and added to) and send a callback object so Excel can restart the main program only once all calculations have been made.

    Thanks

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Quite a bit more complicated

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Wayne

    OK lets see if I can sum this up in a couple of words:

    1) Have an MS-Excel workbook designed to accept all the data and calculations that you need to do. The data will be accessible via any of the well-known methods; ADO, DAO, ODBC and then some VBA code run the calculations.

    2) MS-Excel will save the data, after it
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quite a bit more complicated

    Hmm. A bit over my head here I'm afraid.

    I guess you could have Xl get acquainted with your program by setting a reference to it from Xl, just like you would set a reference to the Word objetct model when you would want to use Word's functionality in XL. If your project is setup in such a way that it exposes it's objects, then I guess that should be possible. But I have no experience in these matters whatsoever.

    Let us know how you get along!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •