Conflict between innertext VBA and excel

Question

I need help scraping information from InternetExplorer to an Excel Workbook . When i scrap, information in the innertext property is correct, but in the excel it comes wrong. Using the code:

Workbooks("Automatizar PSA").Sheets(1).Cells(linha, 5).Value = ie.Document.getelementsbyclassname("tr-visualizar-detalhes text-center")(i + 1).innertext

I think this is caused because they assume diferent variables, i used variable inspection and got:

Watch : : ie.Document.getelementsbyclassname("tr-visualizar-detalhes text-center")(i + 1).innertext : 
"06/04/2017 11:09 " : Variant/String : Módulo11.PSAScrap

Watch : : Workbooks("Automatizar PSA").Sheets(1).Cells(linha, 5).Value : 
04/06/2017 11:09:00 : Variant/Date : Módulo11.PSAScrap

Any help, how can i fix that? Sorry for the way i posted, not familiar with forums, how can i paste it as code? Thank you!!

Answer 1

This is a regional DMY vs. MDY issue. Your sample is DMY/MDY ambiguous so it is impossible to tell what the dates are to begin with or what your system's regional settings are.

The problem with ambiguous dates (where both the month and day-of-month are less than 13) is that Excel will interpret the DMY as MDY or vice-versa. For any others with a day greater then 12, Excel will just put the bad date in as text.

Here is a 'helper' function that will flip DMY to MDY or MDY to DMY.

function flipDMYMDY(str as string)
    str = trim(str)
    flipDMYMDY = mid(str, 4, 3) & left(str, 3) & right(str, 10)
end function

Use as,

Workbooks("Automatizar PSA").Sheets(1).Cells(linha, 5).Value = _
   flipDMYMDY(ie.Document.getelementsbyclassname("tr-visualizar-detalhes text-center")(i + 1).innertext)