How to refresh a read only excel workbook (part 2)

I have a workbook That is open at  the beginning of the day shift and stays open all day by a user inputting data every 5-10 minutes. I wanted to open this workbook and keep it open all day to seeing data update as is entered. Sorvek came with a very cleaver solution;
(attached workbook)
Now I need to when clicking the refresh button get rid off these message boxes (pictures inside workbook sheet1) Can the code be complemented to when clicking the button the workbook just opens?
Right now I'm at home using my VPN and I put the workbook in a network drive, opened it, clicked another instance of Excel and opened the same workbook (read only) and came to the first original and modified a bit clicked save and when I go back to the read only and click the Refresh button IT WORKS!
I know this is as easy as just open and close the workbook and it will be updated, but think about convenience to user, every sheet has a time stamped in the top left corner, so my user can see the last time was open and if is 10-20 minutes old just click a button and there you have it a refreshed workbook!
This has been one of the Access junkies you Excel  people "can do this" things that been driving me nuts, I think Kevin has hit the nail right on the head.
Can some body tell me how can I complement this code to get rid off these message boxes?
Can this be done, specially the "This workbook is lock for editing" msgbox, and why is that the other msgbox comes up when running the code?

Thanks in advance for any input.
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:
To eliminate the "can't be found" error you need to put the code in a general code module. To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

The other part is going to require some shenanigans :-) I'll work on it...

Exl04Author Commented:
Thanks Keniv, take your time

zorvek (Kevin Jones)ConsultantCommented:
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.

Exl04Author Commented:
Thanks Kevin,
I tried the same way I did the posted workbook, but it crashes here when click the refresh button

Set Module = Workbook.VBProject.VBComponents.Add(1)

Can you explain the code a bit, so us the  neophyte pupils can understand what are you doing in you code?
Exl04Author Commented:
This is what I did;

I followed the same steps than the other workbook;
Made a copy in a public network drive, open it, opened another instance of Excel and opened the same workbook in the public drive, it opened read only, made some changes in the first original opened book saved it, and went to the read only clicked the refresh button and  it crashed and highlighted the line of code I posted above.

The other worked ok when followed these steps.
zorvek (Kevin Jones)ConsultantCommented:
Try the attached workbook.

Exl04Author Commented:
Good morning!

Variable not defined
OriginalValue = ActivateVBAccess
zorvek (Kevin Jones)ConsultantCommented:
Sorry about that...last minute change after testing...never a good idea ;-)


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:

When I redacted my question for this post, I was very careful to word it right, I had the trusted feeling that talented people in this place will come with a solution (that I couldn't find in any other researched place for quite awhile).

 Now people can search and find a solution to what they were told couldn't be possible Refresh a READ ONLY workbook.

I just don't understand Microsoft; we can refresh data in a Pivot Table &..but not a workbook? It doesn't make sense, I know I can just closing and open the workbook to refresh it  , but hey! Why we write macros for? make our workbook users life more productive and efficient. If I can save my user a second or two with a macro that wont take more the saved time to run, then we are in the right path but only if this macro is efficient to the user process.

I tested the workbook and it magically worked seamlessly!

Thanks for sharing you talent with us.
Exl04Author Commented:
Thanks for the solution. This one may never get lots of hits because people "know" is not possible.

Kevin if you can just comment you code so I can fully understand what you did. I will really appreciated. I in the learning path, I  can read code but you got me with this one! I just want to learn.
zorvek (Kevin Jones)ConsultantCommented:
As a Microsoft Excel MVP I'm privileged to know some of the folks who work on Excel. As you can probably imagine, Excel has grown into a rather complex application with a whole lot of people working on it. The Excel team has a bug list a mile long and a wish list 10 times longer than that. They occasionally make mistakes but much more often get it right. The bottom line is they only have so many people who can work on it (there are limits to how many people can work on a single project - see and they have to set priorities. I can pretty safely say that this particular "feature" is relatively low on the list. After over 6,000 questions I have answered and a whole lot more I have seen here on Experts Exchange, this is the first time I have seen this situation. Usually, by this point, the user has migrated to a shared database solution.

But, be that as it may, the solution is pretty cool and I learned how to force Excel to give us programmatic access to the VBA project and then restore that setting as if we never came by :-) A clear violation of security protocols but you and I know that we are doing a good thing.

zorvek (Kevin Jones)ConsultantCommented:
I'm not going to explain the code in the AllowVBAccess and RestoreVBAccess routines for two reasons: 1) you will never need to use those techniques and 2) what I am doing is a little frowned upon. Suffice it to say what I am doing is forcing programmatic access to the Excel VBA project. Here is a short description of the option:

Excel provides an option in the registry to allow programmatic access to VBA projects. The option is set on the Trusted Publishers tab of the Security dialog (Tools->Macros->Security). The option is stored in one of two places:


The registry key value of the option is 0 (don't allow access) or 1 (allow access). The key in the Local Machine hive has precedence and, if present, disables the option check box. If both keys are missing then the value of the option is off (don't allow access).

As far as the main routine...

Public Sub ReOpen()

   Dim Workbook As Workbook
   Dim Code As String
   Dim Module As Object
   Dim OriginalValue As Long
   ' Turn screen updating off so there is less flickering
   Application.ScreenUpdating = False
   ' Add a new workbook (will be discarded later)
   Set Workbook = Workbooks.Add
   ' Build code for the new workbook to re-open this workbook after we close it
   Code = "Public Sub ReOpen()"
   Code = Code & vbCrLf & "   Workbooks.Open """ & ThisWorkbook.FullName & """, ReadOnly:=True"
   Code = Code & vbCrLf & "   Application.ScreenUpdating = True"
   Code = Code & vbCrLf & "   ThisWorkbook.Close False"
   Code = Code & vbCrLf & "End Sub"
   ' Force programmatic access to the VBA project
   OriginalValue = AllowVBAccess
   ' Add a new general code module to the new workbook
   Set Module = Workbook.VBProject.VBComponents.Add(1)
   ' Stuff the code into that new module
   Module.CodeModule.InsertLines 99999, Code
   ' Restore programmatic access to the way we found it so no one gets mad at us
   RestoreVBAccess OriginalValue
   ' Set a timer to call the routine we stuffed into the new workbook (we need to do a timer so we can close this workbook before the other routine is invoked)
   Application.OnTime Now(), "'" & Workbook.Name & "'!ReOpen"
   ' Close this workbook
   ThisWorkbook.Close False
End Sub

The code stuffed into the new workbook is:

Public Sub ReOpen()
   Workbooks.Open "C:\Full\Path\To\Workbook.xls", ReadOnly:=True
   Application.ScreenUpdating = True
   ThisWorkbook.Close False
End Sub

It is invoked by the timer, opens the original workbook (now closed), turns screen updating back on, and then closes itself without saving.

Exl04Author Commented:
Thank you. This is fascinating...the part that amaze me is that we can open a workbook with a macro that is coded in that workbook!, even that the workbook with the macro is close?!?!
 kind a metaphysical existence, "you have to first exist if you want to come later from where you existed" ...if Issac Asimov  would  still be alive, and be a computer programmer, this piece of code it would sparked  an idea for a science fiction novel.

Thanks again for commenting/explaining the code.
zorvek (Kevin Jones)ConsultantCommented:
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.