Querying sheet of unknown name

C

ChaunceyMo

Hi all,

I am importing excels into code via an adodb connection, but have ru
into the problem of many of the excels not having standard sheet name
(ie- "import" instead of "Sheet1"). Is there a way to do a SELEC
query without knowing the name of the sheet? This is furthe
complicated by the workbooks potentially having two sheets rather tha
just one, again with an unknown name.

A method that iterates through the sheets sequentially would b
terrific.

Thanks in advance,
M
 
B

Bob Phillips

Mo,
assuming that the sheet is always the first sheet, you could get the name
from the file before you run the query. Here is a sample function to do that

Function SheetsADOX(FileName As String) As Variant
Dim oConn As Object
Dim oCat As Object
Dim oTable As Object
Dim sConnString As String
Dim sFileName As String
Dim sTableName As String
Dim aSheets As Variant
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"

Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
SheetsADOX = ""
Exit Function
Else
On Error GoTo 0
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

ReDim aSheets(oCat.Tables.Count - 1)
sTableName = oCat.Tables(1).Name
cLength = Len(sTableName)
iTestPos = 0: iStartpos = 1
'Worksheet name with embedded spaces are enclosed by single quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1: iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
sTableName = Mid$(sTableName, iStartpos, cLength - (iStartpos +
iTestPos))
End If

End If

oConn.Close
Set oCat = Nothing
Set oConn = Nothing

SheetsADOX = sTableName
End Function




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
O

onedaywhen

Using the Connection object's OpenSchema method:

Sub test()
Dim Con As Object
Dim rs As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties=Excel 8.0"
.Open
Set rs = .OpenSchema(20) ' adSchemaTables
End With
With rs
Do While Not .EOF
Debug.Print !TABLE_NAME
.MoveNext
Loop
.Close
End With
Con.Close
End Sub
 
Top