CODEWITHSUNDEEP
vbasavereadonly
cheapkid1
cheapkid1

Reputation: 469

vba check if a read-only workbook is opened

I cannot seem to get a definite feedback on whether or not a read-only workbook is opened or not. In my code I have it copy after closing/saving the workbook. I would like to be able to overwrite the read-only workbook if it's opened as read-only by another user. I tried this something like this bit of code, but had no luck, it just kept saying "File not open!" even when I had it opened. How can I check whether or not a "read-only .xlsx" file is opened or not in vba?

Sub Test_If_File_Is_Open_2()

Dim wBook As Workbook

On Error Resume Next

Set wBook = Workbooks("C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open     Machine Schedule\Open Machine Schedule.xlsx")
If wBook Is Nothing Then 'Not open
MsgBox "File is Not open!"
Else 'It is open
MsgBox "File is Open!" 'Never get this to display when I have the excel file open

End If

End Sub

What started the prompt for using this bit of code(above) was because I wanted the macro to not cause an error if the read-only workbook was opened by another user. When I run the macro below and have the copied read-only workbook opened prior, I get an error:"vba run time error 1004 cannot access read-only document" I don't get this error when the copied workbook is closed, it overwrites it like it's supposed to. Here is the code that prompted this question:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim backupfolder As String

backupfolder = "C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open Machine Schedule\"

ThisWorkbook.SaveAs Filename:=backupfolder & "Open Machine Schedule - Current.xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

Sub Auto_Save()

Dim savedate

savedate = Date

Dim savetime
savetime = Time
Dim formattime As String
formattime = Format(savetime, "hh.MM.ss")
Dim formatdate As String
formatdate = Format(savedate, "DD - MM - YYYY")


Application.DisplayAlerts = False

Dim backupfolder As String
backupfolder = "C:\Users\" & Environ("username") & "\Documents\Dropbox\Systems\Open Machine Schedule\"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs backupfolder & "Open Machine Schedule - Current.xlsx", FileFormat:=xlOpenXMLWorkbook
SetAttr backupfolder & "Open Machine Schedule - Current.xlsx", vbReadOnly
Application.DisplayAlerts = True
MsgBox "Backup Run. Please Check at: " & backupfolder & " !"

End Sub

Any help/suggestions would be much appreciated

Upvotes: 9

Views: 53858

Answers (1)

user2140261
user2140261

Reputation: 7993

your first code is only testing to see if a workbook exist not its state.

You could use this instead: 

If wBook.ReadOnly Then
    MsgBox "File is Read-only"
Else
    MsgBox "File is not read-only"
End If

Upvotes: 23

Related Questions