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-17T11:29:02+00:00

    Hi Andrei,

    We usually use Power Query to get data from a web page. You can do it by going to Data > Get Data > From Other Sources > From Web.

    You can find more details about connecting to a web page using Power Query in the article below.

    Connect to a web page (Power Query)

    Besides, if you want to use a dynamic URL, can you share more information about what are you trying to achieve?

    Regards,

    Justin

    0 comments
  2. Anonymous
    2019-01-17T13:48:34+00:00

    In this case I want to take the user input in a cell and use it as part of the URL. It will look something like this: CONCATENATE("//FirstPartOfURL";A1;"SecondPartOfURL")

    0 comments
  3. Anonymous
    2019-01-17T19:33:57+00:00

    Hi, 

    Thanks a lot! Can't wait to try it, when i get back to my working PC! If that will work, it will actually reduce a loooooooooot of manual copying and pasting and we would spend our time more on things that require more thought to it. 

    I do have a follow up inquiry though: on the other form I have a series of user inputs like 5-30 ea in 1 column. How to update data accordingly? Will it accept a range?

    0 comments
  4. Lz._ 38,106 Reputation points Volunteer Moderator
    2019-01-17T21:02:18+00:00

    I do have a follow up inquiry though: on the other form I have a series of user inputs like 5-30 ea in 1 column. How to update data accordingly? Will it accept a range?

    I really don't understand this. Could you upload a screenshot of this and try to re-explain what you expect please?

    0 comments