extract data from web site

[mysterybks]

Is it possible with VBA to have it take info from a database and open an Amazon page, extract data from the page, and save it to the database record?

 

[georgedwilkinson]

Yes. Take a look at IE automation. It is not straightforward.

 

[Uncle Gizmo]

>>>It's not straightforward <<<

no, it looks difficult!

 

[georgedwilkinson]

It's doable, if you already have enough experience to not have to ask another question.

 

[Uncle Gizmo]

don't Amazon have a developer's kit or something?

 

[georgedwilkinson]

It really depends on what the OP is doing. If you just want to sell all Amazon products, you can use an Amazon widget: https://affiliate-program.amazon.com/

 

[mysterybks]

Thank you. I did some searching and found this good code:

[FONT=Courier][SIZE=2][COLOR=#00007f]Sub[/COLOR] ListLinks()[/SIZE][/FONT]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Dim[/COLOR] IeApp [COLOR=#00007f]As[/COLOR] InternetExplorer[/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Dim[/COLOR] sURL [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Dim[/COLOR] IeDoc [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Dim[/COLOR] i [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Long[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘Create new instance of IE[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Set[/COLOR] IeApp = [COLOR=#00007f]New[/COLOR] InternetExplorer[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘Make it visible - some things don’t work[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘unless it’s visible[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   IeApp.Visible = [COLOR=#00007f]True[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘define the page to open[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   sURL = “www.dicks-blog.com”[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘navigate to the page[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   IeApp.Navigate sURL[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘Pause the macro using a loop until the[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘page is fully loaded[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Do[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Loop[/COLOR] [COLOR=#00007f]Until[/COLOR] IeApp.ReadyState = READYSTATE_COMPLETE[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]’store the Document object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Set[/COLOR] IeDoc = IeApp.Document[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘Loop through the links collection.  Most collections[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#007f00]’seem to be zero based, but it’s pretty much trial and[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘error for me[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]For[/COLOR] i = 0 [COLOR=#00007f]To[/COLOR] IeDoc.links.Length - 1[/FONT][/SIZE]
[SIZE=2][FONT=Courier]       [COLOR=#007f00]‘write the linking url to a cell[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]       Cells(i + 1, 1).Value = IeDoc.links(i).href[/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Next[/COLOR] i[/FONT][/SIZE]
 
[SIZE=2][FONT=Courier]   [COLOR=#007f00]‘Clean up[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Courier]   [COLOR=#00007f]Set[/COLOR] IeApp = [COLOR=#00007f]Nothing[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT][/SIZE]

What I want to know is where they have:

[COLOR=#007f00]‘Loop through the links collection.  Most collections[/COLOR]
    [COLOR=#007f00]’seem to be zero based, but it’s pretty much trial and[/COLOR]
    [COLOR=#007f00]‘error for me[/COLOR]
    [COLOR=#00007f]For[/COLOR] i = 0 [COLOR=#00007f]To[/COLOR] IeDoc.links.Length - 1
        [COLOR=#007f00]‘write the linking url to a cell[/COLOR]
        Cells(i + 1, 1).Value = IeDoc.links(i).href
    [COLOR=#00007f]Next[/COLOR] i

Is there a way to have it look up the ISBN number and the publication date on the page about a book and capture it for my database?

Can you steer me in the right direction to find it? Would I look at the source code for the page to see how it's presented? I just checked and can't read source code for an Amazon page. So, can the VBA code read what's displayed on the screen? Then do I just look for ISBN and capture a certain amount of characters to the right? I'm used to using Input #1 etc.


I am not a novice by any means, but this is something I've never tackled before.

Dawn

 

[georgedwilkinson]

Yes, you need to read the data from off the page. You can do this with sub-objects of the IE object (inner html comes to mind but you need to check it out).

I've also done this using Java and it was pretty easy. But the IE method became my favorite because you can POST form data more easily.