ExcelBanter - Select from first worksheet without knowing it's name

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Select from first worksheet without knowing it's name (https://www.excelbanter.com/excel-programming/297090-select-first-worksheet-without-knowing-its-name.html)

Select from first worksheet without knowing it's name

I have an app that reads excel spreadsheets. It works fine, as long as the
first sheet is called "Sheet1". If there is no sheet called Sheet1 it
crashes.

I don't want to force the user to change the spreadsheet, I want to read the
first page no matter what it is called.

Here is how my code works right now:

<code

Dim myds As New DataSet
Dim MyExcelConnectStr As String = "Provider=Microsoft.Jet.OLEDb.4.0;data
source=" & strFileName & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

strExcelSQL = "SELECT * FROM [Sheet1$]"

Dim MyOleADapter As New OleDb.OleDbDataAdapter(strExcelSQL,
MyExcelConnectStr)

MyOleADapter.Fill(myds, "xlsdata")

intRows = myds.Tables(0).Rows.Count

intFields = myds.Tables(0).Columns.Count

</code

Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

which will provide the same function (open sheet1) without knowing it's
name? (which may not be Sheet1). Or, a way to GET the first sheets name
(vb.net)?

Thanks.

Select from first worksheet without knowing it's name

No Luck.

Here are the various things I have tried:

I tried this:
strExcelSQL = "Select * from [Sheets(1)]"
Which Resulted in this:
The Microsoft Jet database engine could not find the object 'Sheets[1]'. Make sure the object exists and that you spell its name and the path name correctly.

--**

strExcelSQL = "Select * from [Sheets(1).Active]"
Which Resulted in this:
The Microsoft Jet database engine could not find the object 'Sheets[1].Active'. Make sure the object exists and that you spell its name and the path name correctly.

--**
strExcelSQL = "Select * from [Sheets(1).Activate]"

Which Resulted in this:
The Microsoft Jet database engine could not find the object 'Sheets[1].Activate'. Make sure the object exists and that you spell its name and the path name correctly.

--**
strExcelSQL = "Select * from Sheets(1).Activate"
Which Results in this:
Syntax error in FROM clause.

--**

strExcelSQL = "Select * from Sheets(1).Active"

Which results in this:
Syntax error in FROM clause.

--**

"Don Guillett" wrote in message ...
try

Sheets(1).Activate

--
Don Guillett
SalesAid Software

"Roger Twomey" wrote in message
...
I have an app that reads excel spreadsheets. It works fine, as long as the
first sheet is called "Sheet1". If there is no sheet called Sheet1 it
crashes.

I don't want to force the user to change the spreadsheet, I want to read
the
first page no matter what it is called.

Here is how my code works right now:

<code

Dim myds As New DataSet
Dim MyExcelConnectStr As String =
"Provider=Microsoft.Jet.OLEDb.4.0;data
source=" & strFileName & ";Extended Properties=""Excel
8.0;HDR=No;IMEX=1"""

strExcelSQL = "SELECT * FROM [Sheet1$]"

Dim MyOleADapter As New OleDb.OleDbDataAdapter(strExcelSQL,
MyExcelConnectStr)

MyOleADapter.Fill(myds, "xlsdata")

intRows = myds.Tables(0).Rows.Count

intFields = myds.Tables(0).Columns.Count

</code

Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

which will provide the same function (open sheet1) without knowing it's
name? (which may not be Sheet1). Or, a way to GET the first sheets name
(vb.net)?

Thanks.

Select from first worksheet without knowing it's name

I don't know how this will help but (this part was done in Excel):
Sub main()
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
MsgBox sht.Name
Next
End Sub

This simply displays the message box with the name of the worksheet. It starts from the beginning and moves single file thru each tab. If you rename a tab or
move a tab that will not matter, you will still get them in the order you see them (left to right).

Based on the above code maybe you could just read the first sht.Name and pass that variable into your code? Kind of like:

Sub main()
Dim sht As Worksheet
Dim x as Integer
Dim the_sheet_name as String

x = 0
For Each sht In ActiveWorkbook.Sheets
If x = 0 then
the_sheet_name = sht.Name
x = 1
End If
Next

strExcelSQL = "SELECT * FROM " & the_sheet_name
....
End Sub

Okay, that was really quick and dirty but I hope I got the idea across. This all was a quick stab, hope it can spark any other ideas.
Toby Erkson
Oregon, USA

On Mon, 3 May 2004 13:48:00 -0400, "Roger Twomey" wrote:

I have an app that reads excel spreadsheets. It works fine, as long as the
first sheet is called "Sheet1". If there is no sheet called Sheet1 it
crashes.

I don't want to force the user to change the spreadsheet, I want to read the
first page no matter what it is called.

Here is how my code works right now:

<code

Dim myds As New DataSet
Dim MyExcelConnectStr As String = "Provider=Microsoft.Jet.OLEDb.4.0;data
source=" & strFileName & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

strExcelSQL = "SELECT * FROM [Sheet1$]"

Dim MyOleADapter As New OleDb.OleDbDataAdapter(strExcelSQL,
MyExcelConnectStr)

MyOleADapter.Fill(myds, "xlsdata")

intRows = myds.Tables(0).Rows.Count

intFields = myds.Tables(0).Columns.Count

</code

Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

which will provide the same function (open sheet1) without knowing it's
name? (which may not be Sheet1). Or, a way to GET the first sheets name
(vb.net)?

Thanks.

Select from first worksheet without knowing it's name

Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

"Don Guillett" wrote in message ...
try

Sheets(1).Activate

Eek!

BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO
http://support.microsoft.com/default...;en-us;Q319998

--

Select from first worksheet without knowing it's name

Take a look in the help for
OleDbConnection.GetOleDbSchemaTable Method

It has an example of how to get the table names for a OleDbConnection object.

--

"Roger Twomey" wrote in message ...
I have an app that reads excel spreadsheets. It works fine, as long as the
first sheet is called "Sheet1". If there is no sheet called Sheet1 it
crashes.

I don't want to force the user to change the spreadsheet, I want to read the
first page no matter what it is called.

Here is how my code works right now:

<code

Dim myds As New DataSet
Dim MyExcelConnectStr As String = "Provider=Microsoft.Jet.OLEDb.4.0;data
source=" & strFileName & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

strExcelSQL = "SELECT * FROM [Sheet1$]"

Dim MyOleADapter As New OleDb.OleDbDataAdapter(strExcelSQL,
MyExcelConnectStr)

MyOleADapter.Fill(myds, "xlsdata")

intRows = myds.Tables(0).Rows.Count

intFields = myds.Tables(0).Columns.Count

</code

Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

which will provide the same function (open sheet1) without knowing it's
name? (which may not be Sheet1). Or, a way to GET the first sheets name
(vb.net)?

Thanks.

Select from first worksheet without knowing it's name

:) 'Eek!' is not a problem. Because the spreadsheet is loaded to a remote
server first, they cannot open it in excel. The circumstances required for
the memory leak cannot occur.

Thanks for the heads up though. I would not likely have known of the issue
otherwise.

"onedaywhen" wrote in message
om...
Is there some function or code that can be used in place of:

strExcelSQL = "SELECT * FROM [Sheet1$]"

"Don Guillett" wrote in message
...
try

Sheets(1).Activate

Eek!

BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO
http://support.microsoft.com/default...;en-us;Q319998

--