I am running the following macro to search for Account #'s in other excel
files. It is working great, except some of the files have worksheets with
Text Boxes on them, and the account # has been entered into the text box -
and the macro won't find it there. Is there any change that can be made to
also search within the text boxes?

Thanks!
Ann

Macro (Ignore the from my cut/paste):

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:=xlPart _
, 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

the Find command won't search textboxes. You would have to add code to look
in the textboxes. What kind of textboxes? From the control toolbox toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy


"Ann" wrote in message
...
I am running the following macro to search for Account #'s in other excel
files. It is working great, except some of the files have worksheets with
Text Boxes on them, and the account # has been entered into the text box -
and the macro won't find it there. Is there any change that can be made to
also search within the text boxes?

Thanks!
Ann

Macro (Ignore the from my cut/paste):

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:=xlPart _
, 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

Tom,

I am not sure how the boxes were created. Is there a way to tell? I did not
creat the workborks that have the text boxes in them, they are from another
dept.

Thanks!
Ann

"Tom Ogilvy" wrote:

the Find command won't search textboxes. You would have to add code to look
in the textboxes. What kind of textboxes? From the control toolbox toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy

Open one of the workbooks and select a sheet that has at least one of the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy




"Ann" wrote in message
...
Tom,

I am not sure how the boxes were created. Is there a way to tell? I did
not
creat the workborks that have the text boxes in them, they are from
another
dept.

Thanks!
Ann

"Tom Ogilvy" wrote:

the Find command won't search textboxes. You would have to add code to
look
in the textboxes. What kind of textboxes? From the control toolbox
toolbar
or from the Drawing Toolbar?

--
Regards,
Tom Ogilvy

Tom,

I am getting a "User-defined type not defined" error for this line:
If TypeOf obj.Object Is MSforms.TextBox Then

Can I assume that they are Drawing Toolbar Textboxes? If so, is there a way
to search through them?

Thanks!
Ann

"Tom Ogilvy" wrote:

Open one of the workbooks and select a sheet that has at least one of the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy

Dim tbox as Textbox

.. . .

for each tbox in .sheets(sh).Textboxes
if instr(1,AcNo,tbox.Text,vbTextcompare) then
' AcNo found

end if
Next

--
Regards,
Tom Ogilvy


"Ann" wrote in message
...
Tom,

I am getting a "User-defined type not defined" error for this line:
If TypeOf obj.Object Is MSforms.TextBox Then

Can I assume that they are Drawing Toolbar Textboxes? If so, is there a
way
to search through them?

Thanks!
Ann

"Tom Ogilvy" wrote:

Open one of the workbooks and select a sheet that has at least one of
the
textboxes.

by default, the control toolbox toolbar textbox has a impression of
depth/being embedded. However, this isn't foolproof

You can try running this:

Sub Checkthebox()
Dim obj as OleObject
Dim cnt as Long
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSforms.TextBox Then
cnt = cnt + 1
End If
Next
If cnt 0 Then
MsgBox "Control Toolbox Textboxes exist"
Else
MsgBox "control Toolbox Textboxes do not exist"
End If
End Sub

If the code won't run or it says they don't exist, then you probably
have
textboxes from the drawing toolbar.

this assumes there is not a mixture.

--
Regards,
Tom Ogilvy