web query is not importing text like usual, instead it is asking me to save a file

I have done web queries in the past. But I am now trying to use a url with an api code instead of a url that goes to the normal website.

When I go to the url in google chrome, I get a page full of script, I believe in JSON format

but when I try to paste this exact same URL into the excel web query browser window, this same result does not display as with a normal browser window, but instead it asks me where to save the file. I can also open the file, but it opens a new excel window and places the data in there. I want the script to just import into excel the same as when I use the url in a normal browser window.

The problem I run into when trying to open the file is that none of these actions are recorded when I start recording a macro. I was hoping to record a macro of me web querying the api url and either get the JSON script directly in the excel window I had open or copy the script from the new excel window and paste it in the window I originally had opened.

If I cannot import the results of the web query with the api url, what is the next alternative to getting the data into exel?

I deleted the api key at the end of the url as I was not sure that this was something that should be posted.

Any help is appreciated

Perhaps have a look at http://officetricks.com/extract-data-from-website-to-excel-vba/

thanks for the response. I checked out the link but did not see anything that was related to my issue.

As a side note, when I record a macro and then run the web query, even if I save the file that it prompts me with to the desktop and then open it, none of these actions are recorded in the VBA code. I have recorded many macros that involve a web query and have never had this occur.

I've never known the macro recorder to not record anything, too much is more the norm, don't know what's with that.

Only time I ever used a sites API to retrieve info, a "page full of script" is exactly what was retrieved.
Using a macro akin to that in the previous link the text could go to a variable or cell to be worked with parsing out the desired info.

Going by the url (which you didn't include here), perhaps the support email on this page is what you need.

Sorry I can't help you.

thanks, Nos. I apologize for my misuse of jargon, as I am sure there is a better way to refer what I am after as a "page full of script". I included a few screen shots below. This first one is importing the homepage of the excel browser, msn:









This is the "page full of script" that I am seeing in a normal browser window and is what I want to appear in excel.
url: https://proapi.whitepages.com/2.2/l...e+65&api_key=e731c72ea4c4430d93f4b4f16bdcec40









And finally, this is what I get when I use the same api url from above when doing the web query. I began recording a macro and put letters a-e in column A to show that the macro recorder is in fact recording. If I were to hit open or save, the recorder would not capture either of these actions. Selecting open results in another instance of excel being opened, which is why I believe it is not captured in the recorder. Hope this helps, and thanks in advance for any input.

There is a limit to what can be displayed in a cell even though the cell value can be much more.

View attachment Retrieve Web Data.xlsm

very nice. thank you.

two questions:

1. is there a way to get the imported data to populate multiple cells like in my first picture I posted above? I have never seen an import like this, and I think it is going to me more difficult to work with the data all in 1 cell.

2. why am I being prompted to save when I am attempting to run the query?

thanks

1a. is there a way to get the imported data to populate multiple cells.... Yes, use VBA to extract desired parts from the text and write them to the specific cells.

1b. like in my first picture I posted above.... I see no correlation between your first picture and the imported text.

2. why am I being prompted to save when I am attempting to run the query?.... other than Excel hasn't been told what to do with it, I have no idea.

I must admit, I have no idea what it is you are trying to achieve.
Hopefully another member can offer assistance.