Dear all,

I've made a large userform with a lot of functions, one of the buttons is used to fill some of the custom properties to a excel file.

So first off, i'm trying to open an Excel file from solidworks vba. This itself is no problem i am abled to open excel files from solidworks vba using the following code:

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
     Set xlApp = CreateObject("Excel.Application")
     Set xlBook = xlApp.Workbooks.Open("C:\Pallet-A.xlsm")

Now, the problem is that everytime i click the button, it opens a new 'duplicate' of the excel file.

To solve this problem i tried implementing the following code.

If IsFileOpen("C:\Pallet-A.xlsm") = False Then
MsgBox "File is open"
    Set xlBook = xlApp.Workbooks.Open("C:\Pallet-A.xlsm", False, False)
Else
    MsgBox "File is not open"
End If

In combination with this function, which i found on the microsoft website:

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

When testing this function it works perfectly, as long as i open my files 'manually', where manually means that i 'manually' go to excel, CTRL-O (to open file), select file, open it.

When opened this way, the function works great and is returning the right value.

But, the actual problem, when opened a file trough Solidworks VBA it does not recognize it to be open. The function always returns FALSE. How can i achieve this function to work properly? My presumption is that i sort of need to 'connect' with the excel app before running the IsFileOpen function, is this correct? If yes, then how could this be achieved?

Thanks in advance!

Best regards,

Arno

-------------------

Edit; Removed HTML tags in forum message as they suppose not to be working correctly.