Select from first worksheet without knowing it's name

[Roger Twomey]

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.

 

[Don Guillett]

try

Sheets(1).Activate

 

[Roger Twomey]

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.

--**

try

Sheets(1).Activate

--
Don Guillett
SalesAid Software
(e-mail address removed)

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.

 

[Toby Erkson]

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

 

[onedaywhen]

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

strExcelSQL = "SELECT * FROM [Sheet1$]"

try

Sheets(1).Activate

Eek!

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

--

 

[onedaywhen]

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]

'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.

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

strExcelSQL = "SELECT * FROM [Sheet1$]"

"Don Guillett" <[email protected]> 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.aspx?scid=kb;en-us;Q319998

--