kurb wrote ...

I ran into two problems with modifications I made

1. The FULL_NAME I used came back with an error that it was too long,
so I am assuming that the maximum lenght for this must be set somewhere.

2. I tried to use a Sheetname instead of "Sheet1$" for TABLE-NAME, but
it wouldn't work

The best approach is to find out what Jet thinks the sheet/table name
is and try using that name e.g. this code fetches all the tables'
(worksheets and valid book-level and sheet-level defined Names) names
for a workbook:

Sub Test2
Const FULL_FILENAME As String = "" & _
"C:\Tempo\db.xls"
Dim vntArray As Variant
vntArray = ExcelTableNames(FULL_FILENAME)
MsgBox Join(vntArray, vbCrLf)
End Sub

Public Function ExcelTableNames( _
ByVal FullFilename As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim lngRows As Long
Dim lngCounter As Long
Dim strTablesNames() As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(20)

End With


With rs

.ActiveConnection = Nothing
Con.Close

lngRows = .RecordCount
ReDim strTablesNames(lngRows - 1)
For lngCounter = 0 To lngRows - 1
strTablesNames(lngCounter) = !TABLE_NAME
.MoveNext
Next

End With

ExcelTableNames = strTablesNames

End Function


Jamie

--