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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
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.
 

Carty

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
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
 

Carty

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
My pleasure & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,819
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top