Use IF THEN ELSE to deal with file already open

[Carty]

Hi. I can open a file remotely but am trying to create an error handling routine where, if the file is closed, open and update the links. If the file is already open it gives a FileInUse error and gives you the option to open it as read only

I have been messing around with some code and ended up here:

Dim Filename as string
Filename = whatever the path is

Workbooks.Open (filename)

If FileInUse (filename) Then Workbooks.Open (filename), Notify:=False
    Else: Workbooks.Open (filename), UpdateLinks:=3
End If

Weirdly it comes back with a compile error "else without if" statement. Has the colon impacted this in a strange way? Could this be made more robust?

Thanks

Paul

 

[Fluff]

It needs to be like

If FileInUse (filename) Then
    Workbooks.Open (filename), Notify:=False
Else: Workbooks.Open (filename), UpdateLinks:=3
End If

You were trying to mix a 1 line If with a block If.

 

[Carty]

Thanks Fluff but now there is a compile error "sub or function not defined" FileInUse highlighted. I was hoping that, as that was the title of the error dialogue box, FileInUse would be a standard thing like DATE for example but I guess I'd have to do some more research on error handling

 

[Fluff]

There is no vba keyword "FileInUse" hence the error.
When I saw that I assumed that you had a function with that name, that checked if a file was being used.

 

[Carty]

So I would need to do something like this:

Sub openbook()
        some code to define what book to open and, if closed, open it and update it
End Sub

function FileInUSe
        some code to check if file open
        some code to set an error
        Some code to handle the error (open as read only)
End function

 

[Fluff]

Something like

   On Error Resume Next
   Workbooks.Open "C:\Mrexcel\book1.xlsx"
   If ActiveWorkbook.ReadOnly Then
      If MsgBox("Invoice sheet is ""ReadOnly"" do you wish to continue?", vbYesNo, "Read Only") = vbNo Then Exit Sub
   End If
   On Error GoTo 0
   Set Wbk = ActiveWorkbook

 

[Carty]

Wow! Thanks Fluff. Wasn't expecting that, was just trying to get an idea if the methodology was correct.
Thanks for your patience and guidance
Paul

 

[Fluff]

My pleasure & thanks for the feedback