Sign in to follow this  
Followers 0
Wombat

_OOoCalc vs _Excel

2 posts in this topic

#1 ·  Posted (edited)

Alright, I've been tearing my mind apart trying to get this to work, and afer many failures I'm here to ask the gracious members of the Autoit community for a little help.

Here's what I'm trying to accomplish:

producing the same exact effect from this code:

Func _Excel_LogData();Logs the job data to \LogBook.xlsx for management
    Local $oExcelLocal = _ExcelBookOpen($LocalPath, 0)
    Local $oExcelSrv = _ExcelBookOpen($SrvPath, 0)

    Local $aArray[14] = [@MON & "/" & @MDAY & "/" & @YEAR, GUICtrlRead($Input1), GUICtrlRead($Input2), GUICtrlRead($Input5), $Remake, GUICtrlRead($Combo1), $1A, GUICtrlRead($Date1), GUICtrlRead($Combo2), GUICtrlRead($Input7)]


    For $i = 1 To 10
        $cell = _ExcelReadCell($oExcelLocal, $i, 1)
        If $cell = "" Then
            _ExcelWriteArray($oExcelLocal, $i, 1, $aArray)
            ExitLoop
        EndIf

    Next

    For $i = 1 To 10
        $cell = _ExcelReadCell($oExcelSrv, $i, 1)
        If $cell = "" Then
            _ExcelWriteArray($oExcelSrv, $i, 1, $aArray)
            ExitLoop
        EndIf

    Next

    _ExcelSheetNameSet($oExcelLocal, $WrkStn)
    _ExcelBookSave($oExcelLocal)
    _ExcelBookClose($oExcelLocal)
    Sleep(10)
    _ExcelSheetNameSet($oExcelSrv, $WrkStn)
    _ExcelBookSave($oExcelSrv)
    _ExcelBookClose($oExcelSrv)
    Sleep(10)

EndFunc

With this code:

Func _Libre_LogData();Logs the job data to \LogBook.xlsx for management
    Local $LibreLocal = _OOoCalcBookOpen($LocalPath)
    Local $LibreSrv = _OOoCalcBookOpen($SrvPath)

    Local $aArray[14] = [@MON & "/" & @MDAY & "/" & @YEAR, GUICtrlRead($Input1), GUICtrlRead($Input2), GUICtrlRead($Input5), $Remake, GUICtrlRead($Combo1), $1A, GUICtrlRead($Date1), GUICtrlRead($Combo2), GUICtrlRead($Input7)]


    For $i = 1 To 10
        $cell = _OOoCalcReadCell($LibreLocal, $i, 1)
        If $cell = "" Then
            _OOoCalcWriteCell($LibreLocal, $aArray, $i)
            ExitLoop
        EndIf

    Next

    For $i = 1 To 10
        $cell = _OOoCalcReadCell($LibreSrv, $i, 1)
        If $cell = "" Then
            _OOoCalcWriteCell($LibreSrv, $aArray, $i)
            ExitLoop
        EndIf

    Next

    _OOoCalcSheetNameSet($LibreLocal, $WrkStn)
    _OOoCalcBookSaveAs($LibreLocal, $LocalPath, "LogBook", True)
    _OOoCalcBookClose($LibreLocal)
    Sleep(10)
    _OOoCalcSheetNameSet($LibreSrv, $WrkStn)
    _OOoCalcBookSaveAs($LibreSrv, $SrvPath, "LogBook", True)
    _OOoCalcBookClose($LibreSrv)
    Sleep(10)

EndFunc

The Excel code produces the desired output perfectly. I need to replicate that using the _OOoCalc code...

One issue I'm having is I need the _OOoCalc code to open the book hidden, just as i have the Excel code doing. I had this code working, with the _OOoCalc code opening the book as hidden and producing the desired output but accidentally formatted by backup drive and lost the working code... revisited two months later and cannot remember for the life of me how i did it. I've scoured the depths of google and as deep into the Forum as I could go and haven't found a thing.

So, does anyone have a suggestion? ... please and thank you

(btw, my _OOoCalc code may be using the incorrect functions for the job, I believe that was the case with the lost code that it used different functions to produce the same output as the Excel code...)

Edited by Wombat

Just look at us.
Everything is backwards; everything is upside down. Doctors destroy health. Lawyers destroy justice. Universities destroy knowledge. Governments destroy freedom. The major media destroy information and religions destroy spirituality. ~ Michael Ellner


The internet is our one and only hope at a truly free world, do not let them take it from us...

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Update:

I was able to get the _OOoCalc code to write the output as desired, but it does not save, nor does it close... and I haven't a clue about opening it hidden. Here's the code so far:

New Update:

I'm able to use the following code to hide the window but it still flashes for a brief second... is there any way to avoid that brief flash?

Func _Libre_LogData();Logs the job data to \LogBook.xlsx for management
    Local $LibreLocal = _OOoCalcBookOpen($LocalPath)
    WinSetState("LogBook.xlsx", "", @SW_HIDE)
    Local $aArray[1][10] = [[@MON & "/" & @MDAY & "/" & @YEAR, GUICtrlRead($Input1), GUICtrlRead($Input2), GUICtrlRead($Input5), $Remake, GUICtrlRead($Combo1), $1A, GUICtrlRead($Date1), GUICtrlRead($Combo2), GUICtrlRead($Input7)]]

    For $i = 1 To 10
        $cell = _OOoCalcReadCell($LibreLocal, $i, 0)
        If $cell = "" Then
            _OOoCalcWriteFromArray($LibreLocal, $aArray, $i, 0)
            ExitLoop
        EndIf

    Next

    _OOoCalcSheetNameSet($LibreLocal, $WrkStn)
    _OOoCalcBookSave($LibreLocal)
    _OOoCalcBookClose($LibreLocal)
    Sleep(10)
EndFunc
Edited by Wombat

Just look at us.
Everything is backwards; everything is upside down. Doctors destroy health. Lawyers destroy justice. Universities destroy knowledge. Governments destroy freedom. The major media destroy information and religions destroy spirituality. ~ Michael Ellner


The internet is our one and only hope at a truly free world, do not let them take it from us...

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
Sign in to follow this  
Followers 0