Dates are not correctly populated from word to excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3537005%22%20slang%3D%22en-US%22%3EDates%20are%20not%20correctly%20populated%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537005%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20i%20resolve%20the%20following%20issue%20below%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20original%20file%20is%20in%20pdf%20and%20i%20have%20a%20macro%20to%20convert%20the%20pdf%20file%20to%20word%20docx.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20got%20another%20macro%20to%20extract%20data%20from%20word%20to%20excel.%20The%20macro%20works%20well%20but%20there%20are%20some%20instances%20which%20the%20dates%20populated%20into%20excel%20was%20incorrect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20dates%20showing%20in%20the%20word%20document%20is%204%2F1%2F2006%20(d%2Fmm%2Fyyyy)%20but%20when%20the%20data%20populates%20into%20excel%2C%20it%20is%20showing%20as%201%2F4%2F2006%20(d%2Fmm%2Fyyyy).%20There%20is%20nothing%20wrong%20with%20the%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20of%20the%20dates%20are%20populated%20correctly%2C%20some%20aren't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVBA%20code%20as%20follows%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20mapbizfiledata()%0A%0ADim%20rngfind1%20As%20Range%0ADim%20rngfind2%20As%20Range%0ADim%20rngfind3%20As%20Range%0ADim%20rngfind4%20As%20Range%0ADim%20wsS%20As%20Worksheet%0ADim%20wsD%20As%20Worksheet%0A%0ASet%20wsD%20%3D%20Sheet1%0ASet%20wsS%20%3D%20Sheet5%0A%0A'Extracting%20data%20from%20Bizfile%0AWith%20wsS.Cells%0A'Application.FindFormat.Clear%0A%0ASet%20rngfind1%20%3D%20.Find(What%3A%3D%22REGISTRATION%20DATE%22%2C%20After%3A%3DActiveCell%2C%20LookIn%3A%3DxlValues%20_%0A%20%20%20%20%20%20%20%20%2C%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse)%0A%0AIf%20Not%20rngfind1%20Is%20Nothing%20Then%0Arngfind1.Offset(0%2C%201).Copy%0AwsD.Range(%22C18%22).PasteSpecial%20Paste%3A%3DxlPasteValues%0A'wsD.Range(%22C18%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%0A%0AElse%0A%0AwsD.Range(%22C18%22)%20%3D%20%22NA%22%0A%0AEnd%20If%0A%0AEnd%20With%0A%0AWith%20wsS.Cells%0A'Application.FindFormat.Clear%0A%0ASet%20rngfind2%20%3D%20.Find(What%3A%3D%22DATE%20OF%20LAST%20AR%22%2C%20After%3A%3DActiveCell%2C%20LookIn%3A%3DxlValues%20_%0A%20%20%20%20%20%20%20%20%2C%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse)%0A%0AIf%20Not%20rngfind2%20Is%20Nothing%20Then%0Arngfind2.Offset(0%2C%201).Copy%0AwsD.Range(%22C19%22).PasteSpecial%20Paste%3A%3DxlPasteValues%0A'wsD.Range(%22C19%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%0A%0AElse%0A%0AwsD.Range(%22C19%22)%20%3D%20%22NA%22%0A%0AEnd%20If%0A%0AEnd%20With%0A%0AWith%20wsS.Cells%0A'Application.FindFormat.Clear%0A%0ASet%20rngfind3%20%3D%20.Find(What%3A%3D%22DATE%20OF%20LAST%20AGM%22%2C%20After%3A%3DActiveCell%2C%20LookIn%3A%3DxlValues%20_%0A%20%20%20%20%20%20%20%20%2C%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse)%0A%0AIf%20Not%20rngfind3%20Is%20Nothing%20Then%0Arngfind3.Offset(0%2C%201).Copy%0AwsD.Range(%22C20%22).PasteSpecial%20Paste%3A%3DxlPasteValues%0A'wsD.Range(%22C20%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%0A%0AElse%0A%0AwsD.Range(%22C20%22)%20%3D%20%22NA%22%0A%0AEnd%20If%0A%0AEnd%20With%0A%0AWith%20wsS.Cells%0A'Application.FindFormat.Clear%0A%0ASet%20rngfind4%20%3D%20.Find(What%3A%3D%22PAID-UP%20%2CORDINARY%22%2C%20After%3A%3DActiveCell%2C%20LookIn%3A%3DxlValues%20_%0A%20%20%20%20%20%20%20%20%2C%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse)%0A%0AIf%20Not%20rngfind4%20Is%20Nothing%20Then%0Arngfind4.Offset(0%2C%202).Copy%0AwsD.Range(%22C17%22).PasteSpecial%20Paste%3A%3DxlPasteValues%0A%0AElse%0A%0AwsD.Range(%22C17%22)%20%3D%200%0A%0AEnd%20If%0A%0AEnd%20With%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20and%20appreciate%20the%20help%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3537005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537712%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20are%20not%20correctly%20populated%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20quick%20and%20at%20first%20glance%20without%20having%20tried%20it%2C%3C%2FP%3E%3CP%3Eremove%20the%20apostrophe%20(')%20from%20the%20number%20format%20lines.%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3Einstead%20of%3CBR%20%2F%3E'wsD.Range(%22C18%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%3CBR%20%2F%3Ewithout%20an%20apostrophe%3CBR%20%2F%3EwsD.Range(%22C18%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%3C%2FP%3E%3CP%3E..remove%20from%20all%20number%20formats.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3538324%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20are%20not%20correctly%20populated%20from%20word%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3538324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3B%2C%20tried%20previously%2C%20it%20didn't%20work%20either.%20I%20am%20going%20to%20share%20the%20files%20here%2C%20see%20whether%20are%20the%20dates%20populating%20correctly%20at%20your%20end.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

How do i resolve the following issue below?

 

The original file is in pdf and i have a macro to convert the pdf file to word docx.

 

I have got another macro to extract data from word to excel. The macro works well but there are some instances which the dates populated into excel was incorrect.

 

For example, dates showing in the word document is 4/1/2006 (d/mm/yyyy) but when the data populates into excel, it is showing as 1/4/2006 (d/mm/yyyy). There is nothing wrong with the formatting.

 

Some of the dates are populated correctly, some aren't.

 

VBA code as follows:

Sub mapbizfiledata()

Dim rngfind1 As Range
Dim rngfind2 As Range
Dim rngfind3 As Range
Dim rngfind4 As Range
Dim wsS As Worksheet
Dim wsD As Worksheet

Set wsD = Sheet1
Set wsS = Sheet5

'Extracting data from Bizfile
With wsS.Cells
'Application.FindFormat.Clear

Set rngfind1 = .Find(What:="REGISTRATION DATE", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If Not rngfind1 Is Nothing Then
rngfind1.Offset(0, 1).Copy
wsD.Range("C18").PasteSpecial Paste:=xlPasteValues
'wsD.Range("C18").NumberFormat = "dd/mm/yyyy"

Else

wsD.Range("C18") = "NA"

End If

End With

With wsS.Cells
'Application.FindFormat.Clear

Set rngfind2 = .Find(What:="DATE OF LAST AR", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If Not rngfind2 Is Nothing Then
rngfind2.Offset(0, 1).Copy
wsD.Range("C19").PasteSpecial Paste:=xlPasteValues
'wsD.Range("C19").NumberFormat = "dd/mm/yyyy"

Else

wsD.Range("C19") = "NA"

End If

End With

With wsS.Cells
'Application.FindFormat.Clear

Set rngfind3 = .Find(What:="DATE OF LAST AGM", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If Not rngfind3 Is Nothing Then
rngfind3.Offset(0, 1).Copy
wsD.Range("C20").PasteSpecial Paste:=xlPasteValues
'wsD.Range("C20").NumberFormat = "dd/mm/yyyy"

Else

wsD.Range("C20") = "NA"

End If

End With

With wsS.Cells
'Application.FindFormat.Clear

Set rngfind4 = .Find(What:="PAID-UP ,ORDINARY", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If Not rngfind4 Is Nothing Then
rngfind4.Offset(0, 2).Copy
wsD.Range("C17").PasteSpecial Paste:=xlPasteValues

Else

wsD.Range("C17") = 0

End If

End With

End Sub

 

thanks and appreciate the help in advance!

2 Replies

@hrh_dash 

On the quick and at first glance without having tried it,

remove the apostrophe (') from the number format lines.
Example:
instead of
'wsD.Range("C18").NumberFormat = "dd/mm/yyyy"
without an apostrophe
wsD.Range("C18").NumberFormat = "dd/mm/yyyy"

..remove from all number formats.

 

 

@NikolinoDE , tried previously, it didn't work either. I am going to share the files here, see whether are the dates populating correctly at your end.