Refresh cells on spreadsheet: Microsoft Excel 2003, refresh cells, excel

by: homer99Posted on 2009-06-30 at 18:54:46ID: 24751098

made 500 points

by: ssaqibhPosted on 2009-06-30 at 23:31:43ID: 24751899

I suggest that you use an excel viewer like this

http://www.freedownloadscenter.com/Business/Document_Management_Tools/Excel_File_viewer.html

Although I have not used it, I think it should be possible to program windows scheduler to open the file every n seconds. I am not sure if the opened file can be closed automatically but I certainly think it is worth a try.

Saqib

by: homer99Posted on 2009-07-13 at 08:48:12ID: 24840859

Any other ideas?

by: bhwithunPosted on 2009-07-21 at 19:25:17ID: 24911087

I have created an excel AddIn that will execute an operation on whatever interval you desire.

At the moment all it does is pop up an annoying little box, but that can be changed to load and/or refresh an open worksheet.

This is just a concept. If you like the idea you can tailor it to fit your exact need, or with a little more guidance I can tailor it for you.

It is attached. This is an XLA file and it belongs in your AddIns folder

(C:\Documents and Settings\<YOURUSERID>\Application Data\Microsoft\AddIns\)

I've created a custom toolbar with controls for starting and stopping the timer. A little green clock and a little red clock. They're embedded in the XLA (if I did that correctly) let me know if you need assistance getting this toolbar or AddIn installed.

This timer can be altered to start automatically when Excel opens, and can be adjusted to ONLY refresh the workbook if the user currently has it open (rather than ALWAYS opening the workbook)

You'll also notice a security warning when it runs, since this is a macro. I can explain how you can sign this macro yourself so that Excel will stop warning you about it.

How does the concept suit you?

Brian Withun

NOTE:::
 
This is NOT a .txt file.  I have had to name it as .txt so that I could upload it.  Experts-Exchange does not support uploading .XLA files (for some reason)
 
You will need to rename this file from Timer.txt to Timer.xla, and then place it in your AddIns folder, then activate the AddIn.

1:
2:
3:
4:
5:

Select all Open in new window

Timer.txt
- 25 KB
This is an XLA file, not a TXT file!!!!

by: homer99Posted on 2009-07-22 at 09:43:37ID: 24917056

Thank you, I will give this a try and let you know how it works.

by: homer99Posted on 2009-07-22 at 10:04:01ID: 24917252

I was able to import the Add-in and it has a green and red button which start and stop the timer. Now how can I make it refresh the notebook to update any changes that may have been made since it is a network file?

by: bhwithunPosted on 2009-07-22 at 16:52:00ID: 24920777

I've attached another .xla (named as a .TXT)

You'll have to look over to code to customize it. I've had to guess as the specific values you'll need.

refresh time = 10 seconds
file to load = "Sample.xls"

I don't have a network available to test this on, to make sure "reloading" an already-open file actually causes Excel to reload the file. Please let me know if it doesn't work for you.

I've littered the code with display boxes, so you can see it operating. Feel free to comment out these msgboxes.

Brian Withun

Timer-2-.txt
- 30 KB
another addin

by: homer99Posted on 2009-07-22 at 17:29:53ID: 24920951

I will be out of the office tomorrow but will try this first thing on Friday. Thank you for your help Brian.

by: homer99Posted on 2009-07-24 at 15:12:27ID: 24939575

Hi Brian, I tried this today and it seems to work except for the refreshing the screen part. The timer works good, the pop up windows come up at set time, but the screen does not refresh the changes made to the file.

by: bhwithunPosted on 2009-07-24 at 18:08:45ID: 24940292

Will the user have made any edits to this file?

Would it be problematic to actually close the file, then re-open it?

the timer event could make a note of which worksheet was active in the workbook, what the selection was, and then close the book, and open it again and restore the sheet/selection.

This would probably bring the worksheet to the foreground, where it may not have been, but I think the procedure could also memorize which workbook was active, and make sure that workbook stays active...

by: homer99Posted on 2009-07-25 at 10:19:30ID: 24942379

The computer I want to run this on will not have a user. It is a computer that is hidden that displays the excel sheet on a monitor in the hallway in the front office. It is meant to display which engineers are in the office, and which are traveling and which jobs they are working on. It can close and reopen every 15 minutes or so, that is fine as long as it is automatic. If you can help me do this, that is definitely a good solution. Thanks.

by: bhwithunPosted on 2009-07-29 at 05:58:42ID: 24969625

Ok, I've re-written the AddIn. The code is below.

Now the timer starts when Excel starts. It pops up a dialog which you can remove from the code if you like.

The timer event does nothing at all, until you open the chosen workbook.
Once the workbook specified by WorkbookPath/WorkbookFile is opened, the AddIn timer will detect it and begin closing the file and immediately re-opening the file every time the event occurs.

If you close the workbook yourself, this reloading mechanism stops. Excel runs as normal until you open the chosen workbook.

BW

Option Explicit
 
Public Const WorkbookFile = "Sample.xls"
Public Const WorkbookPath = "C:\"
 
Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
 
Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long) As Long
 
Public TimerID As Long
Public TimerSeconds As Single
 
Sub StartTimer(Optional silent As Boolean = False)
    TimerSeconds = 10 ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
    If Not silent Then MsgBox "Timer Started", vbInformation + vbOKOnly, "REPEAT TIMER"
End Sub
 
Sub EndTimer(Optional silent As Boolean = False)
    On Error Resume Next
    KillTimer 0&, TimerID
    If Not silent Then MsgBox "Timer Stopped", vbInformation + vbOKOnly, "REPEAT TIMER"
End Sub
 
Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
        ByVal nIDEvent As Long, ByVal dwTimer As Long)
    
    EndTimer silent:=True
        Dim wb As Workbook
        
        For Each wb In Workbooks
            If wb.Name = WorkbookFile Then
                wb.Close
                Workbooks.Open Filename:=WorkbookPath + "\" + WorkbookFile, ReadOnly:=True
                'MsgBox "Reloaded " & WorkbookFile & "!", vbInformation + vbOKOnly
                GoTo FinishedReloading
            Else
                'MsgBox "NO = " & wb.Name, vbInformation + vbOKOnly
            End If
        Next wb
        
FinishedReloading:
    StartTimer silent:=True
    
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:

Select all Open in new window

by: homer99Posted on 2009-07-29 at 11:40:47ID: 24973179

Do I just copy and paste this into the Visual basic editor?

by: homer99Posted on 2009-07-29 at 13:31:08ID: 31598446

Thank you Brian, this solution worked perfectly. Thank you.

Question

Refresh cells on spreadsheet

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Microsoft Excel 2003

refresh cells

excel

Microsoft Excel Spreadsheet Software

MS SharePoint

Software Office Suites

Microsoft Office Suite

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press