CTRL F on more than one WB

[rcbricker]

IS there a way to look for data in more than one Wb. I sometimes need to lookup an invoice that was paid in a WB, but not sure which one. Is there a way to search the data in multiple WBs? If so do they all have to be in the same folder/drive? Do they all have to be open?

[Ken Puls]

You could use FSO (search the KB for this term), to open the files, then do a search for them. It would be easier to do this if all the files were in one directory.

Be warned that this could be VERY VERY slow. It will open the workbooks, so if you need to search 100 workbooks, you could be there a long time.

Honestly, if you need to serach historical files, you should probably look at building a database with the relevant info.

Cheers,

[Brandtrock]

Honestly, if you need to serach historical files, you should probably look at building a database with the relevant info.

Agreed.

However, an intermediate solution would be to track the information in the other wb's using an index wb so searching could be done there to find the correct wb to open. Still a time consuming endeavor and not as desirable in the end as converting to a database.

Regards,

[Charlize]

read the contents of a range of a closed workbook and insert it in the activeworkbook in the sheet that's active before calling this procedure. Must be possible to do it with an array (faster than importing).

Search value you are looking for, if not found, clear sheet and go to next workbook.

Don't know if it's faster than opening and searching.

[VBA]Option Explicit
'CWRIR is short for ClosedWorkbookRangeIntoRange
Sub CWRIR(fPath As String, fName As String, sName As String, _
rng As String, destRngUpperLeftCell As String)
'fpath = directory, fname = filename, sname = sheetname, rng = range
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim numbererror As Long
Dim destRange As Range
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
If Dir(fPath & fName) = "" Then
numbererror = CVErr(xlErrValue)
Exit Sub
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
Set destRange = ActiveSheet.Range(destRngUpperLeftCell)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
destRange.Offset(vrow - 1, vcol - 1) = ExecuteExcel4Macro(fpStr)
Next
Next
End Sub
Sub test_reading_closed_file()
'copy values of range A2:E5 from sheet1 of closed workbook in specified directory to
'range a1 on active worksheet of current active open workbook
'go to sheet were you want to put the data
Application.StatusBar = "Processing data ..."
Sheets(2).Activate
'call procedure to read values from closed file
CWRIR "C:\Documents and Settings\CVN\Mijn documenten", _
"VBAX - Copy from closed workbook - data.xls", _
"Sheet1", "A2:E5", "A1"
Application.StatusBar = ""
Sheets(1).Activate
End Sub[/VBA]
Charlize

[rcbricker]

Thanks for the input. It happens so rarely that I do not think a database would be worth building. I will try the VBA provided by Charlize and see if the works. I will let you know.

[Ken Puls]

I recently had a client ask for this same type of functionality (searching closed workbooks.) They wanted to search "thousands" of workbooks. Just give some thought to how many seconds it takes to open and close a workbook (no searching, just open and close), before you can go on to the next one. Work out how many you can do per minute... and so on.

You could use ADO, but again, the time? I'm not sure on Charlize's approach, as I haven't tried it either.

All I'm saying is to be aware of what you're getting into.

[Charlize]

Based on coding from xld (Bob) I've put this together. Just another idea.
[VBA]Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim vInvoiceno As String
'Assuming that invoiceno have following structure yyyy and invoiceno
'ie. 20051, 2006100, 20061001, 200710
'the first four characters are the year
'otherwise I have to use a file collection and check for every file
On Error GoTo errorfound
'Get the year because archive is saved as 2005.xls, 2006.xls, ...
vInvoiceno = InputBox("Give me invoiceno to search for ...", "Search in archive ...")
If vInvoiceno = "" Then Exit Sub
sFilename = "C:\Data\Invoice-archive\" & Mid(vInvoiceno, 1, 4) & ".xls"

'Define the sort of connection you want (excel)
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"
'Search sheetname or range Invoices ??? Don't sure which it is but it works
'when sheetname Invoices exists.
sSQL = "SELECT * FROM [Invoices$]"

Set oRS = CreateObject("ADODB.Recordset")

oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
'Search on first field ie. column A the invoiceno
'If you want to search on column B : oRS.fields(1).Name ...
oRS.Filter = oRS.fields(0).Name & " = '" & vInvoiceno & "'"
'Go to sheet2
Application.ScreenUpdating = False
Worksheets("Lookup").Activate
'Copy values to lookup sheet
ActiveSheet.Range("A1").CopyFromRecordset oRS
If Range("A1").Value <> "" Then
MsgBox "The info you requested for invoiceno : " & vInvoiceno & vbCrLf & _
"Invoiceno : " & Range("A1").Value & vbCrLf & _
"Client : " & Range("B1").Value & vbCrLf & _
"Date : " & Range("C1").Value & vbCrLf & _
"Amount : " & Format(Range("D1").Value, "0.00") & vbCrLf & _
"Paid : " & Range("E1").Value, vbInformation
ActiveSheet.Range("A1:E1").ClearContents
Else
MsgBox "No info found for invoiceno : " & vInvoiceno, vbInformation
End If
Else
MsgBox "No records returned. File is empty : " & Mid(vInvoiceno, 1, 4), vbCritical
End If
Sheets("Invoices").Activate
oRS.Close
Set oRS = Nothing
Exit Sub
errorfound:
MsgBox "There was an error during your search for : " & vInvoiceno & vbCrLf & _
"- File is not on disk ..." & vbCrLf & _
"- ???"
Set oRS = Nothing
End Sub
[/VBA]
Charlize

[rcbricker]

I appreciate the help on this, but I just found out that this was being down the hard way. Some one had started three seperate spreadsheets with all the data on them instead of hundreds as I thought.

I am pretty sure that we can use a VLOOKUP command to search those three spreadsheets. Would this be a quicker way to look for an invoice number or if I know where all three spreadsheets are and move them onto a share drive inside a folder, can something be build that is not as clumsy or slow?

[Ken Puls]

Using a vlookup on a list is going to be almost instant. Opening each drive to search could take, seriously, hours if you have a lot of files.

I'd go vlookup if you have the data. The only caveat I'd throw out is to be careful of how good your list is...