How to capture values from ‘text box’.

[ThrottleWorks]

I have an excel file. In this file, we get few values in ‘text box’.
Basically this is an extract from website and pasted for further processing.
I am required to capture values from these boxes.
Is it possible. Can anyone please help me in this.

Please see attached file for more details.

 

[Hui]

Try this code:

Sub Extract_Text_Boxes()

Dim OLECont As OLEObject
For Each OLECont In Worksheets("Sheet1").OLEObjects
  If OLECont.progID = "Forms.HTML:Text.1" Then
  OLECont.TopLeftCell.Offset(0, 2) = OLECont.Object.Value
  End If
Next OLECont
 
End Sub

 

[Chihiro]

You can do something like below.

Sub Demo()
Dim obj As OLEObject

For Each obj In Sheet1.OLEObjects
    If TypeOf obj.Object Is MSForms.TextBox Then
        obj.TopLeftCell.Value = obj.Object.Value
        obj.Delete
    End If
Next

End Sub

 

[Hui]

Chiro

When I run that it deletes all the Objects on the first pass and then crashes as it can't find any more objects ?

 

[Chihiro]

Ah right, my bad. You need error trap for second run. Or check count of OLEObjects before entering loop.

 

[ThrottleWorks]

Hi @Hui sir, thanks a lot for the help. It is working nice.
PS - Interacting directly with you after long time I guess. Good night.

Hi @Chihiro sir, thanks a lot for the help. Have a nice day ahead.