Prev: The last day
Next: XL 2010: Parent of a Range can be a Shape?
From: Jaded in Cali on 31 May 2010 17:43
I have a spreadsheet copied from a web page table, using Internet Explorer,
and pasted into an Excel Spreadsheet.

One of the data fields I need data from was presented as HTML Text Box form
objects. These apparently pasted into the spread sheet as HTMLText Objects.
They show the data, but it is not accessible to formulas or copying as long
as it is shown as entries in the text boxes.

There are 440 records (rows) in the table. I need to write a VBA script to
extract the data from the HTMLText boxes and write it to cells in the table.

Dim thisbox As textbox
Dim strThisBoxName As String
Dim intValue As Integer
Dim g As Integer

For g = 1 To 1
strThisBoxName = "HTMLText" & g
Set thisbox = Me.[strThisBoxName]
intValue = thisbox.Value
Cells(g, "L").Value = intValue
Next g

returns an Object Required error on the Set... line

Set thisbox = ActiveSheet(strThisBoxName)

and

Set thisbox = Me(strThisBoxName)

both return an "Object doesn't support this property or method" error.

A simple one line assignment:

Cells(g, "L").Value = Me.["HTMLText"& g].Value

returns an Object Required error, although the same line for a single
instance of the HTMLText box:

Cells(g, "L").Value = Me.HTMLText1.Value

works exactly as it should.

I cannot find the magic combination of brackets, parentheses, and periods to
successfully use a script-generated name for the HTMLText objects to access
their values.

Thank you in advance for any suggestions.
From: Dave Peterson on 31 May 2010 18:02
There are two textbox controls in excel.

One is from the Drawing toolbar and you could use:
Dim TB as textbox
set tb = activesheet.textboxes("HTMLText" & g)
msgbox tb.text

The other textbox is from the control toolbox toolbar and you could use:
Dim TB As msforms.TextBox
Set TB = ActiveSheet.OLEObjects("HTMLText" & g).Object
MsgBox TB.Text

=========
I bet you'll find that the objects pasted from the web page belong to the
control toolbox toolbar version of the textboxes.

You could use code like this to loop through either style:

Option Explicit
Sub testme()
Dim TB As TextBox 'from the Drawing toolbar
Dim OLEObj As OLEObject 'from the control toolbox toolbar
Dim DestCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set DestCell = .Range("A1")

'from the drawing toolbar
For Each TB In .TextBoxes
DestCell.Value = TB.Text
Set DestCell = DestCell.Offset(1, 0)
Next TB

'from the control toolbox toolbar
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is msforms.TextBox Then
DestCell.Value = OLEObj.Object.Value
Set DestCell = DestCell.Offset(1, 0)
End If
Next OLEObj
End With

End Sub

Jaded in Cali wrote:
>
> I have a spreadsheet copied from a web page table, using Internet Explorer,
> and pasted into an Excel Spreadsheet.
>
> One of the data fields I need data from was presented as HTML Text Box form
> objects. These apparently pasted into the spread sheet as HTMLText Objects.
> They show the data, but it is not accessible to formulas or copying as long
> as it is shown as entries in the text boxes.
>
> There are 440 records (rows) in the table. I need to write a VBA script to
> extract the data from the HTMLText boxes and write it to cells in the table.
>
> Dim thisbox As textbox
> Dim strThisBoxName As String
> Dim intValue As Integer
> Dim g As Integer
>
> For g = 1 To 1
> strThisBoxName = "HTMLText" & g
> Set thisbox = Me.[strThisBoxName]
> intValue = thisbox.Value
> Cells(g, "L").Value = intValue
> Next g
>
> returns an Object Required error on the Set... line
>
> Set thisbox = ActiveSheet(strThisBoxName)
>
> and
>
> Set thisbox = Me(strThisBoxName)
>
> both return an "Object doesn't support this property or method" error.
>
> A simple one line assignment:
>
> Cells(g, "L").Value = Me.["HTMLText"& g].Value
>
> returns an Object Required error, although the same line for a single
> instance of the HTMLText box:
>
> Cells(g, "L").Value = Me.HTMLText1.Value
>
> works exactly as it should.
>
> I cannot find the magic combination of brackets, parentheses, and periods to
> successfully use a script-generated name for the HTMLText objects to access
> their values.
>
> Thank you in advance for any suggestions.

--

Dave Peterson
 | 
Pages: 1
Prev: The last day
Next: XL 2010: Parent of a Range can be a Shape?