Refreshing Website Data

Hi, I'm using MS Excel 2010. I have a spreadsheet that is linked to a website and which imports data from the website when I open the spreadsheet. -- All good so far...

My problem is that the fastest data refresh rate is 1 minute. Although that seems quick, 1 minute is quite a long time for some internet data. I need to be able to code a way into the spreadsheets VBA module that will update the data at a custom rate.

I tried to use the "RefreshAll" command, but that really doesn't work. So, my question is - how do I programmatically update my website data inside my spreadsheet?

The data that I'm importing is financial data, so a simple example would be a stock quote. In that example, my question would be - how do I "programmatically" update the data from the webpage via the spreadsheet?

Thank you very much of your help.

Fulano
Mr_FulanoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Well, I would give a question back to you: why is so relevant that you receive the refreshed under 1 minute?
Why I'm asking this? Because if your need is for something more realtime, maybe an Excel front-end is not what you need.
0
Mr_FulanoAuthor Commented:
Walter, I would prefer to use an Excel front end, because the spreadsheet already has A LOT of other functionality to it and the updates is merely a piece of a bigger Dashboard. So, rebuilding the front end is not something I really want to do.

I can deal with updates every 15 seconds or even 30 second, but 1 minute is a bit to go without updated data.

Any suggestions on my question?

Thanks,
Fulano
0
FarWestCommented:
you can make your refresh process as a cscript task that runs a vbs file, which will opens your excel file and makes update,
you can add this task to windows task scheduler with proper interval
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

James ElliottManaging DirectorCommented:
^^ not sure vbs is necessary. You could code the update on the workbook_open event and add a command line switch to open excel with the target file.
0
Mr_FulanoAuthor Commented:
Gentlemen, thank you for your comments, but opening the file and have it initially update is not the problem. My problem is that I cannot programmatically change the website data refresh interval. The default process only allows for a minimum of 1 minute refreshes. I would like to have the ability to put in the number of seconds that I want the spreadsheet to poll the website for a refresh.

Please note that an example or a link to an example of your suggestion will be required for credit on this question.

Thank you,
Fulano
0
FarWestCommented:
we did not talk about initially refresh, it is all about refresh, in task scheduler (either vbs, or file open) you can set interval,
now if you want this interval to be less than 1 minute and live while the user is seeing the file, and if your site is auto refreshing and it self gives you live data without making another request, then think about possibilities, otherwise making subsequent requests is not recommended to be for less than 1 minute, and you could notify user that you have one minute interval,
and for me submitting a sample excel file with code is required to go on in responding to this question

Regards
0
Mr_FulanoAuthor Commented:
FarWest, my question is clear... If you do not have a solution, then no need to provide any further comments on this matter. -- Thank you for your participation.
0
Mr_FulanoAuthor Commented:
I've requested that this question be deleted for the following reason:

I would like to close this question. None of the respondents have provided solution that solve my specific question. I will find a solution on my own.
0
Walter RitzelSenior Software EngineerCommented:
Sorry, I have to disagree. We are still exploring what you need...
0
Walter RitzelSenior Software EngineerCommented:
I think this is what you want. Look at the macros.
This is prepared to refresh every 20 seconds. And the connection was with a text file, and one single connection.

I dont know how many connections do you have, but if they go back to a remote database and is more than one query to get data refresh, I really think that you are going to introduce a serious slowness on the interface, besides create interruptions on the responsiveness of the tool. But this is not what you want to know, right? You just want the way to do it.
Test.xlsm
REQUIRED-COURSE-MAP.txt
0
Mr_FulanoAuthor Commented:
Walter, thank you. I tried to open the file and was now able to do so. It keeps telling me "The file is corrupt." Could you simply post the code in the module?

I was however able to open the Map TXT file.
0
Walter RitzelSenior Software EngineerCommented:
Here it goes.
Dim TimeToRun

Sub ScheduleRefresh()
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime TimeToRun, "ThisWorkbook.RefreshData"
End Sub

Private Sub Workbook_Open()
    Call ScheduleRefresh
End Sub

Sub RefreshData()
    ThisWorkbook.RefreshAll
    Call ScheduleRefresh
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_FulanoAuthor Commented:
Thank you Walter. I'm going to test... much appreciated.
0
Mr_FulanoAuthor Commented:
Walter, I've fond another approach to a solution, but I'm going to award you the points for trying to help. BTW, I have another question on the forum, so if you want to give that one a try as well, I'd appreciate it the help.

Fulano
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.