better way to copy data from an embedded html box

[Gary Keramidas]

i have some fields from a browser window pasted into excel. the become
shapes when pasted into excel. i give the shapes names starting with box 1
to box ?, depending on what's been pasted into excel.

when one shape is selected, it shows the following the the formula bar:
embed("forms.html:text.1","").

right now i have some code that will copy the data from the field:

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

this worked fine until my client got a new pc. i run windows 7 and don't
have an issue, but they have an acer. it also resets the keyboard indicators
so i'm wondering if there is a better way to get the text from the shape?

 

[joel]

It is hard to determine the root cause of the problem from the the cod
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 i
the with has a period in front of it. I don't know what a box is bu
try this change. I took a rectangle and added text to the shape. No
sure if that is what you are doing. It is only a guess.

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

 

[Gary Keramidas]

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/webApplicationDemoV10/default/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

 

[Peter T]

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

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/webApplicationDemoV10/default/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

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/showthread.php?t=183255

Microsoft Office Help

 

[Peter T]

Ah, just seen the button "Ajax with XBasic", never heard of Ajax before!

Peter T

 

[Gary Keramidas]

it's a pretty powerful database program called alpha version 10 from alpha
software. they call it codeless ajax because a lot of it can be written just
by making selections. it can read and write sql, too.

check it out here. http://www.alphasoftware.com/
there are a lot of videos of the features here,
http://www.alphasoftware.com/products/v10/video/A5V10WebAppVideos/
and yes, you would have needed to click, grid searches, basic search to get
where i wanted you to go. thought the link would do it, but i guess not.

i will check your solution, thanks a lot.

--


Gary Keramidas
Excel 2003

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

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/webApplicationDemoV10/default/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

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/showthread.php?t=183255

Microsoft Office Help

 

[Gary Keramidas]

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

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

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/webApplicationDemoV10/default/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

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/showthread.php?t=183255

Microsoft Office Help

 

[Peter T]

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

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

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

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/webApplicationDemoV10/default/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



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/showthread.php?t=183255

Microsoft Office Help

 

[Gary Keramidas]

there are only html objects, it's pasted from the same screen every time.

--


Gary Keramidas
Excel 2003

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

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

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

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/webApplicationDemoV10/default/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



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/showthread.php?t=183255

Microsoft Office
Help