CellFormat VB Function

by Bill Dowling 29. July 2010 12:04
This code is used to call the CellFormat function to get a single Excel Cell '****************************** ' Open up the Excel Application '****************************** Dim xlApp As New Excel.Application xlApp.Visible = False xlApp.UserControl = False '****************** ' Open the workbook '****************** Dim xlWb As Excel.Workbook = xlApp.Workbooks.Open("C:\Data.xls", 0, True) '******************* ' Open the worksheet '******************* Dim nWsSheet as Integer = 1 Dim xlWs As Excel.Worksheet = CType(xlWb.Worksheets(nWsSheet), Worksheet) xlWs.Activate() '*********** ' Get a cell '*********** Dim nRow as Integer = 1 Dim nColumn as Integer = 5 Dim sCell As String = CellFormat(xlWs.Cells(nRow, nColumn)) A function to extract and format an Excel Cell in .Net ''' <summary> ''' Format the passed Excel Cell to a Formatted String ''' </summary> ''' <param name="axlCell">Excel Cell</param> ''' <returns>Formatted String</returns> ''' <remarks></remarks> Private Function CellFormat(ByVal axlCell As Object) As String Dim xlCell As Excel.Range = CType(axlCell, Excel.Range) Dim sCell As String = xlCell.Text.ToString.Trim If sCell.Contains("####") AndAlso Not xlCell.Value Is Nothing Then sCell = xlCell.Value.ToString.Trim If TypeOf xlCell.Value Is Date AndAlso IsDate(sCell) Then sCell = Format(xlCell.Value, "MM/dd/yyyy") sCell = sCell.Replace("'", "").Replace("""", "") Return sCell End Function

Tags:

.Net Coding Examples | Excel

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List