Use IF THEN ELSE to deal with file already open

Carty

Board Regular
Joined
Mar 3, 2009
Messages
76
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:

Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
It needs to be like
Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
So I would need to do something like this:
Code:
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
 
Last edited:
Upvote 0
Something like
Code:
   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
 
Upvote 0
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
 
Upvote 0
My pleasure & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top