Opening a read only workbook in Excel 2010 with VBA

TheDugginator

New Member
Joined
Jul 18, 2012
Messages
13
I have some code I have written that is basically supposed to open a read only copy of an Excel 2010 workbook. It then leaves it open for a period of time (long enough for a running Powerpoint slideshow to update linked data) and then closes it without attempting to save, then loop. Very simple right? Should be. But I started getting occasional blip where the the code is attempting to open a read only copy of the file but even after the instruction which specifically requests a read only copy, I get the pop up window asking me if I want to "Open a Read only copy, Notify, or Cancel". We of course, code stops and updating stops. What can I do?
Sub Updater()
'
If Workbooks.Count > 1 Then
Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
Else
End If
Workbooks.Open FileName:="\\tbbc2-me1\share\share\Departmental Board Metrics\WO Data Xtractor Tools\WO Data Xtractor Template.xlsm", ReadOnly:=True
Application.OnTime Now + TimeSerial(0, 45, 0), "Closer"

End Sub
Sub Closer()
If Workbooks.Count > 1 Then
Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
Else
End If
Application.OnTime Now + TimeSerial(0, 1, 0), "Updater"

End Sub

If the code which runs smoothly most of the time, is specifically requesting a read only copy, then why the pop up as if it was not specified? Help please.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Perhaps add this before your If stmt in both of the codes:
Application.DisplayAlerts = False

You should turn it back on at the end of the code before the End Sub line
Application.DisplayAlerts = True
 
Upvote 0
OK. I tried that, and oddly enough,that caused it to do it every single cycle. Is there a way to just say,
If (some code that refers to the pop up) choose "Read Only". I know that sounds kind of like a cheap fix, but I have been struggling with this a while so I am willing to be a bit cheesy if it solves this problem. Thanks for any input.
 
Upvote 0
Just to be clear, this is what I did
Sub Updater()
'

Application.DisplayAlerts = False
If Workbooks.Count > 1 Then
Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
Else
End If

Workbooks.Open FileName:="\\tbbc2-me1\share\share\Departmental Board Metrics\WO Data Xtractor Tools\WO Data Xtractor Template.xlsm", ReadOnly:=True
Application.DisplayAlerts = True
Application.OnTime Now + TimeSerial(0, 44, 0), "Closer"

End Sub
Sub Closer()
Application.DisplayAlerts = False
If Workbooks.Count > 1 Then
Workbooks("WO Data Xtractor Template.xlsm").Close SaveChanges:=False
Else
End If
Application.DisplayAlerts = True
Application.OnTime Now + TimeSerial(0, 1, 0), "Updater"

End Sub

Did I do it wrong? Again, thanks for any help!.
 
Upvote 0
I'm hoping to revive this question of mine with more responses. I foolishly responded back to my one responce on a Friday night which of course will get zero attention. I tried the one piece of advice I was given but that did not solve the issue. If you read my other posts after the original, hopefully there is a simple solution to this strange problem.
 
Upvote 0
Good morning.

If I trim down the code to test just the opening of the file (as read only), the file opens as read only and I am not prompted.

Code:
Sub Updater()
'
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\RA Test\WO Data Xtractor Template.xlsm", ReadOnly:=True

Application.DisplayAlerts = True
End Sub
 
Upvote 0
Correct. Thats the crazy part. Even without your Display Alerts addition, it will work as it should for several cycles, sometimes hours, but eventually it will prompt you, even though it is promting you in a response to a direct instruction that should have removed the need for the prompt. It is a wierd anomaly that only occurrs every "random #" of cycles. That is why I was asking for somewhat of an If, or Error or Event handling remedy. Since it only happens randomly.
 
Upvote 0
I don't have Excel 2010 & can't replicate the error you're experiencing. I'm afraid I don't know how to help - sorry.
 
Upvote 0
Although it is against forum rules, I would probably start new post & reference this one - some people only look at posts with zero responses. Sorry I couldn't be of assistance.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top