System.InvalidCastException: Conversion from string "" to type 'Date' is not valid

Question

Error message: System.InvalidCastException: Conversion from string "" to type 'Date' is not valid.

 Sub Main()

        Dim OutApp As Object
        Dim OutMail As Object
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim fecha As String
        Dim proc As System.Diagnostics.Process

        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            If proc.MainWindowTitle.Trim.Length = 0 Then
                proc.Kill()
            End If
        Next

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("\\file.xlsm")
        xlWorkSheet = xlWorkBook.Worksheets("Sheet")

        Try

            xlApp.Range("E41").Activate()
            xlApp.Range("E4").Select()

            xlWorkSheet.Range("E4").Select()
            xlWorkSheet.Range("E4").Activate()
            fecha = xlApp.ActiveCell.Value

            Do While xlApp.ActiveCell.Value <> ""

                'If (DateTime.Today - CDate(fecha)).Days = 180 Then
                 If (DateTime.Today - DateTime.ParseExact(fecha, "dd/mm/yyyy", CultureInfo.InvariantCulture)).Days = 180 Then

                   ' --more lines ---
                   '...
    End Sub

Getting error in this line:

Do While xlApp.ActiveCell.Value <> ""

For some reason it seems xlApp.ActiveCell.Value is being treated as date instead of string.

Another thing, is this the right way to compare if there are 6 months difference between today and a cell value?

If (DateTime.Today - DateTime.ParseExact(fecha, "dd/mm/yyyy", CultureInfo.InvariantCulture)).Days = 180

Answer 1

if you have a date value in a cell, Excel will auto format that cell type to date. So, when you compare that with "", it will throw exception. You should check for type of the cell value or cast the cell value to string before comparision.