VBA and SQL

[CubsFan]

Hi all, I was wondering if someone can help me with this question. I can
successfully use an ADO method with a SQL statement to search through a
massive amount of spreadsheets in distributed folders to look for information
on the sheets with a SQL query. However, I noticed that the code example I
used to develop the script with heavily depends on knowing at least the sheet
names ahead of time.

Is there a method for getting the sheet names similar to getting a list of
table names when the sheet names are unknown?

I have to search through the list to find every instance of a workbook with
a particular string subset in the sheet names.

For example, if I were looking for 'xyz' in the sheet names, I would get the
sheet names and do a string test 'intPos = InSt(sheetname, "xyz")' for each
sheet, and if intPos is greater than zero, it found it. But, I need to get
the collection of sheet names without the hassle of creating a new instance
of excel opening each and every book to get the names.


Here is an example of my current code that searches through the workbooks to
look for every workbook that has the exact sheet name 'xyz' (is called in
another loop and sent the file name):

'************************************
Public Sub LookInXLFile(stFile As String)

On Error GoTo errTrap

stSQLConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & stFile
& ";Extended Properties=Excel 8.0;"

stSQL = "SELECT * FROM [xyz$]"

GetSQLData
'another sub that just creates the db connection and record set - the stSQL,
dbconn, rsData, stSQLConnString, and mySheetFound are global variables

If Not rsData Is Nothing Then
If Not rsData.EOF Then
mySheetFound = True
End If
End If


CloseConn
'again another sub that just closes the connection and destroys the objects
to free up the memory space

Exit Sub
errTrap:

CloseConn

End Sub
'**************************

Again, this works as long as the sheet name is there. But if I need to
search through the list of sheet name that contain 'xyz' is there a 'LIKE
'xyz%' ' statement that can be used in the 'FROM' part of the SQL statement?

I guess what I need is the worksheets collection from the workbook object as
a set of table names?

Thanks for any help!
CubsFan

 

[Rob Bovey]

Here's a link with an example of how you can get this information:

http://www.appspro.com/Tips/WorkbookTips.htm#WT1

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dick Kusleika]

Is there a method for getting the sheet names similar to getting a list of
table names when the sheet names are unknown?

Sub GetSheetNames()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim sConn As String

Set cn = New ADODB.Connection

sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=YourSources;Extended Properties=Excel 8.0;"

cn.Open sConn

Set rs = cn.OpenSchema(adSchemaTables)

Do While Not rs.EOF
Debug.Print rs.Fields("Table_Name").Value
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

 

[CubsFan]

Thanks for the great help!!
CF

Sub GetSheetNames()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim sConn As String

Set cn = New ADODB.Connection

sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=YourSources;Extended Properties=Excel 8.0;"

cn.Open sConn

Set rs = cn.OpenSchema(adSchemaTables)

Do While Not rs.EOF
Debug.Print rs.Fields("Table_Name").Value
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub