Click here to Skip to main content
15,918,168 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
See more:
Hi,

I am saving sample data table from database to Excel using OLEDB connection.

If I save the excel file with [.XLS] extension, I can open the file and see the stored data table.

But if I create the Excel file with [.XLSX] format, after moving datatable,
if I try to open it I am getting error like
"File format or file extension is incorrect. Check whether file has corrupted or file extension doesnot match with file format" and I cannot open the Excel file.

I am using Windows 7 and tried installing "Acess Database engine" but still getting the same error.

Below is the src code in which I am trying to create simple excel file with .XLSX format.
(File path selected is C:\\Users\Vijay\Desktop\Recipe.XLSX)


VB
Dim saveDialog As New System.Windows.Forms.SaveFileDialog
        saveDialog.Title = "ExportFile"
        saveDialog.Filter = "MS Office Excel2007|*.xlsx"
        'saveDialog.DefaultExt = "xlsx"
        'saveDialog.SupportMultiDottedExtensions = True
        saveDialog.FileName = "Recipe"

        If saveDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then

            ExpFileName = saveDialog.FileName
        End If

        Dim ConString As String = ""

    ConString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=YES"";Data Source={0}", ExpFileName)
        Dim conObj As New System.Data.OleDb.OleDbConnection()
        conObj.ConnectionString = ConString
        conObj.Open()


        Dim createTable As String = "CREATE TABLE sheet1(VJ1 char(255),VJ2 char(255))"

        Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(createTable, conObj)

        cmd.ExecuteNonQuery()


Thanks in Advance,

Vijay
Posted
Updated 4-Apr-23 3:05am
v2

In your code ' Extended Properties=""Excel 12.0;HDR=YES ' of your connection string .a 'Xml' should be added to make ' Extended Properties=""Excel 12.0 Xml;HDR=YES '.
.So you have to modify your connection string as :

VB
ConString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0 Xml;HDR=Yes"";Data Source=" & ExpFileName)

It seems to be used to specify or validate data in excel sheet .i.e. i am not sure why this is so important but
This connection string works as of your scenario.
 
Share this answer
 
v2
Comments
Vijay hit 11-Jun-13 7:37am    
Hi Rajan,

Thanks a lot. Just by changing connection string it worked well.
As you mentioned we need to add Xml to Extended property.
'Xml' should be added to make ' Extended Properties=""Excel 12.0 Xml;HDR=YES '.

Offf... I was sick of searching for the answer from 2 days. Really it helped a lot.

Thanking you once again.
Surendra Adhikari SA 11-Jun-13 7:47am    
welcome, i have already tested it in my mechine .and thanks for your +5 .
Your code generates the "old" Excel format. When you just change the extension of the old format to ".xlsx", Excel throws that exception. When you save a file in the newer format nad change the extension to ".xls", Excel will warn you that the file could be corrupted, but you can still open it. So: just make sure the extension matches the format.
 
Share this answer
 
Comments
Vijay hit 11-Jun-13 5:37am    
Hi Bernhard,

Thanks for the valuable answer.
Do you mean the way I am saving file using OLEDB is old method. In that case could you please let me know
how I can save .XLSX in new format(May be piece of code).

Or you mean I might be saving in .xls format,In that case my answer is I am saving file in new format i.e .XLSX (You can now see in my question which I have modified and also mentioned the path).

I am really exhausted to find the solution for this.

Appreciate comment.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900