CODEWITHSUNDEEP
stringexceldatetime-formatvba
AndersVba
AndersVba

Reputation: 11

VBA conversion failed when converting date and/or time from character string

An often asked question, when I search the internet, but none of the answers seem to fit my problem. I hope you can help.

I am trying to collect dates from a DB, using code: 

Sheets("Data").Select
Dim sh1 As Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet

sh1.Activate
If ActiveSheet.FilterMode Then
    Range("ActivityPlan[[#Headers],[AIDA]]").Select
    ActiveSheet.ShowAllData
End If

Dim lLastRow As Long
'Get Last row
lLastRow = sh1.Range("C" & Rows.Count).End(xlUp).Row

And having defined my values using the type of the three following examples

     If Not sh1.Cells(i, 19) = "" Then
        tMaintSimulStartDate = Format(sh1.Cells(i, 19), "MM/dd/yyyy")
     End If

     tMaintExpDate = Format(sh1.Cells(i, 20), "MM/dd/yyyy")

     If Not sh1.Cells(i, 21) = "" Then
        tUpBaseDteFix = Format(sh1.Cells(i, 21), "MM/dd/yyyy")
     Else
        tUpBaseDteFix = vbNull
     End If

None of them seem to work. I just get the error "conversion failed when converting date and/or time from character string", VBA marking my " CN.Execute (SQL) "

Upvotes: 1

Views: 3548

Answers (1)

JNevill
JNevill

Reputation: 50119

I think it's very likely that the values in your cells sh1.cells(i,19), (i, 20), (i,21) are just strings. They may look like dates, but Excel doesn't recognize them as such.

Your best bet is to use DateValue() function in VBA. This will take in a string and spit out an actual, honest to goodness date. If this fails, it's because your Date format in the cell is something that DateValue() can't recognize. In which case you'll probably have to parse out the values of the date from the cell and then send it over to DateValue() to turned into a proper date.

Upvotes: 2

Related Questions