Importing from Excel 1

[arrian]

I'm having a problem importing from an excel spreadsheet. The code I am using is as follows:

Function ImportFromExcel(ByVal strPath As String)

Dim tmpDS As New DataSet
Dim tmpAdapter As OleDbDataAdapter
Dim tmpCon As OleDbConnection

'Creates the OleDB connection
tmpCon = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & strPath & "; " & _
"Extended Properties=Excel 10.0;")

'Creates and fills the data adapeter, importing all the excel information
tmpAdapter = New OleDbDataAdapter("select * from [sheet2]", tmpCon)
On Error GoTo ErrHandle
Call tmpAdapter.Fill(tmpDS)

tmpCon.Close()

Return tmpAdapter

errhandle:
MsgBox(Err.Number & ": " & Err.Description)
End Function

The problem arises here:
Call tmpadapter.Fill(tmpDS)

It gives me error #5, Could not find installable ISAM

Any ideas on what this means???

 

[chrissie1]

why do use the word call???

and on error should be replaced with try catch

something like this

try
  tmpAdapter.Fill(tmpDS)
catch ex as sqlexception
  messagebox.show(ex.message)
end try
  tmpCon.Close()

but it could be that this is not the problem. Is excell installed??



Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

I used the word call 'cause the code I found to do this had call. I've since removed it. I've replace the On Error with a try/catch, and for some reason when it errors, it doesn't catch the exception.

Yes, Excel is installed. It's version 2003.

 

[arrian]

Changed the exception from sqlexception to just exception and it catches it now...

 

[chrissie1]

same error I presume.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

Yes, still getting the Could not find installable ISAM error...

 

[chrissie1]

as I thought the problem is in the connectionstring.

http://blogs.wdevs.com/gaurang/archive/2005/06/15/5112.aspx

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

I've actually read that exact post you've linked to, and unfortunatly, it's not working. The code is as follows:

    Function ImportFromExcel(ByVal strPath As String)

        Dim tmpDS As New DataSet
        Dim tmpAdapter As OleDbDataAdapter
        Dim tmpCon As OleDbConnection

        'Creates the OleDB connection
        tmpCon = New System.Data.OleDb.OleDbConnection( _
          "provider=Microsoft.Jet.OLEDB.4.0; " & _
          "data source=" & strPath & "; " & _
          "Extended Properties=""Excel 8.0"";HDR=YES")

        'Creates and fills the data adapeter, importing all the excel information
        tmpAdapter = New OleDbDataAdapter("select * from [sheet2$]", tmpCon)
        Try
            tmpAdapter.Fill(tmpDS)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        tmpCon.Close()

        Return tmpAdapter

    End Function

It's still returning the same error.

 

[dolfo35]

I'm curious. Isn't the object library for Excel 2003 '11.0'? I noticed that Extended Properties = 8.0. That may have nothing to do with it, but it's usually the things that are overlooked that cause me problems.

Rudy

 

[chrissie1]

I think it will something more like this

"provider='Microsoft.Jet.OLEDB.4.0';data source='" & strPath & "'; Extended Properties='Excel 11.0';HDR=YES")

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

Single quotes around Excel 11.0??? I've tried with 11.0, 10.0, 9.0, 8.0... Just to be sure, and nothing's worked so far.

 

[arrian]

Is there maybe a better way to pull the data from an excel file to a DataAdapter? I need to put that info into a grid, then move it into an Access database...

 

[chrissie1]

according to

http://www.connectionstrings.com

it should be more like this

change the 8.0 by 11.0 and see how the hdr and imex are in the double quotes.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" 
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

Hurray! It works now! Thank you SO much!!! Now I just have to figure out how to get the DataAdapter to display in the DataGrid...

 

[chrissie1]

pfff that's simple.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[arrian]

Yeah, stupid me, I should have been returning the Dataset, not the data adapter. It's all working now. Thanks a lot!

 

[infinitelo]

Thanks for the cool link!


if it is to be it's up to me

 

[ICTECH]

Hay Arrian...

Can yopu please post you final Code snippet here? I'm still getting an error and can't see it right now....

Thanks...

 

[arrian]

Sure thing! Here's the final code I've got for importing from Excel:

Function ImportFromExcel(ByVal strPath As String)

        Dim tmpDS As New DataSet
        Dim tmpAdapter As OleDbDataAdapter
        Dim tmpCon As OleDbConnection

        'Creates the OleDB connection
        tmpCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

        'Creates and fills the data adapeter, importing all the excel information
        tmpAdapter = New OleDbDataAdapter("select * from [sheet2$]", tmpCon)
        Try
            tmpAdapter.Fill(tmpDS)
        Catch ex As Exception
            ErrHandle(Err, "Import from Excel")
        End Try
        tmpCon.Close()

        Return tmpDS

    End Function