Macro search text boxes

[Mike H]

Is there any way for a macro to search or iterate through text boxes?
I need to find and replace text that might be within text boxes
(and not just in cells), for hundreds of spreadsheets.
I don't know what excel or windows versions, but probably 2003 on XP.

 

[Chip Pearson]

The following code will look at every workbook in a folder, search
every text box (ActiveX control textbox) on every worksheet, and
replace FindText with ReplaceText. Change the lines marked with
'<<<<< to the values you need.

Sub AAA()
Dim Folder As String
Dim FileName As String
Dim WB As Workbook
Dim WS As Worksheet
Dim OLEObj As OLEObject
Dim FindText As String
Dim ReplaceText As String
Dim S As String

Folder = "C:\Test" '<<<<< CHANGE AS NEEDED
FindText = "abc" '<<<<< CHANGE AS NEEDED
ReplaceText = "def" '<<<<< CHANGE AS NEEDED
ChDrive Folder
ChDir Folder
FileName = Dir("*.xls", vbNormal)
Do Until FileName = vbNullString
Set WB = Workbooks.Open(FileName)
For Each WS In WB.Worksheets
For Each OLEObj In WS.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
S = Replace(OLEObj.Object.Text, _
FindText, ReplaceText)
OLEObj.Object.Text = S
End If
Next OLEObj
Next WS
WB.Close savechanges:=True
FileName = Dir()
Loop
End Sub



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Mike H]

Thanks a bunch. Looks like you've done that before.
I already had the search folders part, the OLEObj is the part I needed.
I'll put it to good use at work.

 

[Chip Pearson]

Thanks a bunch. Looks like you've done that before.

After 12 years of Office and Excel development, sometimes I feel like
I've done everything before.

I already had the search folders part, the OLEObj is the part I needed.

The OleObject is basically a generic container with few properties of
its own. The real key is OleObject.Object which gets you a reference
to the actual object contained within OleObject.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[bryen]

Dear All,
I'm still teaching myself VBA, and there's a huge problem whose
solution i cannot figure out. Your help would be greatly appreciated.
i have an input workbook from which to extract data to another
workbook (thus 2 different workbooks. The input sheet has data split
up in five categories. Each category has five rows of data. So say the
categories are titled: A, B, C, D, and E in column A. With each
category separated by five rows, each row (in column C) has different
numbers. The output sheet (in the other workbook) has all categories
A, B, C, D, and E arranged (not alphabetically) in separate rows
(column 1), with each category separated by five empty rows. The
solution i desperately need is for a macro to input data within their
matching category in the input sheet. I think this means that it has
to check that the category within the input sheet matches that in the
output sheet, so that it can place that categories data in the right
category's columns. again, your help will be highly appreciated.

 

[Mike H]

Bryen,
You might want to move this to a new thread - this thread was already two
days old when you posted it and no one will ever see it here.

MH