I found a post from 2011 on this topic and copied the code. I edited the code to improve brevity and reflect my requirement.
Code: Select all
Sub S_format_cellrange
Dim Localformat as new com.sun.star.lang.Locale
Dim objDoc As Object
Dim objNumberFormats As Object
Dim objNumberFormatstring As String
Dim objNumberFormatid As Long
Dim objSheet As Object
Dim objRange As Object
Dim varData As Variant
Dim i As Integer
Dim k As Integer
If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
End If
oMRI = CreateUnoService("mytools.Mri")
objDoc = ThisComponent
objNumberFormats = objDoc.NumberFormats
objNumberFormatstring = "DD/MM/YYYY"
objNumberFormatid = objNumberFormats.queryKey(objNumberFormatstring, Localformat, True)
if objNumberFormatId = -1 then 'not existing yet
objNumberFormatId = objNumberFormats.addNew(objNumberFormatstring,Localformat)
end if
objSheet = ThisComponent.Sheets.getByName("Dictionary")
objRange = objSheet.GetCellRangeByName("A2:A2")'<-------------Cellrange to be converted
varData = objRange.getDataArray()
objRange.ClearContents(4)
objRange.NumberFormat = objNumberFormatId
oMRI.inspect varData
for i = 0 to uBound(varData)
for k = 0 to UBound(varData(i))
If Not (varData(i)(k) = "") Then
varData(i)(k) = datevalue(replace(varData(i)(k),"'",""))'trim single quotes from around string
End If
next k
next i
oMRI.inspect varData
objRange.setDataArray(varData)
End Sub
The final 'oMRI.inspect varData' results in the error "Object Not Set" and the data is shown as a Date whose value is zero, yet the watch window at that time shows the value as Variant/Date with the value 11/02/2017 - see attached screen-shot.
If I comment out the for loop, the code runs to completion - as expected.
I would be very grateful if someone could point out my error.
Cheers,
Doug