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
On 09/14/2010 12:14, Andee Zetterbaum wrote:
> 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?
>
>> On Monday, May 31, 2010 5:43 PM 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 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/tutorials...using-moq.aspx
--
Dave Peterson
Similar Threads
