none
Updating Excel Range using ADO.NET RRS feed

  • Question

  • Hi,

    I am trying to update Excel Ranges using ADO.NET. Range could be either a single named cell or a group of cells in my case.

    For the first update, the value gets populated in the cell designated by the range name but the range name moves to a different cell location. Subsequently, the values get populated in the new location of the range. I am not sure why the range name gets moved after the first update.

    My connections string is:
    "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:\\RangeTest.xls;" + "Extended Properties=\"Excel 8.0;HDR=NO\"";

    My command string is: aaa.CommandText = "UPDATE [TestRange] SET [F1] = '12345'";
    I am using F1 because sometimes I do not have any column names for my range.

    Our team is not sure about using Interop as it might require some software on server etc...

    Thanks in advance for your time and efforts.

    Thursday, July 9, 2009 9:20 PM

Answers

  • You are somewhat limited in what you can consider a table for Excel.  You can either write to a whole sheet or you can write to a named range in Excel.  The named range has to be created ahead of time and cannot be created via a call from the driver (you can pre-create the ranges in Excel or via automation).

    If you need more fine grained control then you need to use automation.
    • Marked as answer by Yichun_Feng Friday, August 7, 2009 6:10 AM
    Monday, July 20, 2009 6:06 PM
    Moderator
  • Jet is very limited when it comes to updates or other functionality related to Excel spreadsheets. Keep in mind that spreadsheets are not really tables and do not have any fixed internal structure, so named ranged could be lost during updates. As Matt pointed if you really need full control then Interop (automation) gives this functionality, but it will require Excel installed on computer and Excel will be launched as out-of-process application
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Friday, August 7, 2009 6:10 AM
    Tuesday, July 21, 2009 10:30 AM
    Moderator

All replies

  • You are somewhat limited in what you can consider a table for Excel.  You can either write to a whole sheet or you can write to a named range in Excel.  The named range has to be created ahead of time and cannot be created via a call from the driver (you can pre-create the ranges in Excel or via automation).

    If you need more fine grained control then you need to use automation.
    • Marked as answer by Yichun_Feng Friday, August 7, 2009 6:10 AM
    Monday, July 20, 2009 6:06 PM
    Moderator
  • Jet is very limited when it comes to updates or other functionality related to Excel spreadsheets. Keep in mind that spreadsheets are not really tables and do not have any fixed internal structure, so named ranged could be lost during updates. As Matt pointed if you really need full control then Interop (automation) gives this functionality, but it will require Excel installed on computer and Excel will be launched as out-of-process application
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Friday, August 7, 2009 6:10 AM
    Tuesday, July 21, 2009 10:30 AM
    Moderator