How to import always the FIRST available sheet from EXCEL-File

  • Thread starter Thread starter Peter Stojkovic
  • Start date Start date
P

Peter Stojkovic

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 ??
 
¤ 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)
 
Back
Top