Connecting to Excel workbook

Question

I'm using following code for upload excel file to the system in VB.NET. This is working for office 2007. But Not for office 2010. How can I change the code for work for all office versions.

If (strFileType.Trim() = ".xls") Then
    connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", target + "\\" + FileUpload1.FileName)
Else
    connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", target + "\\" + FileUpload1.FileName)
    '"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; 
End If

What do you mean by "upload"? To me it looks like you connecting to Excel, not uploading it. Check this http://stackoverflow.com/questions/1461053/diagnosing-an-oledb-exception-when-quering-excel-2010 — T.S., Aug 27 '15 at 05:36
I'm uploading a Excel sheet to the SQL database . Using above connection I'm reading data in Excel sheet — Tom, Aug 27 '15 at 06:03
This is not really called "uploading". This is Data Import. Besides, from your question it wasn't clear that this is what you do. sounds as you rally don't have an issue because most likely you simply have wrong connection string — T.S., Aug 27 '15 at 14:28

score 0 · Answer 1 · answered Aug 27 '15 at 06:17

modify the statement in else part from

connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data    
   Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", target + "\\" +  FileUpload1.FileName)

to

connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", target + 
"\\" + FileUpload1.FileName)

The Extended Properties modified

Connecting to Excel workbook

1 Answers1