AnsweredAssumed Answered

IsFileOpen function not working

Question asked by A. D. on Jun 13, 2016
Latest reply on Jun 29, 2016 by Andreas Killer

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.

Outcomes