How to refresh a read only excel workbook

How can I refresh a network excel workbook that is in constant use?

I have a workbook that in use all day, this workbook is recording data every 5 to 15 mins, and I need to know if there is a way to; open it and so how refresh it. If I build a macro to open it and close it, as soon as I close the workbook the opening macro can be activated because the workbook is close, so is there a way to use may be a vbscript or any other vb code to use it in Excel pulldown menu and as I open the workbook I can run a code to open and close the workbook so I can see the most update data?

Have researched for awhile with no luck, but I know someone will come with a cleaver way to do this.

Even if the code is outside Excel will be helpful, something I can put in my desktop and trigger it, off course if it can be put in a button a menu will be better.

Thanks in advance for the replays.
LVL 1
Exl04Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Do you want to open the workbook periodically and then close it as soon as the work for that time period is done?

Kevin
Bert01Commented:
I'm not sure what you are asking.  Why does it need to close?  If the open workbook is saved (say, in VBA with the .Save method every 15 minutes), then the file has current data.
Bert
Exl04Author Commented:
Zorvek, thanks for answering my post.
What I need if possible is to when I need to see if there is any updates click a object to refresh the workbook, but if is not possible just refreshed every time set.
The time I will (and if have the ability to do share the idea with co-workers) need to open and closed to refresh is determined by the need, so there is no specif time frame. I build  this workbook, and is use to track a process. I will need to periodical see what is going on with the new macros updated every week (until is completely finish) and also the whole manufacturing team keeps track of the data.
Right now I got the workbook open most of the day, that is the reason I need a way to refresh, and I know manufacturing is constantly checking this workbook and I'm afraid that some of them if let it open may get old data inadvertently.


Thanks

JB
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

zorvek (Kevin Jones)ConsultantCommented:
Have you considered just saving the workbook when you make some changes? You can save without closing it. Or am I still missing something?

Kevin
Exl04Author Commented:
Bert01
Thanks for answering
When the workbook is open by anybody else but the user, is read only is not a shared workbook. I don't have a .Save in the code because the workbook is 3megs big, the cycle time for the person inputting data is 5 minutes, he needs to input data for like 3 minutes and when walk away from the computer to inspect the next product, he clicks save manually when walking away fro the computer to inspect the next product. If I code to save every entry (he makes 10 to 15 entries per product) he will be there the whole cycle time. Hope this help understand the need.
zorvek (Kevin Jones)ConsultantCommented:
There is no way you will be able to propagate an entry without saving. The way you are doing it now is about as good as it will get.

As an alternative, you can push the data into an Access or SQL Server database. Then can have virtually immediate refreshing globally without suffering a performance problem. But that will require a bit of a rewrite of your workbook and the addition of a database.

Kevin
Exl04Author Commented:
Lets briefly explain again the process.
A workbook not shared is open by a user in the morning and user have this workbook open all day inputting data, me and the the rest need to keep track of what is input. If I open the workbook at 8:00 am and keep it open until 9:30 am, the data from 8 am will be still showing at 9:30 unless I close the workbook and opening it again to refresh the data. I need a way to open workbook and just run some code to refresh (access is out of the way in this project)

Why do I need to keep it open that long, because I need to keep track of any macro not functioning properly, the rest even that the time is showing in every sheet may get a false reading of data if not refresh.

Thanks again guys for the input
Exl04Author Commented:
Sorry for the lagged posting, every time I post an answer is there that I didnt read before posting
Exl04Author Commented:
Is there any way to code a vbcode and run it from the pull down menu? a simple close and open workbook code.
zorvek (Kevin Jones)ConsultantCommented:
Here is a solution. Add this code to the ThisWorkbook code module. It will "refresh" every 10 minutes by closing and reopening the workbook:

Private Sub Private Sub Workbook_Open()()

   Application.OnTime Now() + TimeSerial(0, 10, 0), "ThisWorkbook.Refresh"

End Sub

Public Sub Refresh()

   Application.OnTime Now(), "ThisWorkbook.Launch"
   ThisWorkbook.Close False

End Sub

Public Sub Launch()

End Sub

Kevin
zorvek (Kevin Jones)ConsultantCommented:
Or, if you want to do it on command, place this code in any general code module and add a command button to run Refresh.

Public Sub Refresh()

   Application.OnTime Now(), "Launch"
   ThisWorkbook.Close False

End Sub

Public Sub Launch()

End Sub

Kevin
Exl04Author Commented:
Thanks Keving
Ok on the first code posted I don't know if I follow you; if I put this code on the workbook it will refresh the workbook, but not the workbook opened read only right? The workbook in use it is save manually every 5-10 minutes by the user when walking way from the computer to inspect the next product. Please clarify this fro me if I followed right.
On the second code if I put this second code in the workbook and after the user opens it in the morning and I open the workbook read only and press this button, would it refresh the data?
zorvek (Kevin Jones)ConsultantCommented:
All the code does is close and re-open the workbook. The first set of code does this using a timer. The second set of code is designed to be invoked by you clicking on a button or running a macro.

Kevin
Exl04Author Commented:
Kevin with your code like this (Private Sub Private Sub Workbook_Open()()) I removed the extras,
I don't know if I'm getting it right, right now I'm at home and I send the workbook to a friend, he opened it putting the workbook in the public drive, he opened again and with my VPN I opened  the workbook and  but can make it work even with the button. Can you put your code in a sample workbook? I may not getting it in the right place.

Thanks


JB
zorvek (Kevin Jones)ConsultantCommented:
Ouch! Somehow I buggered that post. I also made a change to stop the timer when the workbook is closed. I attached a sample that refreshes every 10 seconds.

Private mTimer As Date

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   Application.OnTime mTimer, "ThisWorkbook.Refresh", Schedule:=False

End Sub

Private Sub Workbook_Open()

   mTimer = Now() + TimeSerial(0, 10, 0)
   Application.OnTime mTimer, "ThisWorkbook.Refresh"

End Sub

Public Sub Refresh()

   Application.OnTime Now(), "ThisWorkbook.Launch"
   ThisWorkbook.Close False

End Sub

Public Sub Launch()

End Sub

Kevin
Refreshing-Workbook.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exl04Author Commented:
Thanks for sharing the brilliant knowledge, and let me learn from you.
Exl04Author Commented:
Kevin how does the button will work with this code. can you modify it to work at command with a button.


Thanks again


JB
zorvek (Kevin Jones)ConsultantCommented:
Use this code for that:

Public Sub Refresh()

   Application.OnTime Now(), "Launch"
   ThisWorkbook.Close False

End Sub

Public Sub Launch()

End Sub

To add a command button to a worksheet that runs a macro, right-click on the menu bar and select Forms. Click on the gray rectangle icon. With the mouse, click where the upper left corner of the command button is to be placed and drag the mouse down and to the right to create the shape of the command button. A dialog is displayed asking for the macro to run when the command button is clicked. Select the desired macro and click the OK command button.

Kevin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.