Jeremy Oldham
9/25/2002 9:18:00 PM
In Select * from tablename, the tablename is not a Worksheet, it is a named
range within a worksheet. So you may have Sheet1 as the worksheet, but have
the following names ranges:
"Results" which spans columns a1:aa100
"Results2" which spans columns ab1: az100
Results3" which spans columns a:200:az200
In this case you would have three tables within on worksheet that you could
select from.
Jeremy
"Murali" <cutekutty@hotmail.com> wrote in message
news:#0fTQPnXCHA.4200@tkmsftngp08...
> Hi
>
> First i exported a table to excel file and convert back.It works fine.
> But when i directly import from another excel file which has no header(no
> column name),it says error in "select * from sheetname" as
>
> "The Microsoft Jet database engine could not find the object 'Sheet1'.
Make
> sure the object exists and that you spell its name and the path name
> correctly."
>
> even it fetches a sheet name sheet1 from the following code.
>
> Dim Cn As New ADODB.Connection()
> Dim rs As New ADODB.Recordset()
> Dim Cat As New ADOX.Catalog()
> Dim xlSheet As ADOX.Table
> conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName &
> ";Extended Properties=""Excel 8.0;HDR=Yes"""
>
> I tried both HDR=Yes and HDR=No
>
> Cn.Open(conString)
> Cat.ActiveConnection = Cn
> cmd.ActiveConnection = Cn
> For Each xlSheet In Cat.Tables
> xlsheetName = xlSheet.Name.Replace("$", "")
> Next
> cmd.CommandText = "select * from " & xlsheetName 'ERROR HERE
> rs = cmd.Execute()
>
> Could u help me in this?
>
> -Murali
>
> "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
> news:7s6eou8pc65ncs1m0nmau596b42chk7jsp@4ax.com...
> > On Mon, 16 Sep 2002 16:40:33 -0400, "Murali" <cutekutty@hotmail.com>
> wrote:
> >
> > ¤ HI Thanks
> > ¤ But i got it already.I want to get the name of the Sheet ?
> > ¤ eg "Select * from SheetName"
> > ¤
> >
> > If you want the names of the Worksheets in the Workbook this can be done
a
> couple of ways. You can
> > use automation, which requires Excel, or you can use data access
methods.
> The data access method
> > that I know will work requires ADO and ADOX:
> >
> > Dim cnn As New ADODB.Connection()
> > Dim cat As New ADOX.Catalog()
> > Dim xlSheet As ADOX.Table
> >
> > cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=d:\My Documents\Book2.xls;Extended
> Properties=Excel 8.0;")
> >
> > ' Open the catalog
> > cat.ActiveConnection = cnn
> >
> > For Each xlSheet In cat.Tables
> > Console.WriteLine(xlSheet.Name)
> > Next
> >
> > cat = Nothing
> > cnn.Close()
> > cnn = Nothing
> >
> >
> > Paul ~~~ pclement@ameritech.net
> > Microsoft MVP (Visual Basic)
>
>