Searching a group of Excel workbooks.....
 

I have about 100 or so workbooks that are contained in the
same folder. Rather than manually opening each file to search
for a particular piece of data, what is a quicker and more
efficient way to do this???

Would I need a VBA script that opens and closes each file,
one after the other and then do my search in each workbook??

Thank you!

Searching a group of Excel workbooks.....
 

"Robert Crandal" wrote in message
...
I have about 100 or so workbooks that are contained in the
same folder. Rather than manually opening each file to search
for a particular piece of data, what is a quicker and more
efficient way to do this???

Would I need a VBA script that opens and closes each file,
one after the other and then do my search in each workbook??

Same search range for every workbook?
And when a match is found, what is the action?
Copy the range found elsewhere, delele/edit the range found,
or what else?

Bruno

Searching a group of Excel workbooks.....
 

Hi Bruno!

Yes, the search procedure will be the same for each workbook because
all workbooks have the same format.

If a match is found, I already know how I want to process it.
I'm just wondering what my search code should look like.
Is it basically, open next file, search and process data, close file,
etc, etc, repeatedly?? Does that sound right? Does such a search
normally take long?

Thank you!

"Bruno Campanini" wrote in message
...
"Robert Crandal" wrote in message
...
I have about 100 or so workbooks that are contained in the
same folder. Rather than manually opening each file to search
for a particular piece of data, what is a quicker and more
efficient way to do this???

Would I need a VBA script that opens and closes each file,
one after the other and then do my search in each workbook??

Same search range for every workbook?
And when a match is found, what is the action?
Copy the range found elsewhere, delele/edit the range found,
or what else?

Bruno

Searching a group of Excel workbooks.....
 

you could use the GetOpenFileName method with AllowMultiSelect =
true. This gives you an array of file names. Then loop through this
array and do whatever you need to do. Carefull with the
ScreenUpdating = false, but this speeds it up a bit.


Sub LoopThruFiles()
Dim folderPath() As Variant
Dim i As Integer
Dim wb As Workbook

Application.ScreenUpdating = False

'Get an array of filenames with path
folderPath = Application.GetOpenFilename("Excel Files (*.xls),
*.xls", , , , True)
For i = 1 To UBound(folderPath)
Debug.Print folderPath(i)
Set wb = Workbooks.Open(folderPath(i))
'set reference to particular cell
'do something
wb.Close SaveChanges:=True
Set wb = Nothing
Next i
Application.ScreenUpdating = True

End Sub

Searching a group of Excel workbooks.....
 

Robert Crandal has brought this to us :
I have about 100 or so workbooks that are contained in the
same folder. Rather than manually opening each file to search
for a particular piece of data, what is a quicker and more
efficient way to do this???

Would I need a VBA script that opens and closes each file,
one after the other and then do my search in each workbook??

Thank you!

You can use ADO to search data stored in workbooks without opening them
for each search. Here's some example code by Rob Bovey that
demonstrates how this is done.

http://www.appspro.com/conference/Da...rogramming.zip

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Searching a group of Excel workbooks..... (ADO)
 

That file contains soooo many functions and it is filled with lots of
arcane code that I don't know where to begin, haha! 8(

What is the simple way to open a group of files with this ADO?
How do I make a query into an open file? And finally, how do
I close the file(s) when done with my inquiry?

Thank you!


"GS" wrote in message
...
Robert Crandal has brought this to us :

You can use ADO to search data stored in workbooks without opening them
for each search. Here's some example code by Rob Bovey that demonstrates
how this is done.

http://www.appspro.com/conference/Da...rogramming.zip

Searching a group of Excel workbooks..... (ADO)
 

On Sep 24, 3:33*am, "Robert Crandal" wrote:
That file contains soooo many functions and it is filled with lots of
arcane code that I don't know where to begin, haha! * 8(

What is the simple way to open a group of files with this ADO?
How do I make a query into an open file? *And finally, how do
I close the file(s) when done with my inquiry?

Thank you!

"GS" wrote in message

...



Robert Crandal has brought this to us :

You can use ADO to search data stored in workbooks without opening them
for each search. Here's some example code by Rob Bovey that demonstrates
how this is done.

*http://www.appspro.com/conference/Da...ogramming.zip- Hide quoted text -

- Show quoted text -

Here is an idea you may like to try. make an array or list and loop
thru to change a formula until found....

Sub vlookupinworkbooks()
what = "text to look for"
For Each wb In Array("filename1", "filename2", "filename3")
Range("o3").Formula = "=VLOOKUP(""" & what & """," & _
"'C:\yourfoldername\[" & wb & ".xls]yoursheetname'!$B$7:$Z$1000,2,0)"
If Not Application.IsNA(Range("o3")) Then Exit Sub
MsgBox wb
Next wb
End Sub

Searching a group of Excel workbooks..... (ADO)
 

Robert Crandal expressed precisely :
That file contains soooo many functions and it is filled with lots of
arcane code that I don't know where to begin, haha!

Begin with the sample that reads/writes data in a closed workbook!
Isn't that what you want to do?

What is the simple way to open a group of files with this ADO?
How do I make a query into an open file? And finally, how do
I close the file(s) when done with my inquiry?

The point I was trying to make is that using the methods demonstrated
in Rob's examples doesn't require opening anything. I don't understand
why you'd want to open all those files when it's not necessary AND it
will slow down your procedure to a crawl and then possibly an out of
memory crash.

Your OP suggests that you want to process each one individually. In
this case just loop the folder and grab the data into a recordset,
process it, and move along to the next file. -Or am I missing
something?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Searching a group of Excel workbooks..... (ADO)
 

Hello! The problem is that I'm a total newbie with ADO. That ZIP file
also contained multiple demo files and I wasn't sure exactly where to begin.

Anyhow, I think I'm on the right track, but my main problem seems to be
getting the right "connection string"??

I'll go ahead and post all my code:
-------------------------------------------------------------------------------------------------------------------------
Sub MyConnection()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

' Create the connection string.
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" &
ThisWorkbook.Path & "\data.xlsx"

MsgBox szConnect

' Create the SQL Statement.
szSQL = "Select * from [Sheet1$A1:A5]"""

' Create the Recorset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

rsData.Close
Set rsData = Nothing

End Sub
--------------------------------------------------------------------------------------------------------------------------

The error message that I'm getting with the above code is something like:
"Unrecognized database format"??? For the connection string, I have also
tried using
"Provider=Microsoft.Jet.OLEDB.4.0", but I got the same error message.

What is the correct connection string for opening up an Excel 2007 .xlsx
file???

Thank you!



"GS" wrote in message
...
Robert Crandal expressed precisely :
That file contains soooo many functions and it is filled with lots of
arcane code that I don't know where to begin, haha!

Begin with the sample that reads/writes data in a closed workbook! Isn't
that what you want to do?

Searching a group of Excel workbooks..... (ADO)
 

What is the correct connection string for opening up an Excel 2007 .xlsx
file???

Thank you!

You don't open any files.

The zip file also contains a Word.doc that explains in detail how to
use ADO with an Access database file, Excel workbook, and plain text
files. I recommend you take the time to read it through so as to
educate yourself. Turn on the Document Map so you can shortcut to the
part about workbooks if you're not interested in learning the meat &
potatoes. I highly suggest, though, that you read the entire doc.
Believe me.., the juice is worth the squeeze!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc