12 Replies Latest reply on Jun 29, 2016 11:40 AM by Andreas Killer

    IsFileOpen function not working

    A. D.

      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.

        • Re: IsFileOpen function not working
          Peter Brinkhuis

          Do you have an Excel file open that you want to add to? Or do you want to open the Excel file when the macro runs?

           

          The first lines that you posted are for connecting to Excel.

           

          I found an article on the always helpful CadSharp. They use the line:

          Set xlApp = New Excel.Application

          Instead of your version, which is similar to the code that comes from SolidWorks recorded macros and which is often advised against. I can't remember the reason though.

          Set xlApp = CreateObject("Excel.Application")

           

          You should consider closing the file afterwards and setting the references to null.

            • Re: IsFileOpen function not working
              A. D.

              First: I would like to open the excel file from within the solidworks macro.

              Further: I think your answer has helped me. Changing to: SetxlApp = New Excel.Application  stopped the macro from only returning false at the check if the file is open or not. So thats working now, great, thank you!

               

              Next problem appeared now, target of the macro is to let it give some cells of the excel file a specific value. To do this i have the following lines of code after deciding whether the excel file should still be opened or already is open:

               

                  xlApp.Workbooks("Pallet-A.xlsm").Worksheets("Werkopdracht").Activate     xlApp.Worksheets("Werkopdracht").UnProtect        xlApp.Worksheets("Werkopdracht").Range("E4").value = 123456     xlApp.Worksheets("Werkopdracht").Range("D10").value = CPLengte     xlApp.Worksheets("Werkopdracht").Range("E10").value = CPBreedte     xlApp.Worksheets("Werkopdracht").Range("F10").value = CPHoogte     xlApp.Worksheets("Werkopdracht").Range("E8").value = CPNAV_Nummer     xlApp.Worksheets("Werkopdracht").Range("D12").value = CPLeverDatum     xlApp.Worksheets("Werkopdracht").Range("D17").value = CPAantal     xlApp.Worksheets("Werkopdracht").Range("I14").value = CPOpmerkingen     xlApp.Worksheets("Werkopdracht").Range("I8").value = CPKlantNaam     xlApp.Worksheets("Werkopdracht").Range("E25").value = CPLengte - (2 * CPPal_Afstand_Balken_Tot_Zijk)     xlApp.Worksheets("Werkopdracht").Range("D24").value = CPPal_Dekplanken     xlApp.Worksheets("Werkopdracht").Range("D23").value = CPPal_Balken     xlApp.Worksheets("Werkopdracht").Range("D25").value = CPPal_Sledes 'Kopmaten invullen:     xlApp.Worksheets("Werkopdracht").Range("F24").value = AdvPalDikteDekplank & " x " & AdvPalBreedteDekplank     xlApp.Worksheets("Werkopdracht").Range("F23").value = AdvPalDikteBalk & " x " & AdvPalBreedteBalk     xlApp.Worksheets("Werkopdracht").Range("F25").value = AdvPalDikteSlede & " x " & AdvPalBreedteSlede        xlApp.Worksheets("Werkopdracht").Protect

              This works perfectly when the excel file wasn't opened by the macro already, but doesn't work when the macro already earlier opened the file. In other words, i think the macro still doesn't connect that well to the excel app when the excel file was already opened earlier.

               

              How can i achieve the macro to still connect to the excel app?

               

              For what it's worth, the following error comes up on the first line of the code above:

               

              Run-time error '9':
              Subscript out of range

               

               

              Thanks for your help!

                • Re: IsFileOpen function not working
                  Peter Brinkhuis

                  When you select the debug option, which line is highlighted for the error? Sorry, found it in your post. The error means that you are trying to evaluate the value of for example some_array(3) when the some_array(2) is the last item in the array.

                   

                  I mentioned closing the file and setting the references to null in my previous reply Have you implemented that already? When you fail to close the document properly, some references may remain present in the memory and unexpected things might happen the next time you open a file.

                   

                  For the Dutch: Groeten uit Enschede

              • Re: IsFileOpen function not working
                Andreas Killer

                Forget about IsFileOpen, when you exactly know where the file is you can use GetObject.

                 

                Sub Test()
                  Dim xlApp As Object 'Excel.Application
                  Dim xlBook As Object 'Excel.Workbook
                  'Note: This opens the file hidden!
                  Set xlBook = GetObject("C:\Pallet-A.xlsm")
                  'Get the pointer to the application
                  Set xlApp = xlBook.Parent
                  'Be sure the application is visible:
                  xlApp.Visible = True
                  'Make the file visible:
                  xlBook.Windows(1).Visible = True
                End Sub

                 

                And I suggest that you remove the references to Excel and use late binding, so your code becomes compatible with other (future) versions of Excel.

                 

                Andreas.

                  • Re: IsFileOpen function not working
                    A. D.

                    Hmm, that sounds interesting!

                    When trying your code it gives an error on the last row;

                    xlBook.Windows(1).Visible = True

                     

                    It's a run-time error 9, Subscript out of range.

                     

                    I've been looking, and found some other code, then blended these two together, did some trial and error testing, and now have this code:

                    Sub opensheet()

                    Dim strSpreadsheet As String

                     

                     

                    Dim xlBook As Object

                    Dim xlSheet As Worksheet

                    Dim xlapp As Object

                     

                     

                    strSpreadsheet = "C:\Pallet-A.xlsm"

                    'Set xlapp = CreateObject("Excel.Application")

                    '    xlapp.Visible = True

                       ' Set xlBook = xlapp.Workbooks.Open(strSpreadsheet)

                        Set xlBook = GetObject(strSpreadsheet)

                        Set xlapp = xlBook.Parent

                        'Set xlSheet = xlBook.Sheets(1) 'Index of the sheet

                        'xlSheet.Activate

                        xlBook.Windows(1).Visible = True

                        xlapp.Visible = True

                    End Sub

                     

                    In fact, this works good, apart from the fact that it besides the pallet-a.xlsm also always opens a blank excel window (So without a workbook in it).

                     

                     

                    Do you have any clue on how this could be avoided?

                     

                    Best regards,

                    Arno

                      • Re: IsFileOpen function not working
                        Andreas Killer

                        apart from the fact that it besides the pallet-a.xlsm also always opens a blank excel window (So without a workbook in it).

                         

                        Which version of Excel do you use?

                        Andreas.

                          • Re: IsFileOpen function not working
                            A. D.

                            Excel 2013 it is

                              • Re: IsFileOpen function not working
                                Andreas Killer

                                Excel 2013 it is

                                I can reproduce the behavior, it's related to Excel, XL2010 and before is MDI, but XL2013 and later is SDI.

                                 

                                Use the code below instead.

                                BTW, if you just want to open/modify/save/close the file, there is no need to make it visible.

                                 

                                Andreas.

                                 

                                Sub Test()

                                  Dim xlBook As Object 'Excel.Workbook

                                  'Note: This opens the file hidden!

                                  Set xlBook = GetObject("C:\Pallet-A.xlsm")

                                  'Make the file visible:

                                  xlBook.Windows(1).Visible = True

                                End Sub

                                  • Re: IsFileOpen function not working
                                    A. D.

                                    Hmm, still can't seem to get it to work properly..

                                    With the following code in a seperate module it opens the excel file (ok, with a blank page behind it, but thats ok for now i think) and it also recognizes the file already to be open when i run the code for the second time. But, the second time, it doesn't activate the workbook.. It should bring it to the front, but doesn't.. Do you know any code for this? Using: Set xlBook = xlapp.Workbooks("C:\Pallet-A.xlsm")  doesn't work unfortunately..

                                     

                                    This is the code i've got right now:

                                     

                                    Option Explicit

                                     

                                     

                                    Sub Test()

                                     

                                     

                                      Dim xlapp As Excel.Application

                                      Dim xlBook As Object 'Excel.Workbook

                                     

                                    ' xlapp.Visible = True

                                      'Note: This opens the file hidden!

                                      'Set xlBook = GetObject("C:\Pallet-A.xlsm")

                                      'Make the file visible:

                                      'xlBook.Windows(1).Visible = True

                                     

                                     

                                     

                                     

                                     

                                     

                                      If IsFileOpen("C:\Pallet-A.xlsm") = False Then

                                            MsgBox "File is not open"

                                            Set xlapp = CreateObject("Excel.Application")

                                            xlapp.Visible = True

                                           

                                                    'Set xlapp = New Excel.Application

                                                    'Set xlBook = xlapp.Workbooks.Open("C:\Pallet-A.xlsm")

                                                    'ActiveWindow.Visible = True

                                     

                                     

                                     

                                     

                                    Else

                                        MsgBox "File is open"

                                                    'Set xlapp = Excel.Application

                                                    'Set xlBook = xlapp.Workbooks("C:\Pallet-A.xlsm")

                                    End If

                                     

                                     

                                     

                                     

                                                    ' xlapp.Visible = True

                                                    'Note: This opens the file hidden!

                                      Set xlBook = GetObject("C:\Pallet-A.xlsm")

                                                   

                                                   

                                                   

                                      xlBook.Windows(1).Visible = True 'Make the file visible

                                     

                                     

                                    End Sub

                                     

                                     

                                    Thanks in advance!

                                      • Re: IsFileOpen function not working
                                        Andreas Killer

                                        This is the code i've got right now:

                                        So difficult? Again: Forget about IsFileOpen, use the code from my last post only, that's all:

                                         

                                        Set xlBook = GetObject("C:\Pallet-A.xlsm")

                                         

                                        After that line the file is opened in Excel, regardless if it was opened before or not.Okay?

                                        Andreas.

                                          • Re: IsFileOpen function not working
                                            A. D.

                                            Thank you Andreas, it´s working now!

                                            I do have a following question, i´ll post it here:

                                             

                                            Now the excel file is open i also would be abled to print it from a button within the (solidworks) userform.

                                            The macro to print is within the excel file and is called PrintSPM

                                            Now, i've seen that the following line should work, but unfortunately it's constantly returning an 1004 error?

                                             

                                            Sub PrintWorklist()

                                                AppActivate "Pallet-A.xlsm - Excel"

                                                xlApp.Run "PrintSPM"

                                            End Sub

                                             

                                            What am i doing wrong in this one?

                                             

                                            Thanks in advance!

                                            Arno