Prev: Save as PDF not working on a domained Windows 7/Vista box
Next: Make (save) a file by using macro recorder.
From: Jaded in Cali Jaded in on 28 May 2010 19:21
I have a spreadsheet (Excel 2003) of data copied from a web page that has
critical data contained in a column of 400 text boxes. I need to remove the
text boxes from the spreadsheet, but retain the data. So far, I have been
able to address the text boxes one at a time from VBA and extract the data,
but I need to know how to address them from within the code. The text boxes
are named HTMLText1 through HTMLText400. I can generate the names using a
For loop, but using them to address the text boxes stumps me.

Dim strThisBoxName As String
Dim intValue As Integer
Dim g As Integer

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

returns an Invalid Qualifier error on the intValue = ... line.

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 a "Needs Object" error on the Set thisbox... line.

I hope from the code, someone can see what I am trying to do and supply the
correct syntax to accomplish it.

Thank you in advance.
From: B Lynn B on 28 May 2010 20:14
I imagine your textboxes may be activex controls, in which case this should
work:

For g = 1 To 400
Cells(g, "L").Value = _
ActiveSheet.OLEObjects("HTMLText" & g).Object.Text
Next g

If somehow they were Shapes on your sheet, this should do it:

Dim g As Long
Dim myShp As Shape
Dim CharCount As Long

For g = 1 To 1
Set myShp = ActiveSheet.Shapes("HTMLText" & n)
CharCount = shp.TextFrame.Characters.Count
Cells(g, "L").Value = shp.TextFrame.Characters(1, CharCount).Text
Next g

Set myShp = Nothing


"Jaded in Cali" wrote:

> I have a spreadsheet (Excel 2003) of data copied from a web page that has
> critical data contained in a column of 400 text boxes. I need to remove the
> text boxes from the spreadsheet, but retain the data. So far, I have been
> able to address the text boxes one at a time from VBA and extract the data,
> but I need to know how to address them from within the code. The text boxes
> are named HTMLText1 through HTMLText400. I can generate the names using a
> For loop, but using them to address the text boxes stumps me.
>
> Dim strThisBoxName As String
> Dim intValue As Integer
> Dim g As Integer
>
> For g = 1 To 1
> strThisBoxName = "HTMLText" & g
> intValue = [strThisBoxName].Value
> Cells(g, "L").Value = intValue
> Next g
>
> returns an Invalid Qualifier error on the intValue = ... line.
>
> 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 a "Needs Object" error on the Set thisbox... line.
>
> I hope from the code, someone can see what I am trying to do and supply the
> correct syntax to accomplish it.
>
> Thank you in advance.
From: Jaded in Cali on 31 May 2010 17:18
Neither of those strategies worked. The OLE version returned an error: OLE
Object not found. The ActiveX version returned a similar error.

Clicking on Properties for the boxes, they are listed as HTMLText objects.
Visual Basic allowed me to Dim the ThisBox variable as "HTMLText" and
provides a HTMLText.value property, but I could not figure out how to address
the boxes using the loop variable.

Any clues would be greatly appreciated.

Thanks to all.

 | 
Pages: 1
Prev: Save as PDF not working on a domained Windows 7/Vista box
Next: Make (save) a file by using macro recorder.