Solved

Problem using OnTime function within Excel 2007 macro

Posted on 2010-09-08
31
478 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now