Jump to content

[Solved] Open Office input date adds " ' " in front

Recommended Posts

So using this OOoCOM_UDF_v08.au3 from: here

Everytime I take the output of:

$Date2 = GUICtrlCreateDate(_DateToDayValue, 72, 40, 257, 25,$WS_TABSTOP)

and plug it into the open office calc spreadsheet with:

$TheDate = GUICtrlRead($Date2)
$openofficecalcwindow = _OOoCalc_Open("C:\Users\Jacob\Desktop\NCV\NCV.ods")
$SheetList = _OOoCalc_SheetList($openofficecalcwindow)
$SheetArrayEnd = UBound($SheetList,$UBOUND_ROWS) - 1
For $i = 1 To $SheetArrayEnd Step 1
        If $SheetList[$i] = "NCV" Then

The Problem: The date in calc is always input with an ' in front of it. Like '8/3/16 which makes calc not recognize it as a date but a string instead.

I've tried changing the string around to try and help with no success:

$TheDate = _DateTimeSplit(GUICtrlRead($Date2),$date,$time)
$TheDate = $arr[$date[1]] & " " & $date[2] & "/" & $date[3]

I've also read $TheDate into a message box and a text file. Neither of which adds the ' in front. I've tried looking through OOoCOM_UDF_v08.au3 for any indication of it adding extra characters while writing, but I just can't see it. It only seems to happen with dates as well. Here is the section it would be in:

; Description:      Write data to the specified worksheet cell in a workbook that was
;                   activated by calling _OOoCalc_Attach() or _OOoCalc_Open() funcs
; Syntax:           $val = _OOoCalc_WriteCell ($oCurCom, $oSheet, $sRow, $sColumn)
; Parameter(s):     $oCurCom – Component object returned by _OOoCalc_Attach() or _OOoCalc_Open()
;                   $sSheet - an integer: number of a worksheet starting 0
;                   $sRow - an integer: row number to write to starting 0
;                   $sColumn - an integer: column number to write to starting 0
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        Leagnus
; Note(s):          none
Func _OOoCalc_WriteCell($oCurCom, $Worksheet, $data, $sRow, $sColumn)
    $CellAddress = CHR($sColumn + 64) & $sRow

    If NOT IsObj($oCurCom) Then Return SetError(1, 0, 0)
    $Sheets = $oCurCom.GetSheets()
    $SheetComponent = $Sheets.GetByName($Worksheet)
    $CellComponent = $SheetComponent.GetCellRangeByName($CellAddress)
    $CellComponent.string() = $data
    $CellValue = $CellComponent.getstring()

Any help would be appreciated. As of right now It's a tad hacky where I try to get to the correct cell and input the data with controlsend.

Edited by keafter
Link to post
Share on other sites

Better open office handler: 

However it seems to have the same problem where it adds a ' in front of date values. It's almost like it's happening as autoit is handing the value over to open office.

It does close the spreadsheet better than the other handler though.

Link to post
Share on other sites

Here is a run-able example of what I'm talking about.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Date.au3>
#include <OOoCalc.au3>

$Form1_1 = GUICreate("Notary Client Verification Form", 667, 273, 192, 132)
$Date1 = GUICtrlCreateDate(_DateToDayValue, 72, 40, 257, 25,$WS_TABSTOP)
$Button1 = GUICtrlCreateButton("OK", 8, 206, 649, 57)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
        Case $Button1
            $TheDate = GUICtrlRead($Date1)
            $OOspreadsheet = _OOoCalcBookNew()
            $OOspreadsheet = _OOoCalcBookOpen("C:\new.ods")

Edit: My issue was resolved right here:


Edited by keafter
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...