External table is not in the expected format
 

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.xl s"
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-------------

External table is not in the expected format
 

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 wrote:
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.xl s"
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-------------

External table is not in the expected format
 

Thanks aidan for the quick answer,

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

-aslam


" wrote:

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 wrote:
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.xl s"
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-------------

External table is not in the expected format
 

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 wrote:
Thanks aidan for the quick answer,

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

-aslam


" wrote:

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 wrote:
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.xl s"
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-------------

External table is not in the expected format
 

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


" wrote:

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 wrote:
Thanks aidan for the quick answer,

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

-aslam


" wrote:

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 wrote:
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.xl s"
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-------------