web query will not load page

[semajjames]

Hi I'm using Excel 2007 to try and web query the page shown below ..

is there some reason why it will not let me import the page ?

Instead of letting me import the page I am forced to download it.

https://poloniex.com/public?command=returnTicker

thanks for any advice

[AliGW]

Please update your user profile version of Office from 7 to Excel 2007.

Instructions on attaching your workbook are at the top of the page.

[semajjames]

Thanks

I have also attached screen shots of where I have been trying to get this to work.

[Haluk]

Your URL points to a JSon file which is just like an ordinary text file, that's why you can not import it as you think.

But, you can import the related datas stored in the JSon file using VBA in several ways...... One of them can be seen in the attached workbook.

[semajjames]

Thank you so much this is perfect, I will now look into adjusting that macro so that it will loop and update rather than start from the button and stop.

[semajjames]

Ok so I have added a loop on a timer and made the code run on start but in doing so it now enters the data in the next column's along instead of removing and replacing ?

[Haluk]

You need to use a proper "Application.OnTime" method.....

Check the attached file, hitting the "Start" button, data will be refreshed with 1 minutes time interval. You can hit the "Stop" button to cancel refreshing process whenever you wish.


.

[semajjames]

That is great, There is just one thing: I need it to work on just one sheet ,, at this time it enters the data into any sheet that is open.

I tried this

Please Login or Register  to view this content.

and this also

Please Login or Register  to view this content.

This I have not tried because I do not know which bits to replace with your code.

Please Login or Register  to view this content.

thanks again

[AliGW]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Haluk]

@semajjames;

Try the attached file...... data will be displayed on Sheet2 of the workbook which houses the VBA code.

.

[semajjames]

perfect thanks

[semajjames]

So for all this time this worked tho now the macro has stopped working from what I can see that the web page is still the same but I should think that it is only the web page that could have changed.

I have changed from excel 2007 to 2010

anyway the macro code is well beyond me could anyone see why ?

[Marc L]

Hi,

just declare the variable r as Long rather than Byte !

[semajjames]

thanks that worked,

so what changed to make that adjustment needed ?

excel version ?

Oh the imported document got longer maybe ?

[Marc L]

First as Excel can contain more than one million rows it was a bad idea to use a variable limited to 255 ‼

And as you can see yourself just clicking on the original webpage link there are far more than 255 elements !

And last but not least, the weird in this VBA procedure is using a random number to download those elements
as you can see just comparing the original URL and the one used within the VBA procedure …