Note your code will error if any the sheet includes any non Forms.HTML type
ole-objects (eg Excel worksheet aX controls), hence the pre-check of the
progID in my example. If you have different types of HTML objects do
something like

If Left$(ole.progID, 10) = "Forms.HTML" Then

Thanks for the links in your earlier post.

Regards,
Peter T


"Gary Keramidas" wrote in message
...
peter:

thanks for the code.i was able to get the result i wanted using your idea.
here's what i ended up with, using the cryptic object HTMLnames that get
pasted into the sheet.
thanks again.

For Each ole In ws.OLEObjects
With ws3.Range("B1")
Select Case ole.Object.HTMLName
Case "SNA"
.Value = ole.Object.Value
Case "SA1"
.Offset(, 1).Value = ole.Object.Value
Case "SA2"
.Offset(, 2).Value = ole.Object.Value
Case "SCT"
.Offset(, 3).Value = ole.Object.Value
Case "SST"
.Offset(, 4).Value = ole.Object.Value
Case "SZP"
With .Offset(, 5)
.Value = "'" & ole.Object.Value
.Value = """" & .Value & """"
End With
End Select
End With
Next

--


Gary Keramidas
Excel 2003


"Peter T" <peter_t@discussions wrote in message
...
Your link does not give a page with any fields to fill out. However I
went to Web-security/Login, entered my name in the User-ID field, copied
all to Excel. The following returned the name I had entered in IE

Sub test()
Dim sUserID As String
Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If ole.progID = "Forms.HTML:Text.1" Then
If ole.Object.HTMLName = "Userid" Then
sUserID = ole.Object.Value
Debug.Print sUserID
End If
End If
Next

End Sub

BTW, some web form there stuff I'd be interested to learn more about,
could you point me in the right direction, thanks.

Regards,
Peter T

"Gary Keramidas" wrote in message
...
thanks joel, but that won't work. it's not a drawing object. it's and
embedded field that shows embed("forms.html:text.1","") when it's
selected.

i did forget to put the select line when i posted, so the box is
selected. like i mentioned, it does work, i am just looking for another
way to get the data from the field.
if you want to simulate what i have, you can go to this site and fill in
your name. copy the 2 fields and paste them into sheet 1. delete column
A. to get rid of the labels and run the code below. it's not exactly
what i do, but close enough. it should put your name in a1 & b1.


http://65.75.250.238/webApplicationD...ault/grids.a5w

Sub ReNumber_Boxes()
Dim ws As Worksheet
Dim ws3 As Worksheet
Dim shp As Shape
Dim k As Long
Dim i As Long
Dim z As Long
k = 1
Set ws = Worksheets("Sheet1")
Set ws3 = Worksheets("Sheet3")

For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name
shp.Name = "Box " & k
k = k + 1
Next

Application.CutCopyMode = False
z = 1
ws.Activate

For i = 1 To ws.Shapes.Count
ws.Shapes("Box " & i).Select
Selection.Verb Verb:=xlPrimary
SendKeys "^A", True
SendKeys "^C", True
ws3.Cells(1, z).PasteSpecial xlPasteAll
Application.CutCopyMode = False
z = z + 1
Next
End Sub






Gary Keramidas
Excel 2003


"joel" wrote in message
...

It is hard to determine the root cause of the problem from the the code
you provided. I do have an issue with the code you posted

With ws.Shapes("Box " & i)
Selection.Verb Verb:=xlPrimary
SendKeys "^A", True
SendKeys "^C", True
end with


The shape may not be selected using the With statement since nothing in
the with has a period in front of it. I don't know what a box is but
try this change. I took a rectangle and added text to the shape. Not
sure if that is what you are doing. It is only a guess.

From
Selection.Verb Verb:=xlPrimary
To
DrawingObject.Caption


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=183255

Microsoft Office Help