• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Refresh data from Web at specific times of day

tilltheend

New Member
Hi,
I have multiple data in different sheets of a file. I fetch data into them from website.
As of now, I have set refresh data every few minutes, for each of sheets.
But, I want to refresh data at specific times of day. e.g. at 10am, then 11am, then 12pm.
Can somebody please guide in this.
Thanks
 

Chihiro

Excel Ninja
Give us bit more detail.

What method are you using to query data from Web?

Typical method would involve using Windows Task Scheduler and VBScript. But hard to help you without knowing your set up.
 

tilltheend

New Member
Give us bit more detail.

What method are you using to query data from Web?

Typical method would involve using Windows Task Scheduler and VBScript. But hard to help you without knowing your set up.
Hi, Thanks for revert. Here it is...
I have Excel 2013. In 1 excel file, I have 5 sheets. All sheets get data from 1 website.
I use option 'Data -> From Web'.
I need get this data from website periodically. So for 1 sheet, I have kept 'Data range properties -> Refresh every 60minutes'; for 2nd sheet, Refresh 90min; for 3rd sheet Refresh 120min and so on.

Now, instead of this setup, I want sheet1 to be refreshed at 10am; sheet2 at 11am; sheet3 at 12pm and so on.

Please guide on this.
 

Chihiro

Excel Ninja
Very little detail here to actually help you... hence lack of response.

General concept.
Have VBA macro set up to refresh query, for each of the query. Create VBS files to run VBA Macro. Then use Task scheduler to run specific VBS on schedule.

Sample VBScript to open check if workbook is already open or not, and open if it isn't. Then run VBA sub on that workbook.
Code:
ExcelFileName = "Book1.xlsx"
myFolder = "C:\Test\"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
If Err Then
  If Err.Number = 429 Then
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set wb = xl.Workbooks.Open(myFolder & ExcelFileName)
  Else
    WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
  End If
  WScript.Quit 1
End If
On Error Goto 0

Set wb = Nothing
For Each obj In xl.Workbooks
  If obj.Name = ExcelFileName Then
    Set wb = obj
    Exit For
  End If
Next
If wb Is Nothing Then
  Set wb = xl.Workbooks.Open(myFolder & ExcelFileName)
  WScript.Quit 1
End If

xlApp.Run(ExcelFileName&"!"&"MacroName") 'VBA sub that refreshes specific query
 

tilltheend

New Member
Thanks for reply.

Wow, whatever you mentioned went 10 feet over my head.... I have never written VBA/Macro.

I have attached file in which data is being fetched from Web. Sheet names mention Time to Refresh.

Can you please look into it and help.
Thanks a lot.

Very little detail here to actually help you... hence lack of response.

General concept.
Have VBA macro set up to refresh query, for each of the query. Create VBS files to run VBA Macro. Then use Task scheduler to run specific VBS on schedule.

Sample VBScript to open check if workbook is already open or not, and open if it isn't. Then run VBA sub on that workbook.
Code:
ExcelFileName = "Book1.xlsx"
myFolder = "C:\Test\"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
If Err Then
  If Err.Number = 429 Then
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set wb = xl.Workbooks.Open(myFolder & ExcelFileName)
  Else
    WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
  End If
  WScript.Quit 1
End If
On Error Goto 0

Set wb = Nothing
For Each obj In xl.Workbooks
  If obj.Name = ExcelFileName Then
    Set wb = obj
    Exit For
  End If
Next
If wb Is Nothing Then
  Set wb = xl.Workbooks.Open(myFolder & ExcelFileName)
  WScript.Quit 1
End If

xlApp.Run(ExcelFileName&"!"&"MacroName") 'VBA sub that refreshes specific query
 

Attachments

Top