Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Text box value from a web page copy and paste

    We are needing to get some information from a table on a web page at Amazon. It is easy enough to copy and paste it into Excel. However, some of the important data comes in a textbox. I want to extract that data and put it into the cell above the text box. I've tried VBA subs and formulas in the cell where I want the data to go but nothing seems to be working. With two cells, I want to take the data out of the bottom one which contains the textbox and put it into the top one and eventually merge the two cells to get rid of the texbox. Can anyone point me in the right direction?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    We need more information about the data you are using. Can you post a sample?

    cheers, Paul

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Here is a sample of just the two cells. I want to take the value out of the textbox that is in A2 and put it into A1.
    I will have several thousand of these to do or I could just type the value in...TextboxExample.jpg
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    No one has any ideas on how to get the value of the textbox? I've tried everything I can think of. From the above example, I've tried things like:
    MsgBox (Range("A2").Value) - I get a blank message box
    MsgBox (HTMLText1.Value) - I get a blank message box
    MsgBox (HTMLText1.Text) - I get a blank message box
    MsgBox (ActiveSheet.Shapes("HTMLText1").Text) - I get a message - "The item with the specified name wasn't found."
    Various other ideas have resulted in blanks. I want to put the value into cell A1 like Range("A1").Value = HTMLText1.Text but that isn't working so I tried the message box just to see if I could get the value and I can't do it.

    I've never asked a question here before where the answer wasn't available. I've done a lot of searching on the internet and can't find an answer. I can't imagine that this is too much for you Excel gurus.

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Have you tried copying the data into a text file (e.g. using Notepad or Notepad++)? This should lose some of the formatting and may well leave the text as text.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    This code will put the values into column [B] in the corresponding row. Adjust as required.
    Code:
    Sub getValues()
    
    Set zSht = ActiveSheet
    With zSht
    For Each Z In .OLEObjects
    If TypeOf Z.Object Is msforms.TextBox Then
    r = Z.TopLeftCell.Row
    Cells(r, "B").Value = Z.Object.Value
    End If
    Next Z
    End With
    
    End Sub
    zeddy
    Polo Instructor

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That looks great but when I run it, I get a message saying, "Unable to get the TopLeftCell property of the OLEObject class". I wonder if the textbox pasted from a web page is an OLEObject. When I look at the properties of the textbox from the developer tab in design mode, it says "HTMLText1 HTMLText" at the top in the combobox that contains the list of objects on the sheet.

    I had not tried to paste the web information into Notepad+ but have since tried that and it does not include either the textbox or the value in it when I paste it there.

  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I finally got it to work. My code is very similar to the suggestion from Zeddy but there are some differences. Nonetheless, thanks to all who contributed. Apparently there are two types of textboxes that can be in Excel - one from the Drawing toolbar, and one from the control toolbox toolbar (these happened to be the latter). My solution is below:

    Dim OLEObj As OLEObject 'from the control toolbox toolbar
    Dim DestCell As Range
    Dim wks As Worksheet
    Set wks = ActiveSheet
    With wks
    For Each OLEObj In .OLEObjects
    If TypeOf OLEObj.Object Is msforms.TextBox Then
    Set DestCell = OLEObj.TopLeftCell.Offset(-1, 0)
    DestCell.Value = OLEObj.Object.Value
    End If
    Next OLEObj
    End With

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..puzzled about the message you got in post#7.
    I took your sample file, copied a few more test entries, and then ran my routine.
    It worked OK.
    see attached file.

    zeddy
    •Repercussions Team Leader
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yes, that is rather strange. Your file worked fine for me too. I only changed a little bit of your code as you can see. Perhaps I had a typo at first. Bottom line, it works now and saves me hours of time. Thanks for your help. J. P. Zinn

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    As TopLeftCell is used in both routines I guess it was a typo.

    If you want to delete the boxes after getting the values, you could use code like this:
    Code:
    Sub getValues()
    
    Set zSht = ActiveSheet          'define shortcut
    With zSht                       'use shortcut
    For Each zBox In .OLEObjects    'loop through all ActiveX controls on sheet
    If TypeOf zBox.Object Is MSForms.TextBox Then   'check control type
    r = zBox.TopLeftCell.Row                        'control is on this row number
    Cells(r, "A").Value = zBox.Object.Value         'put value into column A, then..
    zBox.Delete                                     '..delete the control
    End If                          'end of test for control type
    Next zBox                       'process next control
    End With                        'end of shortcut
    
    End Sub
    see attached example file

    zeddy
    •Toast Quality Assurance Manager
    Attached Files Attached Files

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    jpzinn,

    Is it possible to provide the url of Amazon site and the location of the textbox on the webpage. I may be able to provide you the code you will need to extract the data right off of the web page into Excel.

    Maud

  13. #13
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I did delete the boxes and there were also some checkboxes and comboboxes on the worksheet so I just deleted all the objects. I didn't need an if TypeOf section since I deleted them all. Thanks though.

  14. #14
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud, the Amazon site is on sellercentral but it belongs to a business owner and is his inventory with his login information. What I have now works really well and saves me hours of time. I would be interested in knowing how to extract data right from a web page though...

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    JP,

    By looking at the source code from the webpage, you can determine the "id" of the textbox then extract the data. You can place a webBrowser control in your workbook to open the URL then use the following line of code to extract the value

    Range("A1")=WebBrowser1.Document.getelementbyid("i dName").Value where idName is the id name you found in the source code.

    The entire code might look something like this:

    Code:
    Private Sub GetValue()
    '----------------------------------
    'NAVIGATE TO URL
    WebBrowser1.Visible = True
    WebBrowser1.Navigate2 "https://NameOfWebite.com"
    Do
    DoEvents
    Loop Until WebBrowser1.ReadyState = 4
    '----------------------------------
    'COPY TEXTBOX VALUE TO CELL A1
    Range("A1") = WebBrowser1.Document.getelementbyid("IDnAME").Value
    End Sub
    Note that you will need to reference Microsoft Internet Controls in the VB editor
    Tools > Refernces > Microsoft Internet Controls > OK

    IElibrary.png

    The control is added to the worksheet from Developer tab > Insert > ... > Microsoft Web Browser

    msbrowser.png

    HTH,
    Maud

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •