By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,320 Members | 2,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,320 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 ??



May 12 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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)
May 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.