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!
