Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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? -- Gary Keramidas Excel 2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
Ah, just seen the button "Ajax with XBasic", never heard of Ajax before!
Peter T "Peter T" <peter_t@discussions wrote in message BTW, some web form there stuff I'd be interested to learn more about, could you point me in the right direction, thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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/product...0WebAppVideos/ 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 "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy data from an embedded html box
there are only html objects, it's pasted from the same screen every time.
-- Gary Keramidas Excel 2003 "Peter T" <peter_t@discussions wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tough one - image and embedded html document | Excel Programming | |||
Copy embedded chart with updated data request | Excel Programming | |||
Copy data from an HTML file and paste into excel using a macro | Excel Programming | |||
Delete embedded HTML | Excel Discussion (Misc queries) | |||
copy html data to spreadsheet | Excel Programming |