Searching a group of Excel workbooks.....

[Robert Crandal]

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!

 

[Bruno Campanini]

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

 

[Robert Crandal]

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!

 

[dksaluki]

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

 

[GS]

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/DatabaseProgramming.zip

HTH

 

[Robert Crandal]

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!

 

[Don Guillett Excel MVP]

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!








- 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

 

[GS]

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?

 

[Robert Crandal]

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]

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!