Solved

Problem using OnTime function within Excel 2007 macro

Posted on 2010-09-08
31
485 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:dchew
  • 16
  • 12
  • 2
  • +1
31 Comments
 
LVL 7

Expert Comment

by:vincem1099
ID: 33631671
What is in the Autorefresh procedure?  Does it work if run manually?
0
 

Author Comment

by:dchew
ID: 33631704
i guess i am confused, because i thought i pasted the contents of the AutoRefresh procedure...
0
 

Expert Comment

by:nick189
ID: 33631715
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Expert Comment

by:vincem1099
ID: 33631719
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?
0
 

Author Comment

by:dchew
ID: 33632126
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.
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33632290
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

0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33632304
Oops Autorefresh Should look like:

Sub AutoRefresh()
'
' AutoRefresh Macro
'

'
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:05:00"), "RunAutoRefresh"
       
End Sub
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33632335
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.
0
 

Author Comment

by:dchew
ID: 33633551
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!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33634611
Do you actually run the AutoRefresh macro (either manually or via an Open event  or AutoOpen procedure) when you open the workbook?
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33638000
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
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33638013
Then select the following options:
ShareWorkbook.JPG
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33638066
Don't ever use Shared Workbooks. They are notoriously prone to corruption and weird behaviour.
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33638500
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

0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33638518
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.
0
 

Author Comment

by:dchew
ID: 33649239
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.
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33649286
The vbscript should work with a non shared workbook.
0
 

Author Comment

by:dchew
ID: 33649360
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?
0
 

Author Comment

by:dchew
ID: 33649710
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.
0
 

Author Comment

by:dchew
ID: 33649781
or is it already set to run automatically?  
0
 

Author Comment

by:dchew
ID: 33649818
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?
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33650046
Once the script is started it will run continuously.  You can add it to a scheduled task that runs at system startup.
0
 

Author Comment

by:dchew
ID: 33650133
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
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33650173
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
0
 

Author Comment

by:dchew
ID: 33650183
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...
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33650196
What are the errors?
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33650225
I should work without the workbook open
0
 

Author Comment

by:dchew
ID: 33650309
these are the errors i'm getting.  weird thing is, they don't come up each time.  
script.1.bmp
script.2.bmp
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 33650323
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?
0
 

Author Comment

by:dchew
ID: 33650464
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
0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 250 total points
ID: 33650548
Try this code with the logging removed
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 oExcel = createobject("Excel.Application")
if err.number <> 0 then
	wscript.quit (err.number)
end if

do
set oWorkbook = oExcel.workbooks.open("workbookupdate.xlsx",,True)

if err.number <> 0 then
	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

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question