Addressing HTMLText object in Spreadsheet VBA Script

[Jaded in Cali]

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]

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

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.

 

[Andee Zetterbaum]

Dave Peterson's code is almost exactly what I need for a similar situation. However, that code puts the results in a single column. In my case, the html text boxes are in several columns on the spreadsheet, and I need the contents placed in the same cell where the text box is located. Ideally, I'd then like to automatically remove the text box controls, leaving just the values. Can anyone help me adapt the code accordingly?

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 does not 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.

On Monday, May 31, 2010 6:02 PM Dave Peterson wrote:
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 will 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:

--

Submitted via EggHeadCafe - Software Developer Portal of Choice
Mocking WCF Services Using Moq
http://www.eggheadcafe.com/tutorial...61d435d81/mocking-wcf-services-using-moq.aspx

 

[Dave Peterson]

Textboxes can cover multiple cells. Maybe you can use the topleftcell of the
range that the textbox covers.

Change the code that points to the DestCell. And add a line to remove the textbox:

For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is msforms.TextBox Then
Set Destcell = OLEObj.TopLeftCell
Destcell.Value = OLEObj.Object.Value
OLEObj.Delete
End If
Next OLEObj

Dave Peterson's code is almost exactly what I need for a similar situation. However, that code puts the results in a single column. In my case, the html text boxes are in several columns on the spreadsheet, and I need the contents placed in the same cell where the text box is located. Ideally, I'd then like to automatically remove the text box controls, leaving just the values. Can anyone help me adapt the code accordingly?

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 does not 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.

On Monday, May 31, 2010 6:02 PM Dave Peterson wrote:
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 will 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:

--

Submitted via EggHeadCafe - Software Developer Portal of Choice
Mocking WCF Services Using Moq
http://www.eggheadcafe.com/tutorial...61d435d81/mocking-wcf-services-using-moq.aspx