Link to home
Start Free TrialLog in
Avatar of dchew
dchew

asked on

Problem using OnTime function within Excel 2007 macro

I am trying to create a macro for a workbook we have created that will simply refresh the data on  the workbook once it is opened every 5 minutes.  

This is what I have written into the macro:

Sub AutoRefresh()
'
' AutoRefresh Macro
'

'
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:05:00"), "AutoRefresh"
       
End Sub

It is a pretty simple macro, but it isn't working.  
The way I have tested it is that I have the workbook opened on multiple PCs on the network.  I then have one user update the data on her PC.  5 minutes later or more, I check  the other PCs that have the workbook open.  None of the updated data shows up.

Kindly please advise.
Avatar of vincem1099
vincem1099
Flag of United States of America image

What is in the Autorefresh procedure?  Does it work if run manually?
Avatar of dchew
dchew

ASKER

i guess i am confused, because i thought i pasted the contents of the AutoRefresh procedure...
Just a few thoughts on the problem.  The code you have written should do the trick.  However,

1 - The Ontime macro should be in  a general module for it to work. (In VBE Insert > New module)

2 - You can start the macro using the Auto_Open macro (runs when workbook opened)

3 - If you are using MS Query, then you need auto-refresh setting on for the data link to work (right click and press edit query)

4 Try application.calculate to refresh data

Hope this helps

Nick
I see what you are saying.  One  problem is that you are calling the AutoRefresh procedure from your OnTime command which means that this procedure is calling itself.  You should put the code you want called on time in its own subroutine.  Does ActiveWorkbook.RefreshAll do what you want when you run it by itself?
Avatar of dchew

ASKER

there is no data link so no concerns there.  the ActiveWorkbook.RefreshAll does work manually.  

so can any of you explain more specifically what I need to do.  I'm not very good at VB scripting / macros, and aren't sure what is meant by putting the code into it's own subroutine.
Add the following code to your module window and change your autorefresh module to:


Sub AutoRefresh()
'
' AutoRefresh Macro
'

'

    Application.OnTime Now + TimeValue("00:05:00"), "AutoRefresh"
       
End Sub


Sub RunAutorefresh()

ActiveWorkbook.RefreshAll

End Sub

Open in new window

Oops Autorefresh Should look like:

Sub AutoRefresh()
'
' AutoRefresh Macro
'

'
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:05:00"), "RunAutoRefresh"
       
End Sub
Actually the code you had to begin with looks like it would fun every 5 minutes.  I think that if you are not using a data connections the refreshall command is not going to do anything.  It is for refreshing data connections.
Avatar of dchew

ASKER

here is the scenario that we are trying to achieve.

we are a manufacturing company that ships out many parts a day.  certain days some parts become "hotter" than others.  the guys back in shipping need to know which parts are moving up the priority chain without having to walk all the way up front and bother the customer support department.    we have an excel spreadsheet on a network share with proper permissions set up for the relevant users.  this spreadsheet is displayed on two "hotboards"  (40" LCD monitors) by way of two "userless" PCs.  I can control these PCs remotely to open the spreadsheet and basically just leave it open 24/7/365.  The data on those hotboards needs to update periodically.  it isn't an external data source, just a spreadsheet that is being updated by a different department on PCs that aren't connected to those monitors.  

sorry for the verbose explanation, just wanted to get it out there so that you folks could see the true need behind what we are trying to accomplish in case it aids in finding a solution.

thanks already for all the great help!
Avatar of Rory Archibald
Do you actually run the AutoRefresh macro (either manually or via an Open event  or AutoOpen procedure) when you open the workbook?
I think using the built in shared workbook function will perform this task for you rather than creating code:

http://office.microsoft.com/en-us/excel-help/about-shared-workbooks-HP005262294.aspx

Here are the screen shots for what you would select:
ShareWorkbook1.JPG
Then select the following options:
ShareWorkbook.JPG
Don't ever use Shared Workbooks. They are notoriously prone to corruption and weird behaviour.
Here is a vbscript that will close and reopen the workbook so that updates will show up.  You will want to put your correct pathnames for the log file and the Excel workbook.
const acimport = 0
const acspreadsheettypeexcel9 = 8
const ForAppending = 8	
const ForReading = 1
const ForWriting = 2
const dbopendynaset = 2
dim fso
dim oexcel
dim oworkbook
dim log

Set fso = CreateObject("Scripting.FileSystemObject")

'**** Set up Logging to a file
if not fso.FileExists("Enter path to log file here") then
	set log = fso.CreateTextFile("workbookupdate.log")
else
	set log=fso.OpenTextFile("workbookupdate.log",ForAppending)
end if
log.writeline(now & " updateworkbook.vbs Started")

set oExcel = createobject("Excel.Application")
log.writeline(now & " Create Excel Object " & err.number & err.Description)
if err.number <> 0 then
	log.writeline(now & " Error # " & cstr(err.number) & " " & err.Description)
	wscript.quit (err.number)
end if

do
set oWorkbook = oExcel.workbooks.open("workbookupdate.xlsx",,True)
log.writeline(now & " Open Workbook " & err.number & err.Description)
if err.number <> 0 then
	log.writeline(now & " Error # " & cstr(err.number) & " " & err.Description)
	wscript.quit (err.number)
end if
oExcel.visible=True

'Wait for 1 minutes and 2 seconds
wscript.sleep 62000

'Close Workbook
oWorkbook.close
loop

Open in new window

The vbscript needs to be saved using notepad in a file with a .vbs extension and then run by double clicking on it.  You will be able to view the progress in the log file.
Avatar of dchew

ASKER

we originally tried to use the shared workbook options as described above, and we had nothing but trouble.  

weird errors, users not being able to save changes to original file -- forced to make a copy to save changes, etc.  

from everything i've read online in my pursuit of a solution here, folks say to stay far away from that option; as rorya also indicated above.
The vbscript should work with a non shared workbook.
Avatar of dchew

ASKER

ok, so i put that code into a .vbs file, and then will I have to manually execute that file every time?  i would like it to run automatically.  task scheduler perhaps?
Avatar of dchew

ASKER

that code works like a charm!  now i just need to get it to run automatically say every 5 minutes or so.
scheduled tasks won't work as i can only schedule it once a day.
Avatar of dchew

ASKER

or is it already set to run automatically?  
Avatar of dchew

ASKER

ok, i'm an ID10T.
it is opening and closing, and thus updating the data.

is there anyway to do it without having it open and close the workbook?  say, just refreshing the data?
Once the script is started it will run continuously.  You can add it to a scheduled task that runs at system startup.
Avatar of dchew

ASKER

so it seems to be working; sort of.

may need a little tweaking, but aren't sure what or where.

Here is what is happening:

I open the spreadsheet on my workstation in EDIT mode so I can make changes.  

The spreadsheet is open on the HOTBOARD PC in read only mode.  

If I make changes to the spreadsheet and save them, the changes aren't appearing on the HOTBOARD display.  

It doesn't seem like the script is automatically running.

Am I supposed to be opening the spreadsheet on the HOTBOARD PC, then executing the script?  or just executing the script, which will open the spreadsheet...

Also getting a permission denied error for Line 16  Char 2
Line 16 is where the script is trying create the log file perhaps there is a permission problem with the location for the file path you entered in the script for the log file
Avatar of dchew

ASKER

if i execute the script without the workbook open, i get errors.  

if i open the workbook, and then execute the script, it opens up another instance of the workbook.

i don't want that.  am i doing something wrong?  confused...
What are the errors?
I should work without the workbook open
Avatar of dchew

ASKER

these are the errors i'm getting.  weird thing is, they don't come up each time.  
script.1.bmp
script.2.bmp
Does the user logged into the PC have persmission to create a file in the location that is specified for the log file in line 16?
Avatar of dchew

ASKER

yes i am logged in as domain administrator with full control rights over the entire C: drive as well as full control rights for C:\Temp
ASKER CERTIFIED SOLUTION
Avatar of vincem1099
vincem1099
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial