# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  [SOLVED] 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@virgin.net

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.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-------------

----------


## 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@virgin.net" 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.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@virgin.net

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
>
>
> "aidan.heritage@virgin.net" 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.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-------------
> >
> >

----------


## replyaslam

Thanks aidan,

I have below code which works fine now.

Dim xlApp As New Excel.Application
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


"aidan.heritage@virgin.net" 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
> >
> >
> > "aidan.heritage@virgin.net" 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.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-------------
> > >
> > >
>
>

----------

