Friday, December 2, 2011

Opening Excel Application ,Modify,Save and Close in .NET

Opening Excel Application ,Modify,Save and Close in .NET


Public Function Change_Excel_Cell_Formatting(ByVal pFilePath As String, ByVal pSheetNo As Integer, ByVal pRowIndex As Integer, ByVal pMsgTitle As String) As Boolean
        Dim xla As Object
        Dim xlw As Object = Nothing
        Dim xls As Object
        Try
            xla = CreateObject("Excel.Application")
            xlw = xla.WorkBooks.Open(pFilePath)  ' GetObject(pFilePath)
            xls = xlw.sheets(pSheetNo)
            'xls.Rows(pRowIndex & ":" & pRowIndex + 1).Select()   '' Prob with this line that got error  
            ''as Too many fields, so limiting only 255 cols
            xls.Range(xls.cells(pRowIndex, 1), xls.Cells(pRowIndex + 1, 255)).Select()
            xla.Selection.NumberFormat = "@"

            xls.Range(xls.Columns(256), xls.Columns(xls.columns.count)).Select()
            xla.Selection.Delete()

            xla.DisplayAlerts = False
            xlw.save()
            Return True
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message, pMsgTitle, 
            Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
            Return False
        Finally
            If Not IsNothing(xla) Then xla.DisplayAlerts = False
            If Not IsNothing(xlw) Then xlw.close()
            xls = Nothing
            xlw = Nothing
            If Not IsNothing(xla) Then xla.Quit()
            If Not IsNothing(xla) Then 
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xla)
            xla = Nothing
            GC.Collect()
        End Try
    End Function

No comments:

Post a Comment