Microsoft Excel 2010 Web Query Macro: Pulling Multiple Pages From One -

-

i looking find on macro.. idea is, upon execution macro pull data web page (i.e http://www.link.com/id=7759) , place let's sheet2, , open page 2, , place right below page 1's data in sheet 2.... , on , on until set page number.. ideally pull following in order;

title artist type paper size image size retail prize quantity

and further more ideal placed in proper columns , rows of 4 , 8 rows down(columns across in web page).

any on greatly, appreciated. have done research , found similar macros, sadly have had no luck getting them work me. vb's fail go through well.

bit of useful info (maybe) figured out when trying write own, maybe save ever helps time..

.webtables = "8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38"

those tables each item want put que...

here's sample method going

based on few assumptions

  • workbook contains sheet hold query data called "query"

  • workbook contains sheet put data in called "alldata"

  • all old data removed on running macro

  • i think need include table 7 in qyuery

  • pages process hard coded for pg = 1 1 , change suit

.

sub querywebsite()     dim shquery worksheet, shalldata worksheet     dim cldata range      dim qts querytables     dim qt querytable     dim pg long, long, n long, m long     dim vsrc variant, vdest() variant      ' setup query     set shquery = activeworkbook.sheets("query")     set shalldata = activeworkbook.sheets("alldata")      'set qt = shquery.querytables(1)     on error resume next      set qt = shquery.querytables("liebermans")     if err.number <> 0         err.clear         set qt = shquery.querytables.add( _             connection:="url;http://www.liebermans.net/productlist.aspx?id=7759&page=1", _             destination:=shquery.cells(1, 1))         qt             .name = "liebermans"             .webselectiontype = xlspecifiedtables             .webformatting = xlwebformattingnone             .webtables = "7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38"             .webpreformattedtexttocolumns = true             .webconsecutivedelimitersasone = true             .websingleblocktextimport = false             .webdisabledaterecognition = false             .webdisableredirections = false             .refresh backgroundquery:=false         end     end if     on error goto 0      = instr(qt.connection, "&page=")      ' clear old data     shalldata.usedrange.clearcontents     shalldata.cells(1, 1) = "title"     shalldata.cells(1, 2) = "artist"     shalldata.cells(1, 3) = "type"     shalldata.cells(1, 4) = "paper size"     shalldata.cells(1, 5) = "image size"     shalldata.cells(1, 6) = "price"     shalldata.cells(1, 7) = "quantity"       m = 0     redim vdest(1 10000, 1 7)     pg = 1 1         ' query wb site         qt.connection = left(qt.connection, + 5) & pg         qt.refresh false          ' process data         vsrc = qt.resultrange         n = 2         while n < ubound(vsrc, 1)             if vsrc(n, 1) <> "" , vsrc(n - 1, 1) = ""                 m = m + 1                 vdest(m, 1) = vsrc(n, 1)             end if             if vsrc(n, 1) "artist:*" vdest(m, 2) = trim(mid(vsrc(n, 1), 8))             if vsrc(n, 1) "type:*" vdest(m, 3) = trim(mid(vsrc(n, 1), 6))             if vsrc(n, 1) "paper size:*" vdest(m, 4) = trim(mid(vsrc(n, 1), 12))             if vsrc(n, 1) "image size:*" vdest(m, 5) = trim(mid(vsrc(n, 1), 12))             if vsrc(n, 1) "retail price:*" vdest(m, 6) = trim(mid(vsrc(n, 1), 14))             if vsrc(n, 1) "quantity in stock:*" vdest(m, 7) = trim(mid(vsrc(n, 1), 19))              n = n + 1         loop     next      ' put data in sheet     shalldata.cells(2, 1).resize(m, 7) = vdest  end sub

Comments

Post a Comment

Popular posts from this blog

objective c - Change font of selected text in UITextView -

-
can suggest me how change font of selected text in uitextview.i cannot use uiwebview since have edit text. have seen pages app.how possible in app. see formatting text within uilabel differently . since need editing, should @ javascript-based editors such ckeditor or tinymce . highly recommend wwdc 2011 video session 511: "rich text editing in safari on ios."
Read more

php - Accessing POST data in Facebook cavas app -

-
so i've got simple form in canvas / iframe facebook app, , i'm trying pass along values post. reading on s.o. , fb's latest docs, understand it, data send via post form can accessed on receiving end $_request object. i read on thread on s.o. in order post forms work need pass along input named "signed_request" value current signed_request (i have signed request working ok otherwise...all login , authentication stuff working fine). isnt mentioned anywhere in official fb docs. so problem comes in $_request object signed request, , bunch of other session stuff. form inputs found. the way can read them set method of form "request" isn't real form method. takes of inputs , sends them args in url. horrible. here's sample page canvas app form i'm using try debug (leaving out authentication stuff): <form enctype="application/x-www-form-urlencoded" method="post" target="_top" id="my_form" action=...
Read more

c# - Getting control value when switching a view as part of a multiview -

-
i have following code in aspx page: <asp:button id="display_button" runat="server" text="display" onclick="button1_click" /> &nbsp; <asp:button id="edit_button" runat="server" text="edit" onclick="button2_click" /> &nbsp; <asp:button id="save_button" runat="server" text="save" onclick="button3_click" visible="false" /> &nbsp; <asp:multiview id="multiview1" runat="server" activeviewindex="0"> <asp:view id="view1" runat="server"> <asp:formview id="view_program" runat="server"> <itemtemplate> <%# eval("status").tostring().trim() %> </itemtemplate> </asp:formview> </asp:view> <asp:view id="view2" runat="server"> ...
Read more