Open Excel workbook as read-only via VB6

Question

I have an application written in VB6 that writes data to a spreadsheet. I'm using the MS Excel 11.0 Object library to create an instance of Excel and open the book:

Dim xlApp As Excel.Application, remoteBook As Workbook
Set xlApp = New Excel.Application
Set remoteBook = xlApp.Workbooks.Open(sheetName)

In addition to writing to the workbook "sheetName", the program also allows the user to launch the workbook in order to view the cumulative results.

There is a chance, however slim it may be, that a user could have the workbook open for viewing the results while someone else is trying to write to it. I want to give the user writing to the sheet priority. Is there a way I can launch the sheet for viewing as read-only? There is a read-only property of the excel application object, but it is (of course) read-only.

How can I set up my program to write data to the workbook even if someone has accidentally left the file open at their desk?

Answer 1

Simply do this:

Set remoteBook = xlApp.Workbooks.Open( sheetName, , true)

Where true is whether or not to open as Read Only. ReadOnly is the third parameter to this method.

Answer 2

I think you might be able to do it via the Workbook.ChangeFileAccess method as described here. Not sure if it will suit your circumstances though.

Answer 3

Let me make sure I have properly interpreted your issue:

1. Your app writes an excel file
2. The App launches the file in Excel to the User

Now here's what I think you're saying: Once the user is viewing the sheet, they may or may not want to edit that sheet.
In other words, you don't want to use

Set remoteBook = xlApp.Workbooks.Open( sheetName, , true)

100% of the time because the user viewing may want to change the data.

The downside is that this dastardly user may leave the file open to prevent other users from writing to that file.

Is that correct?

If so, it sounds like you may need to explicit state in your app to "Open for viewing" or "open for read-only" access and then toggle the Read Only property appropriately; which is probably undesirable.

However, you can't force a save on an office doc once someone else has it open.