Text box value from a web page copy and paste

Topic

New Reply

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?

Reply | Quote

Replies

We need more information about the data you are using. Can you post a sample?

cheers, Paul

Reply | Quote

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…41236-TextboxExample

Reply | Quote

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.

Reply | Quote

Hi

This code will put the values into column 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

Reply | Quote

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.

Eliminate spare time: start programming PowerShell

Reply | Quote

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.

Reply | Quote

I have seen the “Unable to get the TopLeftCell property of the OLEObject class” error as a result of Excel’s VBE getting confused and, apparently, compiling the code incorrectly. Save the file, re-open it, and the problem should go away.

Reply | Quote

I get a message saying, “Unable to get the TopLeftCell property of the OLEObject class”.

I have seen the “Unable to get the TopLeftCell property of the OLEObject class” error as a result of Excel’s VBE getting confused and, apparently, compiling the code incorrectly. Save the file, re-open it, and the problem should go away.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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(“idName”).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()
[COLOR=”#008000″]’———————————-
‘NAVIGATE TO URL[/COLOR]
WebBrowser1.Visible = True
WebBrowser1.Navigate2 “https://NameOfWebite.com”
Do
DoEvents
Loop Until WebBrowser1.ReadyState = 4
[COLOR=”#008000″]’———————————-
‘COPY TEXTBOX VALUE TO CELL A1[/COLOR]
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

41274-IElibrary

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

41275-msbrowser

HTH,
Maud

Reply | Quote

I did delete the boxes and there were also some checkboxes and comboboxes on the worksheet so I just deleted all the objects.

Btw, the following snippet will delete all the objects on the active sheet instead of manually deleting them.

Code:

    ActiveSheet.DrawingObjects.Select
    Selection.Cut

Maud

Reply | Quote