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
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,
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
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
xla = Nothing
GC.Collect()
End Try
End Function
No comments:
Post a Comment