Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching across multiple workbooks in Excel 2003 | Excel Programming | |||
Searching Multiple Excel Workbooks | Excel Discussion (Misc queries) | |||
Searching Multiple Workbooks | Excel Discussion (Misc queries) | |||
Searching Across 2 workbooks | New Users to Excel | |||
Searching multiple workbooks | Excel Discussion (Misc queries) |