EXCEL97-MACRO OPENS FILE AS READ-ONLY

[MrsTFB]

I have a macro in Excel97 that opens another workbook, updates that workbook and closes it(saving). It works beautifully unless someone on the network has the file open. In that case, it opens it as read-only, makes the update and asks for a new name.

Is there a way to code a message to update workbook later or another way to verify if the workbook is open before the update is made?

I would appreciate any suggestions!

Bsimm GO TITANS!

 

[dsi]

Once you open the book, you could check for the read only status:

    Dim wbTmp As Workbook
    
    Workbooks.Open "C:\test.xls"
    Set wbTmp = ActiveWorkbook
    
    If wbTmp.ReadOnly = True Then
        'process Read Only case
    Else
        'process updates
    End If

DimensionalSolutions@Core.com

 

[MrsTFB]

Thanks for your reply.

Can I put code there in the read-only case to wait until available, then enter the data to update and close the workbook, saving then?

I'm doubting that will work, because the workbook containing the macro that opens the updating workbook will be closed. That macro will not be running.

Do you have any suggestions for the read-only case. I thought about a message saying the status sheet had not been updated, and request the user to come back later and update that workbook. The group I am working with, though, I'd MUCH rather it happen automatically through the macro. Else, many of the updates will not occur.


Thanks for any ideas!

Bsimm

 

[dsi]

The first thought that comes to mind would be to export the necessary data to a specific temp file. Then, include a macro (in the file that may be read only) that checks for the existance of this file. You could use Sub Autpen so it will run upon opening. Then, if the files exist, you could import the data from the files, update the Excel file, and delete the temp file. DimensionalSolutions@Core.com

 

[MrsTFB]

That seems pretty deep for my level of expertise. I may give it a try though.

Thanks very much for your input!

Bsimm
GO TITANS!