"File In Use" Alert

[JDaniluck]

Hey All,

This is my first question on this site. I use it all of the time by searching through the forums. So I thought I would finally join in the fun.

Anyhow, time for business. I am running into a problem with the "File in Use" alert.

I am attempting to write a code to automatically write data to a workbook in a shared location.

Every now and then this workbook in this shared location is in use by another person.

I have written a code to keep trying to open up the workbook in "Write" mode. However, When my code runs I get the "File in Use" message. I would like to add code into my workbook to automatically select "Read-Only" if I am prompted by the "File in Use" message.

So my overall goal is to open up this workbook in "write" mode, but if it opens up in "Read-Only" then I have the workbook close and then start again in 30 seconds. This repeats until I am able to open the workbook in "Write."

Any thoughts on how to control the "File in Use" alert?

Here is my code so far -

Dim Myfile As Range
Dim NewFile As Variant
Dim WB1 As Workbooks
Dim RF As Worksheet

Set WB1 = Workbooks("Repair_Form.xlsm")
Set RF = WB1.Sheets("Repair_Form")

Set Myfile = RF.Range("AK4")
' Database Location

        Workbooks.Open (Myfile.Value)

    If Workbooks("Repair_Authorize_Database.xlsx").ReadOnly Then
        Workbooks("Repair_Authorize_Database.xlsx").Close
        CountDown_Timer.startCountDown
        Exit Sub
    Else
    
        MsgBox "I made it :)"
    End If
    
        Workbooks("Repair_Authorize_Database.xlsx").Save
        Workbooks("Repair_Authorize_Database.xlsx").Close
        
Exit Sub

ErrorHandle1:

    MsgBox "I can not determine if the file is in Read Only"
    Exit Sub

End Sub

 

[JDaniluck]

Hey All,

To update everyone.

I tried using Workbooks.Open FileName:= Myfile.Value, Notify:=True & False. I still get this dang "File In Use" alert.

 

[JDaniluck]

File In Use Alert Message - VBA

Hello All,

A couple of days ago I posted a thread just like this and didn't get any answers.

http://www.mrexcel.com/forum/excel-questions/838881-file-use-alert.html

To sum up what I am trying to do is to simply control the "File In Use" alert message with VBA in excel 2010/2013.

I am trying to avoid using the Sendkey method. All I need is a way to select read-only if write is not available when openning a workbook.

 

[RoryA]

Re: File In Use Alert Message - VBA

Please just bump your original post - don't create duplicates. (I've merged the two).

Read only is the default option, so have you tried adding:

Application.DisplayAlerts = False

before opening the workbook?

 

[JDaniluck]

Re: File In Use Alert Message - VBA

Please just bump your original post - don't create duplicates. (I've merged the two).

Thanks RoryA

 

[RoryA]

Re: File In Use Alert Message - VBA

You may have missed the edit to my post where I added a code suggestion.

 

[JDaniluck]

Re: File In Use Alert Message - VBA

Hey RoryA,

You are right, I did not see your edit.

I have tried this code and I still get the same result each time.

After the code loops for about the 3rd time I get the same alert message.