• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 36
  • Last Modified:

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
0
Mr_Fulano
Asked:
Mr_Fulano
  • 7
  • 4
  • 2
  • +1
1 Solution
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now