AnsweredAssumed Answered

Closing excel from Solidworks VBA won't work

Question asked by A. D. on May 24, 2016
Latest reply on Sep 24, 2019 by Madson Germano

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

Outcomes