On Error Resume next 1

[vestax22]

Hi,

I'm programming an application using Excel 2000. I have a macro inside this application that opens another workbook to extract data from it. Here is the code :

Sub openTrains()

Application.ScreenUpdating = False

Dim MySource As String

currentYear = Worksheets("Menu&quot.Range("B35&quot.Value
Mybook = ActiveWorkbook.Name
MySource = "C:\Prod\" & currentYear & "\" & "Daily Ore Train Movements" & ".xls"

On Error GoTo errorHandler
Workbooks.Open MySource
trainBook = ActiveWorkbook.Name

With Worksheets("Billing&quot
traindata(0) = .Range("E64&quot.Value
traindata(1) = .Range("I70&quot.Value
End With

Exit Sub

errorHandler:
If Err.Number = 1004 Then
MsgBox ("Error Openning the file&quot
End If
Resume
End Sub


If an error is capted it means that the file was not found or that the file is already opened. I want to know how to tell the machine that if the file is already opened to extract the data from the opened file.

Another thing that you should notice is that after openning the file I put the name of the opened workbook inside trainBook to be able to close it afterwords. This works fine if the file opens without any errors but when there is an error I capt the name of the active workbook and close it afterwords. This is not good.

Any suggestions or insights on error capting would be greatly appreciated. Thx

 

[SkipVought]

Why don't you check forst to see if Mybook is open...

    Dim bOpen As Boolean
    bOpen = False
    For Each wb In Workbooks
       If wb.Name = Mybook Then bOpen = True
    Next
    If Not bOpen Then
    'open the workbook
    End If

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Why don't you check forst to see if Mybook is open...

    Dim bClosed As Boolean
    bClosed = True
    For Each wb In Workbooks
       If wb.Name = Mybook Then bClosed = False
    Next
    If bClosed Then
    'open the workbook
    End If

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com