Verify Table Exists Using Fuzzy Search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When importing an excel file I'd like to check if the ImportErrors table was
created. I've gotten it to work hardcoding the name "Sheet1$_ImportErrors",
but it would be better to just check if a table exists with "ImportErrors" in
the name (quite probable user will send files with different named tabs).
Following a previous posting here from John Nurick I've got the following
working -

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty,
"Sheet1$_ImportErrors"))

If Not rs.EOF Then CheckTable = True

I tried -
'Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty,
Right("ImportErrors", 12)))

but that didn't work. I'm thinking this might involve AllTables collection
and the Right function, but have no experience using collections (now seems
like a good time!). Is this possible? Can someone provide a code example?
Thank you for your help.

(By the way, if this posted twice I apologize for the duplication. The
first time I created this post I got a Server Busy timeout. I recreated the
question not knowing if it went through before or not.)
 
Function FindImportErrors() As String
Dim lngTblCount As Long
Dim lngTblNdx As Long
Dim tdfs As TableDefs

Set tdfs = CurrentDb.TableDefs
lngTblCount = tdfs.Count - 1
For lngTblNdx = 0 To lngTblCount
If InStr(tdfs(lngTblNdx).Name, "$_ImportErrors") > 0 Then
FindImportErrors = tdfs(lngTblNdx).Name
Exit For
End If
Next lngTblNdx
End Function
 
Of course! InStr! And - mmm, TableDefs - interesting.
Thanks, Klatuu. This was the last piece (I hope) of the puzzle. Come
Monday, when I present the app, it's gonna rock!
 
Back
Top