Share via

How to extract data from web page using dynamic URL?

Anonymous
2019-01-17T07:19:45+00:00

I'm trying to grab data automatically from data base to automate copy/paste process. I've managed to create URL in the cell, but it seems I can only enter text in the URL field, no reference to that cell.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2019-01-17T15:12:03+00:00

    Hi Andrei

    Passing dynamic value/parameter to a Query has been answered n times here & there... Assuming initial URL is http://ABCD/DEF/IJK

    1. Follow Justin's instructions to build the initial query and enter the above URL (http://ABCD/DEF/IJK)

    if you look at the M code in the Power Query Advanced Editor, somewhere at the beginning you will see something like Web.Contents("http://ABCD/DEF/IJK") - hard coded URL between quotes 2. Load the result into your XL workbook 3. Assume your concatenated URL - CONCATENATE("//FirstPartOfURL";A1;"SecondPartOfURL") - is in A1, name cell A1 i.e. UserUrl (with a workbook scope) 4. Edit your query and add the following line of code - in the Power Query Advanced editor - before the line that contains Web.Contents("http://ABCD/DEF/IJK"):

    myDynamicUrl = Table.FirstValue(Excel.CurrentWorkbook(){[Name="UserUrl"]}[Content]), 5. Update Web.Contents("http://ABCD/DEF/IJK") with Web.Contents(myDynamicUrl)

    Done. Hope this helps

    2 people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-01-18T06:08:52+00:00

    Yes, surely. I have a column of numbers, that I want to feed into query and get results from similar intranet HTML pages. Please see below in column A I have a series of numbers I want to feed into URLs and queries.

    So far, as I understand, I can do only one, correct?

    0 comments
  2. Lz._ 38,106 Reputation points Volunteer Moderator
    2019-01-18T07:48:12+00:00

    Hi Andrei

    No picture in your last reply :-( Anyway I got the overall idea and correct the query, as it is now, can only handle one. However, there might be a way to do what you want but that's a different story as iterating with Power Query (Get & Transform) is not obvious...

    Suggestion:

    1. Close/Mark as answer (can help others with similar need) this thread if what I suggested above works
    2. Open a thread on the Power Query Technet forum: provide as much details as possible (inc. URL public) + explain if the expected output should combine the result of each URL. Ideally upload snapshot of the expected result
    0 comments
  3. Anonymous
    2019-01-18T08:15:47+00:00

    Hi Lz,

    Have no idea why picture doesn't upload =/.

    But this already will save enormous amount of my working time.

    And yes, I will do as advised and go to forum.

    0 comments
  4. Lz._ 38,106 Reputation points Volunteer Moderator
    2019-01-18T08:43:35+00:00

    Hi Lz,

    Have no idea why picture doesn't upload =/.

    Just in case: you can't copy/paste picture on this forum => Save your pic/snapshot as .jpg/.pgn; When replying upload it using the "Insert image" icon of the toolbar

    But this already will save enormous amount of my working time.

    Good :-)

    And yes, I will do as advised and go to forum.

    Best option of advanced Power Query issues. There are a few people there with really advanced skills & experience. Be patient though…

    0 comments