keafter

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

3 posts in this topic

#1 ·  Posted (edited)

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
            _OOoCalc_WriteCell($openofficecalcwindow,$SheetList[$i],$TheDate,2,4)
        EndIf

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()
    MsgBox(0,$CellAddress,$CellValue)
    Return
EndFunc

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

Share this post


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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $TheDate = GUICtrlRead($Date1)
            MsgBox(0,"",$TheDate)
            $OOspreadsheet = _OOoCalcBookNew()
            _OOoCalcBookSaveAs($OOspreadsheet,"C:\new.ods")
            _OOoCalcBookClose($OOspreadsheet)
            $OOspreadsheet = _OOoCalcBookOpen("C:\new.ods")
            Sleep(1000)
            _OOoCalcWriteCell($OOspreadsheet,$TheDate,"A1")
    EndSwitch
WEnd

Edit: My issue was resolved right here:

 

Edited by keafter
Resolution

Share this post


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