I want to get the value of two Datepicker on VBA ms-Word and then use DateDiff to calculate the difference between two dates and make it display on the document. I can get the value of the Datepicker by using "ContentControl.Range.Text" but when I use it with DateDiff is saying "Type missmatch".
Sub Test1()
'
' Test1 Macro
'
'
Dim doc As Document
Set doc = Application.ActiveDocument
Dim CCtrl As String
CCtrl = "Date1"
Dim chkExt As Boolean
chkExt = False
Dim control As ContentControl
For Each control In doc.ContentControls
If control.Tag = CCtrl Then
chkExt = True
Exit For
End If
Next
If chkExt = False Then
MsgBox "Nothing"
Exit Sub
End If
Dim ChangeType As Date
ChangeType = control.Range.Text
Debug.Print IsDate(ChangeType)
End Sub
UPDATE:I Find the problem The display of the date is affecting the code if the date is display this way is working fine Normal Format
but if it display in Thai format is not working Thai Format
so is there any workaround to make the display as Thai format but the value of it is the same as the normal one.
I found the Solution Thanks to my Friend so the way to do it is by split the date and then use DateDiff this way on the Document the date still show as thai format but still got the value to use in DateDiff Here the Code that work for me:
Sub Test1()
'
' Test1 Macro
'
'
Dim doc As Document
Set doc = Application.ActiveDocument
Dim CCtrl, CCtrl2 As String
CCtrl = "Date1"
CCtrl2 = "Date2"
Dim chkExt As Boolean
chkExt = False
Dim control As ContentControl
Dim control2 As ContentControl
For Each control2 In doc.ContentControls
If control2.Tag = CCtrl2 Then
control2.DateDisplayFormat = "d ´´´´ bbbb"
Dim Result1() As String
Result1() = Split(control2.Range.Text)
Dim MyClasses1 As New Collection
MyClasses1.Add Item:="01", Key:="Á¡ÃÒ¤Á"
MyClasses1.Add Item:="02", Key:="¡ØÁÀҾѹ"
MyClasses1.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
MyClasses1.Add Item:="04", Key:="àÁÉÒ¹"
MyClasses1.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
MyClasses1.Add Item:="06", Key:="ÁԶعÒ¹"
MyClasses1.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
MyClasses1.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
MyClasses1.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
MyClasses1.Add Item:="10", Key:="µØÅÒ¤Á"
MyClasses1.Add Item:="11", Key:="¾ÄȨԡÒ¹"
MyClasses1.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
Dim engDate1 As String
engDate1 = Result1(0) & "/" & MyClasses1(Result1(1)) & "/" & Result1(2)
Debug.Print IsDate(engDate1)
Exit For
End If
Next
For Each control In doc.ContentControls
'check if there is a tag
If control.Tag = CCtrl Then
control.DateDisplayFormat = "d ´´´´ bbbb"
'For spliting Date as 00 00 00
Dim Result() As String
Result() = Split(control.Range.Text)
'Creating Keys
Dim MyClasses As New Collection
MyClasses.Add Item:="01", Key:="Á¡ÃÒ¤Á"
MyClasses.Add Item:="02", Key:="¡ØÁÀҾѹ"
MyClasses.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
MyClasses.Add Item:="04", Key:="àÁÉÒ¹"
MyClasses.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
MyClasses.Add Item:="06", Key:="ÁԶعÒ¹"
MyClasses.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
MyClasses.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
MyClasses.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
MyClasses.Add Item:="10", Key:="µØÅÒ¤Á"
MyClasses.Add Item:="11", Key:="¾ÄȨԡÒ¹"
MyClasses.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
'Put together the Date
Dim engDate As String
engDate = Result(0) & "/" & MyClasses(Result(1)) & "/" & Result(2)
Debug.Print IsDate(engDate)
Exit For
End If
Next
Dim ccdisplayDateDiff As ContentControl
Dim displayDateDiff As String
displayDateDiff = "displayDateDiff"
For Each ccdisplayDateDiff In doc.ContentControls
If ccdisplayDateDiff.Tag = displayDateDiff Then
ccdisplayDateDiff.Range.Text = DateDiff("d", engDate, engDate1)
Exit For
End If
Next
End Sub
ps. The Alien language is Thai language that can't be display and in the collection is the month in Thai language.Sorry for my bad English.
Thanks everyone for helping :)
CodePudding user response:
Sub Test1()
'
' Test1 Macro
'
'
Dim doc As Document
Set doc = Application.ActiveDocument
Dim CCtrl, CCtrl2 As String
CCtrl = "Date1"
CCtrl2 = "Date2"
Dim chkExt As Boolean
chkExt = False
Dim control As ContentControl
Dim control2 As ContentControl
For Each control2 In doc.ContentControls
If control2.Tag = CCtrl2 Then
control2.DateDisplayFormat = "d ´´´´ bbbb"
Dim Result1() As String
Result1() = Split(control2.Range.Text)
Dim MyClasses1 As New Collection
MyClasses1.Add Item:="01", Key:="Á¡ÃÒ¤Á"
MyClasses1.Add Item:="02", Key:="¡ØÁÀҾѹ"
MyClasses1.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
MyClasses1.Add Item:="04", Key:="àÁÉÒ¹"
MyClasses1.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
MyClasses1.Add Item:="06", Key:="ÁԶعÒ¹"
MyClasses1.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
MyClasses1.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
MyClasses1.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
MyClasses1.Add Item:="10", Key:="µØÅÒ¤Á"
MyClasses1.Add Item:="11", Key:="¾ÄȨԡÒ¹"
MyClasses1.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
Dim engDate1 As String
engDate1 = Result1(0) & "/" & MyClasses1(Result1(1)) & "/" & Result1(2)
Debug.Print IsDate(engDate1)
Exit For
End If
Next
For Each control In doc.ContentControls
'check if there is a tag
If control.Tag = CCtrl Then
control.DateDisplayFormat = "d ´´´´ bbbb"
'For spliting Date as 00 00 00
Dim Result() As String
Result() = Split(control.Range.Text)
'Creating Keys
Dim MyClasses As New Collection
MyClasses.Add Item:="01", Key:="Á¡ÃÒ¤Á"
MyClasses.Add Item:="02", Key:="¡ØÁÀҾѹ"
MyClasses.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
MyClasses.Add Item:="04", Key:="àÁÉÒ¹"
MyClasses.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
MyClasses.Add Item:="06", Key:="ÁԶعÒ¹"
MyClasses.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
MyClasses.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
MyClasses.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
MyClasses.Add Item:="10", Key:="µØÅÒ¤Á"
MyClasses.Add Item:="11", Key:="¾ÄȨԡÒ¹"
MyClasses.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
'Put together the Date
Dim engDate As String
engDate = Result(0) & "/" & MyClasses(Result(1)) & "/" & Result(2)
Debug.Print IsDate(engDate)
Exit For
End If
Next
Dim ccdisplayDateDiff As ContentControl
Dim displayDateDiff As String
displayDateDiff = "displayDateDiff"
For Each ccdisplayDateDiff In doc.ContentControls
If ccdisplayDateDiff.Tag = displayDateDiff Then
ccdisplayDateDiff.Range.Text = DateDiff("d", engDate, engDate1)
Exit For
End If
Next
End Sub
CodePudding user response:
You can achieve this simply by changing the date locale and format to English, collecting your data, then change it back to Thai.
Something like this:
Sub Test2()
Dim doc As Document
Set doc = Application.ActiveDocument
Const CCtrl As String = "Date1"
Const CCtrl2 As String = "Date2"
Const displayDateDiff As String = "displayDateDiff"
Dim control As ContentControl
Dim ccdisplayDateDiff As ContentControl
Dim engDate As String, engDate1 As String
For Each control In doc.ContentControls
Select Case control.Tag
Case CCtrl, CCtrl2
control.DateDisplayLocale = wdEnglishUS
control.DateDisplayFormat = "MMMM d YYYY"
If control.Tag = CCtrl Then
engDate = control.Range.Text
ElseIf control.Tag = CCtrl2 Then
engDate1 = control.Range.Text
End If
control.DateDisplayLocale = wdThai
control.DateDisplayFormat = "d ´´´´ bbbb"
Case displayDateDiff
Set ccdisplayDateDiff = control
End Select
Next
If Not ccdisplayDateDiff Is Nothing Then ccdisplayDateDiff.Range.Text = DateDiff("d", engDate, engDate1)
End Sub