none
Excel Min and Max with VSTO RRS feed

  • Question

  • I have a range of about 500k rows. I need to take a conditional min and max. I'm aware of DMAX and DMIN, but is that the smartest solution I can do with VSTO/.NET or should I go to LINQ or DataTables or something else? I'm looking the fastest possible way to do it?

    The SQL equilevant would be
    SELECT MIN(Sale), MAX(Sale), MIN(Date), MAX(Date)
    WHERE Salesman = "Peter Smith"
    FROM EXCELRANGEA1H500000

    So that query from VSTO against Excel range in the fastest way you can imagine would be...?


    Thursday, October 17, 2013 5:42 PM

Answers

  • Hi,

    >>but is that the smartest solution I can do with VSTO/.NET or should I go to LINQ or DataTables or something else? I'm looking the fastest possible way to do it?<<

    Since VSTO Object model is relatively complex, if you want to query much rows of records in the database faster, I suggest you to resort to the ODBC or ADO.NET instead of VSTO. They have more Database Query optimization. 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 8:37 AM
    Moderator

All replies

  • Hi,

    Do you use the Excel VSTO?

    >>I need to take a conditional min and max <<

    What do you mean by this? Do you want to get the min and max valued?

    If so, I think you could choose to use the DMAX and DMIN formulas to get the minimum and maximum values from a table.

    Here is a link for your reference.

    DMAX DMIN Question

    DMIN

    A VB function for your reference: http://social.msdn.microsoft.com/Forums/en-US/3e9ee902-7ab1-4274-ab11-c8ccecaccbc0/excel-vba-min-and-max-function-limits?forum=exceldev

    Friday, October 18, 2013 1:44 PM
  • Hi,

    >>but is that the smartest solution I can do with VSTO/.NET or should I go to LINQ or DataTables or something else? I'm looking the fastest possible way to do it?<<

    Since VSTO Object model is relatively complex, if you want to query much rows of records in the database faster, I suggest you to resort to the ODBC or ADO.NET instead of VSTO. They have more Database Query optimization. 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 8:37 AM
    Moderator