How to Get web data based on link in excel cell?-CodePudding

I'd like to create an Excel sheet, where in one column there is a link to a website like this:

Link in column A where there is a MAC add in that url that changes from line to line rest of link is geraric. and it takes the info in the 2 lines marked with arrows and put into another cell. This should be possible for multiple links in different rows from websites with the same structure.

How can I make the web query to be based on the link in the adjacent cell?

CodePudding user response：

You can use Power Query to read the contents of a cell.

Then in Power Query, you can return the web contents of the URL like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Get Web Contents" = Xml.Tables(Web.Contents(Table.FirstValue(Source))),

in
    #"Get Web Contents"

You will need to be mindful of the data your query returns, i.e. is it a single value, table, or otherwise?

Here is documentation on how to to load the results from Power Query back to your excel worksheet: https://support.microsoft.com/en-us/office/create-load-or-edit-a-query-in-excel-power-query-ca69e0f0-3db1-4493-900c-6279bef08df4

CodePudding user response：

I sort of found out. just made a simple WEBSERVICE(A2) to the URL and it returns a string with all the data, now i just need to extract only the data i need.