ADO - Excel - External table is not in the expected format

[Adriana Camargo]

I copied the data into a new file, and then it works fine.
Im using some others files that work fine.
The error ocurr just when the program tries to open the connection to
the file.

If somebody can give me a hint?

This is the code that i used to open the excel file.

strExcelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;data source=" & strFileName &
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

cn.ConnectionString = strExcelConnectionString
cn.Open

Thanks!!!

 

[Bob Phillips]

Adriuana,

What is cn in this code?

This works fine for me

Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range
Dim aryData

sFile = "C:\myTest\Volker1.xls"


' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
aryData = oRS.getrows()
Else
MsgBox "No records returned.", vbCritical
End If

MsgBox "Cell A2 contains " & aryData(0, 0)
MsgBox "Cell D4 contains " & aryData(3, 2)

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onedaywhen]

Adriana Camargo wrote ...

I copied the data into a new file, and then it works fine.
Im using some others files that work fine.
The error ocurr just when the program tries to open the connection to
the file.

If somebody can give me a hint?

This is the code that i used to open the excel file.

strExcelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;data source=" & strFileName &
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

cn.ConnectionString = strExcelConnectionString
cn.Open

Thanks!!!

You code worked for me as posted, but you may want to try without the
IMEX=1 (do you need it?) in the connection string and check whether
HDR=No (i.e. no column/field headers) is correct:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strExcelConnectionString As String
Dim strFileName As String
strFileName = "C:\Tempo\db.xls"
strExcelConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & strFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
cn.ConnectionString = strExcelConnectionString
cn.Open ' << no error for me!

--

 

[Adriana Camargo]

Yeah, it works fine with some files, but with others, it fails. The
problem is that i have to work with many files that have same structure,
but different information. So, i have no control on the information in
the file.

I have to use HDR=NO because the files dont have a header or column
name, so if use HDR=YES, it will skip a row.

I have to use IMEX=1, because if i dont use it, the code doesnt read
some values in the cells, and them will be set as NULL. I found in some
microsoft article, that this happens when in the same column i have
different types of data (numbers, and characters)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;194124

So i havent found a solution.
Any suggestions?

Thanks for your help!!!