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

Exl04
Exl04 used Ask the Experts™
on
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;
 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23502689.html
(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.
Refreshing-WorkbookButton.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
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...

Kevin

Author

Commented:
Thanks Keniv, take your time

JB
Top Expert 2008

Commented:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?

Author

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.
Top Expert 2008

Commented:
Try the attached workbook.

Kevin
Refreshing-WorkbookButton2.xls

Author

Commented:
Good morning!

Variable not defined
OriginalValue = ActivateVBAccess
Top Expert 2008
Commented:
Sorry about that...last minute change after testing...never a good idea ;-)

Kevin
Refreshing-WorkbookButton2.xls

Author

Commented:
Kevin,

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?  ..to 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.

Author

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.
Top Expert 2008

Commented:
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 http://en.wikipedia.org/wiki/The_Mythical_Man-Month) 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.

Kevin
Top Expert 2008

Commented:
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:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\X.0\Excel\Security\AccessVBOM
   HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\X.0\Excel\Security\AccessVBOM

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.

Kevin

Author

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.
Top Expert 2008

Commented:
:-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial