Import data from website different websites using change in cell information?

[VC1]

Is it possible to change the website of which my excel is getting its data from within a cell?

E.g.
I'm getting static data on employee number 1515 from intranet.com/1515

But instead of changing the website manually everytime, I need to have a cell where i can just write 1516 (employee number) and now the data will change.

- Is this 'feature' possible? If anyone knows of a youtube where this is shared I would very much appreciate it.

[oeldere]

a1 = intranet.com/
b1 = 1515

c1 = a1&b1

If you drag the formula down and
b2 = 1516

c2 => result => intranet.com/1516

[CK76]

If you are using Get & Transform -> Get Data -> From Web. To query data...

You set up 2 named cells. 1 to hold static path and another for employee number (or if path never changes, you can hard code that portion in the query).

I'd then set up list of available/valid employee number as validation list for the 2nd named cell.

In Query editor, use something like below to set up variable(s).

Please Login or Register  to view this content.

Then use Text.From() on the value from employee number and concatenate it with first part. Using it as argument in the query.

If you are using WEBSERVICE() function to obtain data... you can do much the same set up, but just need to pass the employee number to formula, rather than query.

[VC1]

I'm using the Data -> From Web

But I can't do it the way your are explaining - I just made the example with numbers, but in reality it is between 3-5 initials and a lot of employees

[CK76]

Can't you just prepare table/list of valid entries? That info must be stored somewhere for it to work on Web Query.

At any rate, you can just replace data validation with manual typing. That's not going to change the method used.

[VC1]

No I can't just make a list of entries as we are getting a huge amount of employees entrying and leaving everyday.

The dynamic cell has to be without a list and just for whatever I want to write.

[CK76]

So just do that. As I stated in the previous post. Nothing else changes. Logic for passing on the named range / value to query remains same.

[VC1]

Okay, then I really don't understand what you want me to do.

I've read the post several times

[CK76]

I'd recommend uploading sample workbook. Showing your set up, and what you wish to accomplish.

I can only give you general advise without knowing your intension. I am not a mind reader after all

[VC1]

I don't have any workbook regarding this feature.

For now it is only a feature I would like to see if can be done and then maybe implement it in a real worksheet.

So for now I have only tested Data -> 'Webside' -> Table 0 and Table 1 -> Load to 'Existing worksheet'


But it is basically the same thing I want to implement, just where I use e.g. intranet.com/ as a base formel and kan type anything else in e.g. B2 and it will merge it to the link and import data from the website (table 0 and table 1)

[CK76]

So, just hard code the "intranet.com/" part in the query itself. Then pass on B2 as named range to query and use it to construct your query.

See link where I detailed concept. Post#5
https://chandoo.org/forum/threads/us...s-notes.35658/

And example of usage in thread below. Post#7
This was specifically used for native SQL query, but exactly same principle applies to web query.
https://www.excelforum.com/excel-pro...cel-range.html

[VC1]

Sorry but I'm a complete Excel novice and learning/copying everything from youtube and trying to change specific parts.

What you just posted is russian for me and I don't even get where to put the code. VBA is not accepting it and I am not able to oben Edit Query (faded tab)

[CK76]

No VBA is used.

Before you try to make it dynamic. I'd recommend you query your data and make sure that you have the data structure you want.

Once that's confirmed, add new worksheet in the same workbook and add your named range (or it can be done in same sheet if needed).

Then Data -> "Queries & Connections". Double click on the query. It will take you to query editor.

In the ribbon tool of query editor, View -> Advanced editor. That's where you'd insert line to define your variable and retrieve data from named range.

Google search for "powerquery named range as parameter" and you should find plenty of tutorials and videos.

[VC1]

I'm trying and trying - Can't get this to work.

Most be a different way like a VBA