ADO Importing from an unknown named Excel worksheet

[packwood]

I have the following code that imports from an Excel workbook but I
need to know the worksheet name. If I always want to import from just
the first worksheet in the collection how can I do this?

Ideally I would like the code to refer to something like
"Worksheets(1)" rather than the specific worksheet "Sample data" in my
example.

Or is there a neater way of doing this?
Sub ImportExcel()

Dim cPath As String

cPath = Application.CurrentProject.Path

Dim cSource As String
cSource = cPath & _
IIf(Right$(cPath, 1) <> "\", "\", "") & _
"sample import.xls"

Dim cTarget As String
cTarget = cPath & _
IIf(Right$(cPath, 1) <> "\", "\", "") & _
"imported excel.mdb"

Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
Dim cSQL As String

With oCon
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& _
"Data Source=" & cSource & ";" & _
"Extended Properties=Excel 8.0"
.Open

' Creates Table and adds records from Excel
cSQL = "SELECT * INTO [tblImport1] " & _
"IN '" & cTarget & "' " & _
"FROM [Sample data$]"

.Execute cSQL
End With

End Sub

[Ozzone]

First of all, use Application.Filesearch and then you can simply assign
a string variable to the filename.

Then run your code using the variable as your source file.

Look up filesearch in vba help.

[packwood]

I might have missed something here. Its the worksheets and the first
one in particular I am after not the workbook.