I have a worksheet with account numbers in Column A and a description in
Column B.

I would like to search for each account number's presence in several files
in a folder on my hard drive. If the account number appears in any of the
files in that folder, I'd like to have "Yes" inserted into Column C. If not,
"No" should be inserted.

The files I am searching are all Excel files, located in the same folder,
have multiple sheets, and the data is not necessarily always in the same
place. My account number worksheet is in a separate folder.

I am using Excel 2003, I can insert and run VBA - just not good at writing it!

Thanks!
Ann

See a similar request he http://tinyurl.com/9lj44

Try this amendment:

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value < "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan

Ann wrote:
I have a worksheet with account numbers in Column A and a description in
Column B.

I would like to search for each account number's presence in several files
in a folder on my hard drive. If the account number appears in any of the
files in that folder, I'd like to have "Yes" inserted into Column C. If not,
"No" should be inserted.

The files I am searching are all Excel files, located in the same folder,
have multiple sheets, and the data is not necessarily always in the same
place. My account number worksheet is in a separate folder.

I am using Excel 2003, I can insert and run VBA - just not good at writing it!

Thanks!
Ann

Rowan,

I set up a test file with 6 Accouint #s in column A and descriptions in
column B. And a folder with one Excel workbook (multiple sheets) in C:\Test.
The first 3 account #'s are in the workbook, the last 3 are not.

I am getting "No" for all 6, because the Account numbers in the test file
are not always the only data in the cell. For example: the cell actually
contains "Account 03-32467", when the test file only specifies "03-32467". If
I change the entry in the workbook to show only "03-32467", I do get a "Yes"
returned, so the program you gave me is working - but it is not searching
within each cell.

Unfotunately, the workbooks I will be searching will have the account #'s in
cells with other text. Is there a way to modify the program to search
specifically for the account # within other text. It will not always be alone
in a cell.

Thanks!
Ann

"Rowan Drummond" wrote:

See a similar request he http://tinyurl.com/9lj44

Try this amendment:

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value < "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan

Ann wrote:
I have a worksheet with account numbers in Column A and a description in
Column B.

I would like to search for each account number's presence in several files
in a folder on my hard drive. If the account number appears in any of the
files in that folder, I'd like to have "Yes" inserted into Column C. If not,
"No" should be inserted.

The files I am searching are all Excel files, located in the same folder,
have multiple sheets, and the data is not necessarily always in the same
place. My account number worksheet is in a separate folder.

I am using Excel 2003, I can insert and run VBA - just not good at writing it!

Thanks!
Ann

Hi Ann

You should be able to fix this quite easily by changing the statment:

Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)

to:

Set fndAc = .Find(AcNo _
, lookat:=xlPart, LookIn:=xlValues _
, MatchCase:=False)

Regards
Rowan

Ann wrote:
Rowan,

I set up a test file with 6 Accouint #s in column A and descriptions in
column B. And a folder with one Excel workbook (multiple sheets) in C:\Test.
The first 3 account #'s are in the workbook, the last 3 are not.

I am getting "No" for all 6, because the Account numbers in the test file
are not always the only data in the cell. For example: the cell actually
contains "Account 03-32467", when the test file only specifies "03-32467". If
I change the entry in the workbook to show only "03-32467", I do get a "Yes"
returned, so the program you gave me is working - but it is not searching
within each cell.

Unfotunately, the workbooks I will be searching will have the account #'s in
cells with other text. Is there a way to modify the program to search
specifically for the account # within other text. It will not always be alone
in a cell.

Thanks!
Ann

"Rowan Drummond" wrote:


See a similar request he http://tinyurl.com/9lj44

Try this amendment:

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value < "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan

Ann wrote:

I have a worksheet with account numbers in Column A and a description in
Column B.

I would like to search for each account number's presence in several files
in a folder on my hard drive. If the account number appears in any of the
files in that folder, I'd like to have "Yes" inserted into Column C. If not,
"No" should be inserted.

The files I am searching are all Excel files, located in the same folder,
have multiple sheets, and the data is not necessarily always in the same
place. My account number worksheet is in a separate folder.

I am using Excel 2003, I can insert and run VBA - just not good at writing it!

Thanks!
Ann