Quite a while ago I queried how i might search through
every excel file in a certain directory. The answer
whick came through was some code which opened each
workbook in turn and searched for the particular string i
needed.

Since then I have found that, as there are more and more
files each week, it is taking far too long to open each
one and search them individually. Does anyone know of a
way I could speed this process up, possably by not
needing to open each file.

Thanks

Jonny

The code below reads the names of all Sheets in a Workbook
without opening them. To use this code, you must first set
a reference to "Microsoft
ActiveX Data Objects 2.1 Library" and "Microsoft ADO Ext.
2.1 for DDL and
Security".

Sub ReadSheetNames(TheCompleteFilePath As String)
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=" & TheCompleteFilePath
cat.ActiveConnection = cnn
For Each tbl In cat.Tables
MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

-----Original Message-----
Quite a while ago I queried how i might search through
every excel file in a certain directory. The answer
whick came through was some code which opened each
workbook in turn and searched for the particular string i
needed.

Since then I have found that, as there are more and more
files each week, it is taking far too long to open each
one and search them individually. Does anyone know of a
way I could speed this process up, possably by not
needing to open each file.

Thanks

Jonny
.

See if this is helpful. Forgot where I got it

Sub findit()
For Each wkbk In Workbooks
x = ActiveCell.Value
For Each ws In Worksheets

With ws.Cells
'Set c = .Find("activecell", LookIn:=xlValues, After:=ActiveCell,
SearchDirection:=xlNext)
Set c = .Find(x)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Address < "$A$1" Then
'MsgBox ws.Name & "!" & c.Address
ddd = ws.Name & "!" & c.Address
Exit Sub

End If
'Exit Sub
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
Next ws

Next wkbk
End Sub

--
Don Guillett
SalesAid Software

"jonny" wrote in message
...
Quite a while ago I queried how i might search through
every excel file in a certain directory. The answer
whick came through was some code which opened each
workbook in turn and searched for the particular string i
needed.

Since then I have found that, as there are more and more
files each week, it is taking far too long to open each
one and search them individually. Does anyone know of a
way I could speed this process up, possably by not
needing to open each file.

Thanks

Jonny

Rather than ADOX, you'd find ADO more useful to actually *retrieve* the data.

--

"Serkan" wrote in message ...
The code below reads the names of all Sheets in a Workbook
without opening them. To use this code, you must first set
a reference to "Microsoft
ActiveX Data Objects 2.1 Library" and "Microsoft ADO Ext.
2.1 for DDL and
Security".

Sub ReadSheetNames(TheCompleteFilePath As String)
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=" & TheCompleteFilePath
cat.ActiveConnection = cnn
For Each tbl In cat.Tables
MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub

-----Original Message-----
Quite a while ago I queried how i might search through
every excel file in a certain directory. The answer
whick came through was some code which opened each
workbook in turn and searched for the particular string i
needed.

Since then I have found that, as there are more and more
files each week, it is taking far too long to open each
one and search them individually. Does anyone know of a
way I could speed this process up, possably by not
needing to open each file.

Thanks

Jonny
.