Error Code

[robertsmyth100]

Hi there,

I have a simple peice of VB code that when a button is pressed opens another workbook say 'workbook2', performs a paste from workbook1 into workbook2 and then closes workbook2. If another user has workbook2 already open then there is an error and the code stops working. Is there a way to make the code handle the error and show a message box saying 'try again later' in workbook1? My VB skills are basic indeed but the code I have so far is...

Sub copy_workbook2()

Sheets("1").Select
Rows("19:50").Select
Selection.Copy

On Error GoTo Handler
Workbooks.Open Filename:="\Workbook2"

Sheets("1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveWorkbook.Save
ActiveWorkbook.Close

Handler:
MsgBox ("File in use, try again")
Workbooks.Open Filename:="\workbook2"

End Sub

This code doesn't work as intended and I am not sure I am going about this the right way. Any help would be appreciated...

 

[RichardMGreen]

You can use this code to check if the file is open or not:

'Check if files are open or can be edited
Function IsFileOpen(file_to_open As String) As Boolean
Dim hdlFile As Long
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open file_to_open For Random Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    IsFileOpen = True
    Close hdlFile
End Function

You can then run an if statement to either do the copy/paste if the file is not in use or display the error message if it is.

 

[robertsmyth100]

Thanks. Having a little difficulty fitting this into my code though. Where do I actually define the file? Can I start my Sub with...

If IsFileOpen(file_to_open) = True Then
etc etc

 

[RichardMGreen]

Basically yeah.
You'd need to call the function with the path and name of the file you want to check.
It will return a Boolean type so you can then test it for True/False before doing anything else.