Dear Community,
I am trying to import a csv File (exported from Thunderbird) and adjust it in a way that the unnecessary parts are deleted, and some time calculations are done. For this task I wrote the following macro.
It works fine except of the part, where I use "dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())" to convert the Text Cell to a date or time cell. With this function I have to select the desired properties manually.
I am searching for a possibility to either set the right properties while linking the CSV file, or to perform
" dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())" with automatically selecting and applying the desired properties.
After the conversion from text to “Date” or “US English” the change to display it in a desired format is working as intended as long as at least 1 date and 1 time cell was predefined correctly. Otherwise it will display the wrong format. "dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())" with args1().value = 121 seems only to work properly if libre office already knows which User-defined format is desired.
Is there a way to tell libre office which exact user defined format is desired for displaying the date and time?
After the macro finished its performance the resulting ods should show the Date in European style: DD/MM/YYYY and the time from 00:00 to 24:00.
I added an example csv File (remove .xls and replace it with .csv) and the corresponding result (ods) . I hope someone can help me out with this.
Best regards,
Thomas
Time test.ods (14.1 KB)
Time test.xls (854 Bytes)
Edit: Code formatting
Sub Worktimeconverter
Dim sPath As String, sTitle As String, sFilename As String, sSheetname As String REM_________________________________________________________________________________ REM Select CSV File
Dim oFilePicker : oFilePicker = CreateUnoService( "com.sun.star.ui.dialogs.FilePicker" )
oFilePicker.setTitle( sTitle )
oFilePicker.setDisplayDirectory( sPath )
If oFilePicker.execute() = 1 Then selectFile = oFilePicker.getDirectory()
REM_________________________________________________________________________________ REM Import selected File to acutal Sheet and delete unwanted columns
Dim oSheet As Object, oColumns As Variant
GlobalScope.BasicLibraries.LoadLibrary("Tools")
oSheet=thiscomponent.getcurrentcontroller.activesheet
oSheet.link(ConvertToURL(selectFile), _
sSheetname, FILTER_NAME, FILTER_OPTIONS, _
com.sun.star.sheet.SheetLinkMode.VALUE)
oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
oColumns = oSheet.getColumns()
oColumns.removeByIndex(5, 4)
oColumns.removeByIndex(6, 1)
oColumns.removeByIndex(7, 1)
REM_________________________________________________________________________________
REM Count the Amount of Rows in the CSV File
DIM iRow As Integer
iRow = 0
If oSheet.GetCellByPosition(1,0).String <> "" Then
Do
iRow = iRow + 1
Loop Until oSheet.GetCellByPosition(1,iRow).String = ""
End IF
REM_________________________________________________________________________________
REM Change the Names of the Headers
DIM Counter As Integer, Coutner2 As Integer
Counter = 0
oSheet.GetCellByPosition(0,Counter).String = "Beschreibung"
oSheet.GetCellByPosition(1,Counter).String = "Startdatum"
oSheet.GetCellByPosition(2,Counter).String = "Startzeit"
oSheet.GetCellByPosition(3,Counter).String = "Enddatum"
oSheet.GetCellByPosition(4,Counter).String = "Endzeit"
oSheet.GetCellByPosition(5,Counter).String = "Kostenträger"
oSheet.GetCellByPosition(6,Counter).String = "Kostenstelle"
oSheet.GetCellByPosition(7,Counter).String = "Über Nacht"
oSheet.GetCellByPosition(8,Counter).String = "Dauer"
REM Calculate the Differenz between the Dates and Timestamps
if iRow > Counter Then
Do
Counter = Counter + 1
Counter2 = Counter + 1
oSheet.GetCellByPosition(7,Counter).Formula = "=D"+Counter2+"-B"+Counter2
oSheet.GetCellByPosition(8,Counter).Formula = "=E"+Counter2+"-C"+Counter2 + "+H"+Counter2
Loop Until Counter = iRow -1
End IF
REM_________________________________________________________________________________
REM Change the Format of the Cells so it shows German time
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
REM Select Cells -> Dauer und Tageswechsel
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "H2:I1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
REM Format Cells -> Dauer und Tageswechsel
args1(0).Name = "NumberFormatValue"
args1(0).Value = 121
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())
REM To Convert the other Cells to the right Format use TextToColumns
args2(0).Name = "ToPoint"
args2(0).Value = "$B$2:$B$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
args1(0).Value = 122
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())
args2(0).Value = "$C$2:$C$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())
args1(0).Value = 121
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())
args2(0).Value = "$D$2:$D$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())
args1(0).Value = 122
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())
args2(0).Value = "$E$2:$E$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0, Array())
args1(0).Value = 121
dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())
End Sub
