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

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

Thanks Keniv, take your time

JB

Try this.

Kevin
Refreshing-WorkbookButton.xls

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?

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.

Try the attached workbook.

Kevin
Refreshing-WorkbookButton2.xls

Good morning!

Variable not defined
OriginalValue = ActivateVBAccess

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.

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.

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

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

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.

:-)