External table is not in the expected format

[replyaslam]

I have a default.htm file which has some rows of data along with some images.
My intention is to read this rows of data.

If I somply rename default.htm to default.xls and excute the below code , it
gives error "External table is not in the expected format."

If I open a default.htm and manually save a default.xls the below lines of
code executes sucessfully.

How do I deal with this problem.

CODE START-------------
Dim mExcelFile As String
mExcelFile = "C:\Projects\mamour\AggValueHistory\default.xls"
Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & mExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim sourceSQL As String = "SELECT * FROM Sheet1$"

'now do the work
Dim sourceCon As New OleDbConnection(sourceConStr)


Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
Try
sourceCon.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Dim sourceReader As OleDbDataReader
Dim a As String
Dim b As Integer
Dim c As String
Dim d As String
Try
sourceReader = sourceCommand.ExecuteReader()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

While sourceReader.Read() 'for each row from source
Try
For i As Integer = 0 To sourceReader.FieldCount - 1
'load values into parameters

a = sourceReader.Item(0)
b = sourceReader.Item(1)
c = sourceReader.Item(2)
d = sourceReader.Item(3)
console.write(a & b & c & d)
Next
Catch ex As Exception 'OleDbException
Dim strmsg As String
messagebox.showex.message
End Try
End While
sourceReader.Close()
sourceCon.Close()
End Sub

CODE END-------------

 

[aidan.heritage]

Renaming a file doesn't change it's file type - loading it and saving
it DOES. I would SUGGEST that as the code works if the file is
resaved, you amend it to open the HTML file, then SaveAS file type
excel, then carry on as before.

 

[replyaslam]

Thanks aidan for the quick answer,

Do you/anyone have sample code to programitically open the HTML file, then
SaveAS file type excel.

-aslam

 

[aidan.heritage]

You can actually record this, and get PRETTY much what you need - but
it is

Workbooks.Open Filename:= "whatever.htm"
ActiveWorkbook.SaveAs Filename:= "whatever.xls", FileFormat
:=xlNormal

 

[replyaslam]

Thanks aidan,

I have below code which works fine now.

Dim xlApp As New Excel.Applicatio
xlApp.Workbooks.Open(Filename:="C:\Projects\mamour\AggValueHistory\default.htm")
xlApp.ActiveWorkbook.SaveAs(Filename:="C:\default.xls", FileFormat:=-4143,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False)
If Not xlApp Is Nothing Then
xlApp.ActiveWorkbook.Close()
xlApp.Quit()
xlApp = Nothing
end if

regards
-aslam