locked
Trying to UPDATE cells in Excel 2007 using OLEDB but only 1st row is updating. RRS feed

  • Question

  • User-836379738 posted

    Hello Friends,

    I have an ASP.NET 2005 web app. My requirement is to populate pre-determined cells in a workbook uploaded by the user.

    The steps to my process are as follows:
    Step 1: Upload an Excel file (xls, xlsm, or xlsx) to web server.
    Step 2: Run queries to retrieve data.
    Step 3: Update specific cells in Excel file using OLEDB.
    Step 4: Prompt user to download file.

    The problem I run into (which only occurred after I deployed to Windows Server 2003) is no matter what row/column I specify in the UPDATE statement, it always updates the first row. For example:

    UPDATE [Sheet1$B10:B10] SET F1=123
    UPDATE [Sheet1$B11:B11] SET F1=456
    UPDATE [Sheet1$B12:B12] SET F1=789

    This will update cell B1 three times and the final result is 789 in cell B1 even though I wanted to update B10, B11, B12.

    I have searched online and have seen the problem posted a handful of times but nobody has posted a resolution. It works fine locally and it works fine with an xls file but this problem happens with xlsx & xlsm files using the ACE.OLEDB.12.0 driver. I have reinstalled the drivers and rebooted the server a few times with no change in the result.

    Here's my connection string logic:

                    Select Case Right(UCase(sWorkBook), 4)
                        Case "XLSX"
                            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sWorkBook & _
                                          ";Extended Properties='Excel 12.0 Xml;HDR=NO'"
    
                        Case "XLSM"
                            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sWorkBook & _
                                          ";Extended Properties='Excel 12.0 Macro;HDR=NO'"
    
                        Case ".XLS"
                            sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkBook & _
                                          ";Extended Properties='Excel 8.0;HDR=NO'"
                        Case Else
                            Throw New Exception("Invalid file extension!")
                    End Select

    Any help is very much appreciated!

    Thanks! Mike

    Wednesday, July 13, 2011 2:53 PM

Answers

  • User-836379738 posted

    We resolved the issue with OLEDB only updating the 1st row in Excel. There is a Service Pack & Hot Fix that needed to be installed. Wanted to share the links with everyone in case you ever encounter a similar issue in the future.

    1. Office 2007 data driver:

    http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en.

    2. Install the Office 2007 data driver SP2

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;957262.

    http://www.microsoft.com/downloads/en/details.aspx?FamilyId=6F4EDEED-D83F-4C31-AE67-458AE365D420&displaylang=en

    3. Install the hot fix:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;968861.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 21, 2011 10:36 AM