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

Add comment

About the author

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

Month List