Thread: Unable to save spreadsheet using Office.Interop

My coding sucks, I very new to .Net programing. So I'll try to explain this mess and post some of my code if necessary. On my main form I open a filediaglog and open a xls, create my class object that handles opening and returning the spreadsheet to the main form, the main form the works with data in it basically reads it line by line and inputs data back in. That all works dandy. When I call the close sub I wrote in the class that does the opening of the xls. It's like it sets all my Workbook/Worksheet/App values to nothing. So there for I understand the workbook object can't call the save method when it does have anything to save. Here is my class code:

Code:

Imports Microsoft.Office.Interop

Public Class ClientValidation
    Public StrFile As String
    Public ExWb As Excel.Workbook
    Public ExSheet As Excel.Worksheet
    Public ExRange as Excel.Range
    Public ExApp As Excel.Application
    Public Function OpenExcel() As Excel.Worksheet
        ' Create new Application.
        Dim exApp = New Excel.Application
        Dim exWb as Excel.Workbook
        Dim exSheet as Excel.Worksheet
        'Dim exRange as Excel.Range
        exwb = exApp.Workbooks.Open(StrFile)
        exSheet = DirectCast(exWb.Worksheets(1),Excel.Worksheet)
        Return ExSheet
    End Function
    Public Sub Save()
        ExWb.Save()
        'exWb.
    End Sub
    Public Sub Close()
        ExWb.Save()
        'exRange = Nothing
        exSheet = Nothing
        exWb = Nothing
        ExApp.Quit()
        exApp = Nothing 
    End Sub
    Public Function RecordCount() As Integer
        ExRange = ExSheet.UsedRange
        RecordCount = ExRange.Rows.Count()-1
    End Function
End Class

Rewritting how this all works, I'm going to load the workbook into a datagridview do the work and then export it back as an excel file. This can be marked as answered.

For future reference, in your OpenExcel function you Dim exWb, but it is already declared as a public class variable. The exWb object you open in OpenExcel is effectively gone when you return from the function, and the ExWb variable you declared in the class header is never used except in Save and Close subs.

That explains a lot, that right there was a cluster F of learning how to fake put/get on a threaded app so i temporarily went wild not realizing the impact and shear crudeness of the code. In the end ive learn just get the data i want from excel into either a datatable or datagrid and leave excel alone after that because its a calamity waiting to bust at the seems. Im sure it's great and is wicked powerful for a lot of application probably even mine in areas. But i like the approach of just working with the exported data. I dont feel so confined. Ps that code i posted above in horrible it should be stickied as what not to do with public/shared variable and classes.