6 Replies Latest reply on Jun 6, 2017 2:12 AM by Deepak Gupta

    Closing excel from Solidworks VBA won't work

    A. D.

      Dear all,

       

      I'm trying to make a macro which fills in a cell in a specific excel workbook. I've got this working, but the only thing i would still like to do is to close the workbook again after pressing the ok button (or any other event, doesn't really matter for the question).

       

      I've tried to accomplish this through the XLbook.close command (XLbook is dim'd as Excel.Workbook in this case), but it's giving me the following error: Object variable or With block variable not set (Error 91)

      I can't really find out what's the problem in here, it's probably something stupidly easily, but i can't seem to find it?

      I'll hope you guys can help me find the error in this code. The code is as following:

       

          Dim xlApp As Excel.Application
          Dim XLbook As Excel.Workbook
          
          
      Private Sub CommandButton1_Click()
          MsgBox TextBox1.Value
          xlApp.ActiveSheet.Range("C3").Value = TextBox1.Value
          
      End Sub
      
      
      Private Sub CommandButton2_Click()
          Set xlApp = CreateObject("Excel.Application")
          Dim XLbook As Excel.Workbook
          'Set XLbook = xlApp.Workbook("X:\Arno\Parametrisch model\Test.probeersels\Excel file vullen vanuit Solidworks\Excelfile.xlsx")
          'xlApp.Workbooks.Close FileName:="X:\Arno\Parametrisch model\Test.probeersels\Excel file vullen vanuit Solidworks\Excelfile.xlsx"
          XLbook.Close (SaveChanges = False)
          Unload Me
          
      End Sub
      
      
      
      
      
      
      Private Sub UserForm_Initialize()
          Set xlApp = CreateObject("Excel.Application")
          'Dim xlApp As Excel.Application
          Set xlApp = CreateObject("Excel.Application")
          'Dim Sourcebook As Excel.Workbook
          
          xlApp.Visible = True
          Set XLbook = xlApp.Workbooks.Open("X:\Arno\Parametrisch model\Test.probeersels\Excel file vullen vanuit Solidworks\Excelfile.xlsx", , False) 'Opens Workbook with your instance of xlBook Object
          'xlApp.Workbooks.Open FileName:="X:\Arno\Parametrisch model\Test.probeersels\Excel file vullen vanuit Solidworks\Excelfile.xlsx"
          'xlApp.ActiveSheet.Range("C3") = TextBox1.Value
      End Sub
      

       

      The code is written in VBA.

       

      Thank you for your help,

      Best regards,

      Arno