×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

EXCEL97-MACRO OPENS FILE AS READ-ONLY

EXCEL97-MACRO OPENS FILE AS READ-ONLY

EXCEL97-MACRO OPENS FILE AS READ-ONLY

(OP)
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!

RE: EXCEL97-MACRO OPENS FILE AS READ-ONLY

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

RE: EXCEL97-MACRO OPENS FILE AS READ-ONLY

(OP)
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

RE: EXCEL97-MACRO OPENS FILE AS READ-ONLY

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 Auto_Open 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

RE: EXCEL97-MACRO OPENS FILE AS READ-ONLY

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close