Search/Compare VBA Help
 

I posted this problem yesterday and got great help from Rowan, but I am
having a problem and the original question is now on Page 4 so I am reposting
it. I'd appreciate anyone who could tweak the VBA to help. (see below).
Thanks!

Original Problem:
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's VBA answer:
"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

New Problem:

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

Search/Compare VBA Help
 

Ann,

Change

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

to

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

HTH,
Bernie
MS Excel MVP


"Ann" wrote in message
...
I posted this problem yesterday and got great help from Rowan, but I am
having a problem and the original question is now on Page 4 so I am reposting
it. I'd appreciate anyone who could tweak the VBA to help. (see below).
Thanks!

Original Problem:
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's VBA answer:
"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

New Problem:

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

Search/Compare VBA Help
 

At a quick glance I think you need to change
Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)

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

Hope this helps. I did not test it.
--
Mike Q.


"Ann" wrote:

I posted this problem yesterday and got great help from Rowan, but I am
having a problem and the original question is now on Page 4 so I am reposting
it. I'd appreciate anyone who could tweak the VBA to help. (see below).
Thanks!

Original Problem:
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's VBA answer:
"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

New Problem:

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

Search/Compare VBA Help
 

Thank you Bernie and Mike! That was the problem!

"Bernie Deitrick" wrote:

Ann,

Change

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

to

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

HTH,
Bernie
MS Excel MVP


"Ann" wrote in message
...
I posted this problem yesterday and got great help from Rowan, but I am
having a problem and the original question is now on Page 4 so I am reposting
it. I'd appreciate anyone who could tweak the VBA to help. (see below).
Thanks!

Original Problem:
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's VBA answer:
"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

New Problem:

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