+ Reply to Thread
Results 1 to 11 of 11

Web Query - Website requires data input before it shows desired data

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    Istnabul
    MS-Off Ver
    Office 2014
    Posts
    6

    Question Web Query - Website requires data input before it shows desired data

    Hey there,

    So I am trying to extract data using a basic web query to dump it into a garbage tab to refer to the data in other tabs. However, the websites I try to query demand me to specify a start and end date or to press a button. The URL does not change at all. This challenges me big time as I fear that the solution requires knowledge about non excel related coding languages.


    https://flowdata.fluent.com/sdp/Page...formation.aspx
    On this website I need to press the load data button. Afterwards I can do everything myself using unsophisticated and dirty methods. If just I'd be able to teach excel how to press the button

    http://www.milcador.ren.pt/Olia/Pagi...ionalData.aspx
    On this website I need to change the month and then press the arrow. Same story as above. Once I overcome these issues I'll use simple methods to digest the data.

    What I managed to do with my abilities:
    - standard web query
    - open and copy files from online xls file via VBA automatically
    - manipulate a dynamic URL including start and end date via VBA automatically

    Thank you in advance. All assistance is much appreciated. And don't shy away from posting dirty fixes, I'll probably understand them quicker anyway
    Last edited by Anon2266; 07-31-2014 at 06:36 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this demonstration !


    Hi,

    you must learn HTML basics and learn how to use your web browser inner tools !

    Paste to a worksheet module this demonstration :
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-28-2014
    Location
    Istnabul
    MS-Off Ver
    Office 2014
    Posts
    6

    Re: Web Query - Website requires data input before it shows desired data

    Hi Marc,
    Thank you for your response. However, I am unable to get your example to work. The debugger highlights this bit: "[B1].CurrentRegion.Clear: Me.Paste [B1]"
    As I am not that advanced in VBA I am having trouble fixing it. I'd be glad for any input or assistance on this tough nut

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Web Query - Website requires data input before it shows desired data


    Works like a charm on my side with different Excel versions ‼
    Last edited by Marc L; 07-29-2014 at 08:42 AM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Web Query - Website requires data input before it shows desired data


    I see : remove code and read again post #2 but this time well read, in particular line before code !

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool See this demo ! (safer way to reach elements …)


    Paste this code in a worksheet module (not a standard one !) :
    Please Login or Register  to view this content.
    Post #2 code will fail in case of element order change in the webpage !

    This alternative code reaches elements by name, easier and safer way …
    Last edited by Marc L; 07-30-2014 at 12:00 PM.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Web Query - Website requires data input before it shows desired data


    Sorry, I edit my previous post 'cause I found out issue with safer way …

    Enjoy it !
    Last edited by Marc L; 07-30-2014 at 09:41 AM.

  8. #8
    Registered User
    Join Date
    07-28-2014
    Location
    Istnabul
    MS-Off Ver
    Office 2014
    Posts
    6

    Re: Web Query - Website requires data input before it shows desired data

    Thank you ver much for your assistance Marc!
    You are great!

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Re: Web Query - Website requires data input before it shows desired data


    Thanks !

    But safer code needs to be moderate : remove "(0)" in the line .all(ELT & "6")(0).Value = "03"
    Not a big concern but the correct way (all method doesn't need it instead of getElementsByName method),
    post #6 code updated … Use getElementsByName method when all method doesn't find an element …

    To forgive me, this is a tip :
    if you need May month, you need to pass the string value "05" like that : .all(ELT & "6").Value = "05"

    But if you start from a numeric value you can use .all(ELT & "6").selectedIndex = 4

    Edit : Index starts from zero so May is 5 -1 = 4 …
    Last edited by Marc L; 07-30-2014 at 12:27 PM. Reason: correction …

  10. #10
    Registered User
    Join Date
    07-28-2014
    Location
    Istnabul
    MS-Off Ver
    Office 2014
    Posts
    6

    Re: Web Query - Website requires data input before it shows desired data

    I linked the value input for the month field to the current date now.


    Dim mon As String
    ...
    mon = Sheet2.Cells(2, 2).Value
    ...
    .all(ELT & "6").Value = mon

    The month cell (Sheet2.Cells(2, 2)) displays the month in the following format: 01,02,03,04,05,06,07,08,09,10,11,12. So it suits the format of the web form.

    The macro works reliably now. Thank you! Hope I didn'y stress you out too much

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Web Query - Website requires data input before it shows desired data


    Glad it helps you !

    No stress and I found out why sometimes a issue came with the pilot IE way …

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-03-2013, 02:02 AM
  2. How could i draw this website table in (cant get the web query to see the data in it)
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2013, 12:07 PM
  3. Data Query - How to Change Drop down Selection from Website
    By mattjac1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2013, 04:41 PM
  4. Replies: 0
    Last Post: 07-07-2009, 02:40 AM
  5. [SOLVED] Excel Web Query for Java Applet driven data website
    By mingInv in forum Excel General
    Replies: 0
    Last Post: 08-20-2006, 10:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1