none
Excel 2007 - getting "External table is not in the expected format" error. RRS feed

  • Question

  • Hi,

     I am trying to read data from an Excel 2007 spreadsheet that is in XLSM format from VS2008 using VB.Net (targeting .Net framework 2.0).  I am using the following connection string:

    Dim conn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mFileName & _
                  ";Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1"""

    I am reading a worksheet tab into a strongly-type dataset with the following code:

    Dim sqlFixed As String = "SELECT ProductID, LockPeriodDays, AverageLife, AverageLifeUnits, 
      "RateProductID, Rate " & _
         "FROM [APSFixed$] " & _
         "WHERE ProductID <> ''"
    
    Dim rts As New Rates  'strongly-type dataset
    
    Dim Command As New OleDbCommand(sqlFixed, connection)
    Dim adapter As New OleDbDataAdapter(Command)
    
    adapter.Fill(rts, "APSFixed")

    When I run this, I am getting the "External table is not in the expected format" exception.  If I save it as an XLSX file, I still get the error.  Note that the worksheet I am reading from has formulas that reference other worksheets in the spreadsheet if that matters.  I also tried moving the file to a "trusted location" to see if that helped, but it did not.

    If I create a new tab and paste the data as values into the new tab and try to read that tab, I still get the above exception.

    If I create a new XLSX or XLSM file and paste the data from the original spreadsheet into a same-named tab, I can read that data fine.

    If I save the original XLSM file as an XLS file, then I am able to read the data via the same connection string as above.

    All of my searching on this error seems to point to using the connection string as I have it above as the solution.  Does any one see anything wrong with the above code or have any ideas as to what I need to do to get this to work?

    Thanks,

    Bobby

    Tuesday, April 20, 2010 7:21 PM

Answers

  • Hi Lingzhi,

     

    Thanks for the prompt reply.  I think I may have found the problem.

    I was sanitizing the spreadsheet to send to you by changing values in the sheets.  I then went to run document inspector to see if I needed to clean any other information out.  First, the inspector told me that it could not run because I had protected worksheets.  So I unprotected the protected worksheets and re-ran the inspector.  It told me that that I had 1 hidden worksheet.  So I let it remove that.  I assume that it it unhides it, is that correct?  Or does it actually remove it?  I was unable to find a worksheet to unhide from the Format/Hide and Unhide/Unhide sheet as this option was disabled.

    Anyway, once I had performed the above steps, I was able to access the desired worksheets.  I have the owner of the spreadheet looking into either exporting the worksheets that I need into a new XLSM file or we may do a Save As each time into the XLS format.

    As a test, I restored my copy and just removed the protection from the protected worksheets and did not unhide the other worksheet and was still unable to access the data without getting the error that I got before.  I can only access the data after clicking on Remove All for the Hidden Worksheets.

    Can you tell me whether or not the Remove All command for the hidden worksheet in the document inspector actually deletes or unhides the worksheet?  Also, can you tell me exactly what should stop me from being able to access the data.  Items such as hidden worksheets/cells, protected worksheets/cells, etc.

    Thanks,

    Bobby

    Looks like from my research that the Remove All actually removes the hidden worksheet.

     

    Wednesday, April 21, 2010 2:33 PM

All replies

  • Hello Bobby,

     

    Welcome to MSDN forums!

     

    It’s really hard to troubleshoot this issue without Excel file.   If it is convenient for you, would you mind sending me the demo project and the Excel file?   My mail address is v-micsun@microsoft.com.    Thanks a lot!

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 21, 2010 2:08 AM
    Moderator
  • Hi Lingzhi,

     

    Thanks for the prompt reply.  I think I may have found the problem.

    I was sanitizing the spreadsheet to send to you by changing values in the sheets.  I then went to run document inspector to see if I needed to clean any other information out.  First, the inspector told me that it could not run because I had protected worksheets.  So I unprotected the protected worksheets and re-ran the inspector.  It told me that that I had 1 hidden worksheet.  So I let it remove that.  I assume that it it unhides it, is that correct?  Or does it actually remove it?  I was unable to find a worksheet to unhide from the Format/Hide and Unhide/Unhide sheet as this option was disabled.

    Anyway, once I had performed the above steps, I was able to access the desired worksheets.  I have the owner of the spreadheet looking into either exporting the worksheets that I need into a new XLSM file or we may do a Save As each time into the XLS format.

    As a test, I restored my copy and just removed the protection from the protected worksheets and did not unhide the other worksheet and was still unable to access the data without getting the error that I got before.  I can only access the data after clicking on Remove All for the Hidden Worksheets.

    Can you tell me whether or not the Remove All command for the hidden worksheet in the document inspector actually deletes or unhides the worksheet?  Also, can you tell me exactly what should stop me from being able to access the data.  Items such as hidden worksheets/cells, protected worksheets/cells, etc.

    Thanks,

    Bobby

    Looks like from my research that the Remove All actually removes the hidden worksheet.

     

    Wednesday, April 21, 2010 2:33 PM
  • Something else is weird here.  The Inspect Document tool says there is one hidden sheet.  Looking a the project explorer, there are no sheets that show as hidden.

    I also created some VBA code to loop through the sheets to see if I could see any hidden ones there, but still nothing.  There is only one workbook.

    Sub ShowSheets()
    
      Dim wks As Worksheet
    
      For Each wks In ActiveWorkbook.Worksheets
       MsgBox wks.Name & " - " & wks.Visible
      Next wks
    
    End Sub

    How can the document inspector see a hidden sheet and I cannot?

    Thanks,

    Bobby

    Wednesday, April 21, 2010 7:10 PM
  • Hi Bobby,

     

    I’d like to help but I am not the expert of Excel.  L   From your post, the original issue with the Office 2007 data driver is solved, right?   I would recommend you post the follow-up question on the Excel newsgroup for better support: http://www.microsoft.com/communities/newsgroups/en-us/?dg=microsoft.public.excel.programming.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 8:15 AM
    Moderator
  • Hi Lingzhi,

    Yes, I think this is most likely not a driver issue any more.  I'll post in the other forum.

    Thanks!

    Bobby

    Thursday, April 22, 2010 11:36 AM