Error on Try, Catch End Try statement

[Thomas.Long]

I'm having a bit of difficulty getting a program to work. I have a number of people using a part that writes to the same excel sheet (We do a lookup to ensure that we aren't making duplicate parts with this part generator, so it tells us the file number and location if its already been created). However, if two people are using it at the same time (or on an odd error where the program seems to try to open the connection to the file twice. Can't figure out why that is happening yet) It produces an error where it asks you to save a new copy of the file.

My goal is to try to open a link to the file 4 times, and if it is open each time, attempt to close the link. In theory, the program should only be able to close the link to the file if for some reason the program has had the error where it attempted to open the file twice over. In that case it would close the link and on the next pass through it would open the excel link and run the program normally.

The problem is I am getting a compile error whenever I attempt to input End Try, before I even try to run the program. It's telling me "Expected: If or Select or Sub or Function or Property or Type or With or Enum or end of statement" 

'Open file if it exists and is not open
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    If Dir("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") <> "" Then
        Do While i <= 4

           'Try to close file link in case it is already open on computer
           Try
                Dim currentExcel As Excel.Application
                Set currentExcel = GetObject("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx")
                currentExcel.Quit
           End Try
        
            'Opening excel file
            If Not IsFileOpen("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") Then
                Set oExcel = CreateObject("Excel.Application")
                Set oBook = oExcel.Workbooks.Open("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx")
                Set oSheet = oBook.Worksheets("Sheet1")
                
                Exit Do
            End If

            'If number of iterations are complete and file is still open, message error and exit program
            If IsFileOpen("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") And i = 4 Then
                MsgBox ("File in use")
                Exit Sub
            End If
            
            
            'Wait for three seconds for each loop
            time1 = Now
            time2 = Now + TimeValue("0:00:03")
                        
            Do Until time1 >= time2
                DoEvents
                time1 = Now()
            Loop
            
            
            i = i + 1
        Loop
    Else
        Exit Sub
    End If
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

[MechMachineMan]

If you are not intending to write to the excel file, you should probably just open it as read-only, which would likely prevent some of these issues.

[Thomas.Long]

Unfortunately it needs to be written to. The sum of what this program is supposed to do is check for the existence of a part with the given geometry. If it doesn't exist, it creates one and writes the data for it into the excel file, which is why I need it to be able to write too

[MechMachineMan]

Ok. But you could probably prevent the occurrences of the document being opened by another user if you opened it by default for all users as a Read-Only file, and then only re-open it for write when you have confirmed you need to write to it.

If you code it to do all the writing using code you could probably write a loop to pause the code for a second or 2 and try to access it again (limit it out if it still can't access the code after 10 iterations of the delay loop or something).

[Thomas.Long]

I actually have a waiting loop already written into my code.

            'Wait for three seconds for each loop
            time1 = Now
            time2 = Now + TimeValue("0:00:03")
                        
            Do Until time1 >= time2
                DoEvents
                time1 = Now()
            Loop
  

This is an excerpt from the code shown earlier. The entire program repeats this 4 times so I actually have a total loop period of 3 seconds. My main issue is, while that corrects the one issue of possibly having other people having it open at the same time, there is still the issue of the fact that the program at times attempts to "double open" it.

I can't be sure why this is except for the fact that we're running this program multiple times at a time and for some reason it's not always closing it correctly or opening it correctly. Both instances lead to having the file opened twice by the same user. In this scenario what ends up happening is that I have to open my task manager and forcibly shut down the instance of excel in order to make it work again. That's basically what this try is for; it's intended to basically attempt to get an open link to the file from your computer only and close it. If it can't do that, it must not be open on your computer and is thus not an issue.

Here is the code from the ilogic rule that uses this macro.

'Check that duplicate part not being made
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
	PartExistenceRow = GoExcel.FindRow("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx", "Sheet1", "Length", "=", Parameter("Length"), "Width", "=", Parameter("Width"), "Material", "=", Parameter("Material"), "Type", "=", Parameter("Type"))

	If PartExistenceRow <> -1
		MessageBox.Show(GoExcel.CurrentRowValue("Location"), "Part Already Exists")
		GoExcel.Close
		Exit Sub
	End If
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Here is the entirety of the macro. I've edited out the error handler while I'm working on this so that I can see the errors popping up while I'm trying to add code to it.

Sub Main()

'On Error GoTo ErrorHandler
    
    'Declaring variables
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    Dim i As Integer
    Dim oRow As Integer
    
    Dim oParameters As Parameters
    Dim oLength As Parameter
    Dim oWidth As Parameter
    Dim oMaterial As Parameter
    Dim oType As Parameter
    
    Dim odoc As PartDocument
    
    Dim oExcel As Object
    Dim currentExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    
    Dim UOM As UnitsOfMeasure
    
    Dim time1
    Dim time2
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    'Initializing counter for open attempt
    i = 0
    

'Open file if it exists and is not open
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    If Dir("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") <> "" Then
        Do While i <= 4
        
            'Try to close file link in case it is already open on computer
            Try
                Dim currentExcel As Excel.Application
                Set currentExcel = GetObject("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx")
                currentExcel.Quit
            End Try
        
            'Opening excel file
            If Not IsFileOpen("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") Then
                Set oExcel = CreateObject("Excel.Application")
                Set oBook = oExcel.Workbooks.Open("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx")
                Set oSheet = oBook.Worksheets("Sheet1")
                
                Exit Do
            End If

            'If number of iterations are complete and file is still open, message error and exit program
            If IsFileOpen("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx") And i = 4 Then
                MsgBox ("File in use")
                Exit Sub
            End If
            
            
            'Wait for three seconds for each loop
            time1 = Now
            time2 = Now + TimeValue("0:00:03")
                        
            Do Until time1 >= time2
                DoEvents
                time1 = Now()
            Loop
            
            
            i = i + 1
        Loop
    Else
        Exit Sub
    End If
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


    If Not Dir("C:\Users\" & Environ$("Username") & "\Documents\RESUME.XLW") = "" Then Kill ("C:\Users\" & Environ$("Username") & "\Documents\RESUME.XLW")


    'Makes Excel invisibile
    oExcel.Visible = False

'Get Inventor document information
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    Set odoc = ThisApplication.ActiveDocument
    Set oParameters = odoc.ComponentDefinition.Parameters
    Set UOM = odoc.UnitsOfMeasure
    
    Set oLength = oParameters.Item("Length")
    Set oWidth = oParameters.Item("Width")
    Set oMaterial = oParameters.Item("Material")
    Set oType = oParameters.Item("Type")
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    
'Write in Data
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    With oSheet
        oRow = 1
        
        Do While .range("A" & oRow) <> ""
            oRow = oRow + 1
        Loop

        .range("A" & oRow) = UOM.ConvertUnits(oLength.Value, UOM.GetTypeFromString(UOM.GetDatabaseUnitsFromExpression(oLength.Expression, oLength.Units)), oLength.Units)
        .range("B" & oRow) = UOM.ConvertUnits(oWidth.Value, UOM.GetTypeFromString(UOM.GetDatabaseUnitsFromExpression(oWidth.Expression, oWidth.Units)), oWidth.Units)
        .range("C" & oRow) = oMaterial.Value
        .range("D" & oRow) = oType.Value
        .range("E" & oRow) = odoc.File.FullFileName
        .range("F" & oRow) = Split(Split(odoc.File.FullFileName, "\")(UBound(Split(odoc.File.FullFileName, "\"))), ".")(0)
        
    End With
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    
    
'Saving and exiting Excel
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    oExcel.Save
    oExcel.Quit

    Set oSheet = Nothing
    Set oBook = Nothing
    Set objWorkbook = Nothing
    Set oExcel = Nothing
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    Exit Sub


ErrorHandler:
    oExcel.Save
    oExcel.Quit

    Set oSheet = Nothing
    Set oBook = Nothing
    Set objWorkbook = Nothing
    Set oExcel = Nothing

    Exit Sub
End Sub


Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function