VBA API Guidance

[brandonrlz]

So I've been searching on the site for the last 20-30 minutes, and I think I'm just not looking for the right value....so I need some help.

What I'm wanting to know is....can VBA rewrite an API URL based on certain values selected in either text box, drop down, or even check marks?

My API URL layout is defined as such:

http://domainExample/webApp/api/1.0/

1. Data type
2. Report Name
3. Report Format
4. Production ID
5. Username

Example: domainExample/webApp/api/1.0/toplinetrend-data/topline.ftl?productionId=11223344&username=user.name@domain.com&format=CSV&userAuthKey=b111402b-7cb4-45ce-94de-a997fbf52bf8

This is just more brainstorming tactic so if there is a different way to approach this - I'm all ears!

 

[sijpie]

Not exactlhy sure what you are trying to achieve. Are you confusing API with hyperlink? (API is generally used for a function library, for instance if you want to use the underlying Windows functions you need to link toe the Windows API library)

If you mean can VBA change hyperlinks on the fly, the answer is yes. And you can get the username as well

sUser = Environ("UserName")

see for instance:
http://www.mrexcel.com/forum/excel-questions/715648-relative-cell-reference-w-hyperlink-formula.html

 

[brandonrlz]

Thanks for posting that, that helps! Sorry for the confusion, let me clarify a bit.

I have a reporting API that I can access which spits out XML data of all the tables and their respected data. What I'm doing is using Excel to pull in the data using a data connection from web and then parsing it the way that I want using pivot tables. As I type this and think about it more, your example is great; however, my problem is - I can't modify the data connection after it's connected. Can VBA modify data connection on the fly so that I can use the "hyperlink" formula like you mentioned?

I want to be able to change or modify the URL based on what can be selected, and using that hyperlink formula will work, just not sure about VBA and the data connection piece.

 

[sijpie]

Ah, not my strong point - data connection. I guess it can be set up on the fly, but I guess someone else can answer that better. Suggest you do a search here on data connection, look for some recent ones with a good answer, and send the person providing the knowledge a personal message to look at this thread.