get all text value from sheet

[CsJHUN]

Hi!

I would like to get all text value on a worksheet into one variable.
I managed with cells, its obvious.

...
Sheets(Sheets.Count).Select
For Each cell In Range("A11:EE500")
t = t & " " & LCase(cell.Value)
Next cell...

But stucked with other object which should have text value. So far i managed something like this will do, but...

For Each obj In Worksheets(Sheets.Count).OLEObjects
??????
next obj

Any help/advice is welcome

 

[vds1]

What is your motive behind concatenating large set of cells assigning into one variable ?

 

[CsJHUN]

I would like to search multiple short string within this variable, and i think this is faster than searching for these string the sheet over and over again.


So far the macro snipet:

Sheets(Sheets.Count).Select
For Each cell In Range("A11:EE500")
t = t & " " & LCase(cell.Value)
Next cell
t = LCase(t) 'had do multiple lcase because some characters not changed to lower case (multiple character coding: japanese chars with latin and cirill)
'______________from this the instr search multipled
For elsokar = 0 To Len(t)
elsokar = InStr(elsokar + 1, t, "hmn")
On Error GoTo exithmn
tn = Mid(t, elsokar, 11) & "; " & tn
Next
Exit Sub
exithmn:
'_____________ until this

Basically i need every searched text separated with "; " to make some kind of index/appendix/link/bookmark/footnote thingy

 

[CsJHUN]

So i think i figured it out

....there is more before that
For Each shp In Worksheets(Sheets.Count).Shapes
    On Error Resume Next
    g = shp.GroupItems.Count
        If g > 1 Then
            For i = 1 To g
                szoveg = ActiveSheet.Shapes(shp.Name).GroupItems(i).DrawingObject.Text
                If Len(szoveg) > 5 Then a = a & "; " & szoveg
                Next i
        Else
            szoveg = ActiveSheet.Shapes(shp.Name).DrawingObject.Text
            If Err.Number = 438 Then GoTo nextshape
            If Len(szoveg) > 5 Then a = a & "; " & szoveg
        End If
nextshape:
    On Error GoTo -1
    g = 0
Next shp
...code continues here

For first i tried to ungroup the grouped shapes than read the text but i happier with this solution.