On Fri, 12 May 2006 09:14:44 +0200, "Peter Stojkovic" <ds*@gmx.net> wrote:
¤ I am importing data from an EXCEL XLS-File via OLEDB-Provider
¤
¤ The command is SELECT * from [sheet1]
¤
¤
¤ Everything works fine if the first sheet is named sheet1
¤
¤
¤
¤ But sometimes the name is not known by my application and the import failes.
¤
¤ Is it possible to import always the FIRST available sheet ??
There are only two ways, of which I am aware, that will enable you to retrieve an Excel Worksheet by
its ordinal position in the Workbook. First method is to use DAO:
Dim xlWB As DAO.Database
Dim strFirstSheetName As String
xlWB = OpenDatabase("C:\Test Files\Book10.xls", False, True, "Excel 8.0;")
strFirstSheetName = xlWB.TableDefs(0).Name
xlWB.Close
....and the other is to use automation with Microsoft Excel:
Dim obj As Excel.Application
Dim objWB As Excel.Workbook
Dim strFirstSheetName As String
obj = CreateObject("Excel.Application")
objWB = obj.Workbooks.Open("C:\Test Files\Book10.xls")
strFirstSheetName = objWB.Worksheets(1).Name
objWB.Close
objWB = Nothing
obj.Quit
obj = Nothing
Now if the Workbook only contains a single Worksheet then there is a native .NET method that does
not require COM automation or the use of DAO.
Paul
~~~~
Microsoft MVP (Visual Basic)