Parsing a date

[Kenneth]

I want to write a VBA to extract certain data in MS Word documents and copy
to an Excel document. Inside the Word document there are dates of the form
"dd/mm/yyyy", and I cannot tell anything about the date format setting of
the local machine.

Now I can't get the conversion done correctly when both date and month are
<= 12.
Could somebody give me some suggestion? Thanks.


My VBA code (extracted):

Dim dateString Ds string
Dim xlWks As Excel.Worksheet
// ...
dateString = "02/05/2006" ' 2 May 2006
xlWks.Cells(1, 1).FormulaR1C1 = dateString ' The date value in Excel
became 5 Feb 2006

 

[Cindy M.]

Hi Kenneth,

I want to write a VBA to extract certain data in MS Word documents and copy
to an Excel document. Inside the Word document there are dates of the form
"dd/mm/yyyy", and I cannot tell anything about the date format setting of
the local machine.

Your VBA is in Word, correct? Then you can pull the localization info from the
Registry using the System.PrivateProfileString function. The information is
under HKEY_Current_User\Control Panel\International

Now I can't get the conversion done correctly when both date and month are
<= 12.
Could somebody give me some suggestion? Thanks.


My VBA code (extracted):

Dim dateString Ds string
Dim xlWks As Excel.Worksheet
// ...
dateString = "02/05/2006" ' 2 May 2006
xlWks.Cells(1, 1).FormulaR1C1 = dateString ' The date value in Excel
became 5 Feb 2006

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Kenneth]

Thanks Cindy.

Yes I am using Word VBA. Actually I would like to know whether there are
functions like CDate so I can parse a date in "dd/mm/yyyy" format into a
datetime type.

Now I've come to a workaround by changing the format into "yyyy/mm/dd"
first, but I will consider this as a stupid and nasty way. So anyone could
suggest a better method? Thanks.


My revised Word VBA code:

Dim dateString Ds string
Dim xlWks As Excel.Worksheet
// ...
dateString = "02/05/2006" ' 2 May 2006
' Change into yyyy/mm/dd so it will always parse correctly
dateString = Right(dateString, 4) + "/" + Mid(dateString, 4, 2) + "/" +
Left(dateString, 2)
xlWks.Cells(1, 1).FormulaR1C1 = dateString ' Correct date

 

[Cindy M.]

Hi Kenneth,

Yes I am using Word VBA. Actually I would like to know whether there are
functions like CDate so I can parse a date in "dd/mm/yyyy" format into a
datetime type.

Sure, VBA has CDate - you can search it in the Help. But that won't necessarily
solve your problem, since Visual Basic (with or without the "A") is so flexible
about recognizing dates <shrug> As long as both month and day are less than 12,
the danger of them becoming reversed will still be there.

From the Help: "CDate recognizes date formats according to the locale setting
of your system."

So your approach of parsing out the different "parts" is probably the safest.

Now I've come to a workaround by changing the format into "yyyy/mm/dd"
first, but I will consider this as a stupid and nasty way. So anyone could
suggest a better method? Thanks.


My revised Word VBA code:

Dim dateString Ds string
Dim xlWks As Excel.Worksheet
// ...
dateString = "02/05/2006" ' 2 May 2006
' Change into yyyy/mm/dd so it will always parse correctly
dateString = Right(dateString, 4) + "/" + Mid(dateString, 4, 2) + "/" +
Left(dateString, 2)
xlWks.Cells(1, 1).FormulaR1C1 = dateString ' Correct date

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Kenneth]

Thanks Cindy.

Hi Kenneth,

Sure, VBA has CDate - you can search it in the Help. But that won't
necessarily
solve your problem, since Visual Basic (with or without the "A") is so
flexible
about recognizing dates <shrug> As long as both month and day are less
than 12,
the danger of them becoming reversed will still be there.

From the Help: "CDate recognizes date formats according to the locale
setting
of your system."

So your approach of parsing out the different "parts" is probably the
safest.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply
in the newsgroup and not by e-mail