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!
Edit; Removed HTML tags in forum message as they suppose not to be working correctly.