Error when I import data from excel

Question

0.00/5 (No votes)

See more:

Hi all experts,

I want to import all data from my excel to datagridview in vb.net.
if i don't open excel file, it will error " External Table is not in the expected format." but if i open it, it doesn't display error.

VB

Dim MyConnection As OleDb.OleDbConnection,DA As OleDb.OleDbDataAdapter MyConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.TextBox1.Text & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'") 

DA = New System.Data.OleDb.OleDbDataAdapter("select * from [Detail$]", MyConnection) 
DA.TableMappings.Add("Table", "Tbl1") 
DtSet = New System.Data.DataSet 
DA.Fill(DtSet) 
Me.DataGridView1.DataSource = DtSet.Tables(0)

Does anybody know how can i import data from excel in both cases( Open Excel file and not open excel file)?

Note:
My MS Office is : 2007

Thanks

TONY

Posted 8-Aug-12 15:20pm

soeun tony

Updated 9-Aug-12 3:48am

Joan M

v5

Add a Solution

Comments

Kenneth Haugland 8-Aug-12 21:42pm

And the code looks like?

[no name] 8-Aug-12 21:54pm

Check your connection string and make sure you are using the right one for the version of Excel.

soeun tony 8-Aug-12 22:00pm

Hi Kenneth,
Sorry that don't input my code. Here is my code:
Dim MyConnection As OleDb.OleDbConnection,DA As OleDb.OleDbDataAdapter
MyConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.TextBox1.Text & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
DA = New System.Data.OleDb.OleDbDataAdapter("select * from [Detail$]", MyConnection)
DA.TableMappings.Add("Table", "Tbl1")
DtSet = New System.Data.DataSet
DA.Fill(DtSet)
Me.DataGridView1.DataSource = DtSet.Tables(0)

Note:
My MS Office is : 2007

Thanks

TONY

Joan M 9-Aug-12 9:51am

Soeun tony, when you want to add information to your question it is a good idea to add it to the question itself, not only for the CPian that has asked it. doing so you get the benefit of all the rest of the community to see it, added to teh fact that you can tell the CPian that asked it that you've updated your question.
To do so you just have to click the link "Improve question" that's just under the question itself.

soeun tony 9-Aug-12 22:13pm

Hi Joan,
Thanks for your good idea.i'll follow you next time.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wes Aday · Accepted Answer · 2012-08-09T03:45:00

Solution 1

Use Microsoft.ACE.OLEDB.12.0 for Excel 2007 http://www.connectionstrings.com/excel-2007[^]

Posted 9-Aug-12 3:45am

Wes Aday