OOoCalc.au3 Copy Sheet to same workbook?

[g0gcd]

I've hunted high and low but can't find the exact solution to my problem. I'm trying to use the UDF to execute the Sheet Move/Copy command.

I have a fairly complex workbook containing graphics, charts and data in one sheet, which I use as a template. I want to be able to open the book, copy the template sheet to a new sheet and populate it with the data for item one, then copy the template again to another new sheet and populate that with the data for item two, and so-on. I finally delete the template sheet and save the workbook as my 'Results' file.

No, I don't know how many items I need to add (yes, I did think of just repeating the template sheet many times and deleting the unused ones)

It's a doddle with Excel but I can't find a similar command in OOoCalc.au3.

I an trying to avoid Excel as I can't guarantee that the target system will have Excel installed (and therefore using OpenOfficeCalc to do the manipulation).

Any pointers please?

Thanks 

John

(PS the UDF doesn't have a version number but the most recent change is dated 2016/11/16)

 

[donnyh13]

I don't use OpenOffice, but LibreOffice. But this should work. I gave an example using the UDF and one without if you vary your Template size and don't want to update the used range.

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

#include "C:\Autoit Scripts\Autoit\AutoIt stuff Jan 2022\LibreOffice Writer UDF Creation files\OOoCalc.au3"
#include "C:\Autoit Scripts\Autoit\AutoIt stuff Jan 2022\LibreOffice Writer UDF Creation files\OOoCalcConstants.au3"

Global $oCalc = _OOoCalc_BookNew()
ConsoleWrite(@error & @TAB & @extended & @CRLF)

_OOoCalc_SheetAddNew($oCalc)
ConsoleWrite(@error & @TAB & @extended & @CRLF)

_OOoCalc_WriteCell($oCalc, "Testing", 0, 0)
ConsoleWrite(@error & @TAB & @extended & @CRLF)

; If you know the range covered by your template:
; Copy range A1:f30 from sheet1 (0 Position), to sheet in 1 position (Sheet2) starting at cell A1.
_OOoCalc_RangeMoveOrCopy($oCalc, "A1:F30", "A1", 1, 0, 1)
ConsoleWrite(@error & @TAB & @extended & @CRLF)

; If you do not know/it is variable, the range covered by your template:
Global $sTemplate = "Sheet1"; Template Sheet Name
Global $sDest = "NewSheet"; MUST BE UNIQUE for the Book (No other sheets with the same name.)
Global $iInsertPosition = $oCalc.Sheets.Count(); Insert position in Sheets, in this case, at the end.

    $oCalc.Sheets.copyByName($sTemplate, $sDest, $iInsertPosition)

 

Edited December 27, 2024 by donnyh13

[g0gcd]

@donnyh13 thanks for the prompt reply.

I emulated this approach using the native LibreOffice controls (copying the cells and pasting) and it didn't bring the links between the charts and the cells over properly.

What I did (and should have done before asking) was to read the link information in the UDF. By changing very few lines of code, I have modified _OOoCalc_SheetMove() and created _OOoCalc_SheetCopy() to do exactly what I need to do. The key is $oObj.Sheets.moveByName(). Calling $oObj.Sheets.copyByName() instead, with the additional parameter, does the trick.

I do need to tidy up the error checking on the new parameter but I can now move on! 

Thanks for your help

John

 

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name ..........: _OOoCalc_SheetCopy
; Description ...: Copy the specified sheet to another specified position.
; Syntax ........: _OOoCalc_SheetMove(ByRef $oObj, $sSheet, $sNewSheet, $iPosition)
;                  _OOoCalc_SheetCopy(ByRef $oObj, $iPosition, $sNewSheet, $vSheet = -1)
; Parameters ....: $oObj      - Calc object opened by a preceding call to _OOoCalc_BookOpen, _OOoCalc_BookNew, or
;                               _OOoCalc_BookAttach
;                  $sSheet    - Name of the sheet to move
;                  $sNewSheet - New sheet name
;                  $iPosition - Position in which to move sheet.
;                  $vSheet    - [optional] Worksheet, either by index (0-based) or name.
;                                          Default is -1, which would use the active worksheet.
; Return values .: On Success - Returns 1
;                  On Failure - Returns 0 and sets @error:
;                  |@error    - 0 ($_OOoCalcStatus_Success)         = No Error
;                  |          - 3 ($_OOoCalcStatus_InvalidDataType) = Invalid Data Type
;                  |          - 5 ($_OOoCalcStatus_NoMatch)         = No Match
;                  |@extended - Contains Invalid Parameter Number
; Author ........: GMK
; Modified ......: g0gcd 27/12/2024
; Remarks .......: Modification of _OOoCalc_SheetMove(). No error checking on $NewSheet
; Related .......: _OOoCalc_SheetAddNew, _OOoCalc_SheetDelete, _OOoCalc_SheetNameGet, _OOoCalc_SheetNameSet, _OOoCalc_SheetList, _OOoCalc_SheetActivate, _OOoCalc_SheetMove
; Link ..........: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XSpreadsheets.html#moveByName
;                  http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XSpreadsheets.html#copyByName
; Example .......: No
; ===============================================================================================================================
Func _OOoCalc_SheetCopy(ByRef $oObj, $iPosition, $sNewSheet, $vSheet = -1)
    Local $oOOoCalc_COM_ErrorHandler = ObjEvent("AutoIt.Error", __OOoCalc_ComErrorHandler_InternalFunction)
    #forceref $oOOoCalc_COM_ErrorHandler
    If Not IsObj($oObj) Then Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    If Not IsInt($iPosition) Then Return SetError($_OOoCalcStatus_InvalidDataType, 2, 0)
    If Not IsInt($vSheet) And Not IsString($vSheet) Then Return SetError($_OOoCalcStatus_InvalidDataType, 3, 0)
    If $vSheet > -1 And Not __OOoCalc_WorksheetIsValid($oObj, $vSheet) Then Return SetError($_OOoCalcStatus_NoMatch, 3, 0)
    If IsInt($vSheet) Then $vSheet = ($vSheet < 0) ? (_OOoCalc_SheetNameGet($oObj)) : ($oObj.getSheets.getByIndex($vSheet).name)
    $oObj.Sheets.copyByName($vSheet, $sNewSheet, $iPosition)
    Return SetError($_OOoCalcStatus_Success, 0, 1)
EndFunc   ;==>_OOoCalc_SheetCopy

 

[donnyh13]

You're welcome g0gcd, glad you got it working!

[g0gcd]

One final comment...

Having got it working, I find that OpenOffice Calc copies a sheet but leaves the chart links still pointing to the original sheet data, so it's not the right solution for me.

I have another approach which has all of the items on one sheet, so I'll go there. Thanks again for your time

[g0gcd]

OK, final note on the subject:

I test radio sets and am doing that by driving an analyser under remote control (using AutoIT). There's a range of items (cables/relays/connectors and attenuators) involved and I have measurement results at a range of frequencies for each of these. What I'm trying to achieve is a certificate type document with graphs of those measurements per item. The test results for the radio go happily in one sheet, together with calculations and explanatory graphics. I am trying to use the graphing abilities of Excel (other tools are available) to draw the graphs for the other involved items. 

OpenOffice4 doesn't carry over the links when copying a range of cells. I use this to my advantage. On my template sheet, I fill the data table with the source data for one item and that updates the graph associated with it. Using _OOoCalc_RangeMoveOrCopy() i then copy the completed graph and table to its place in the report sheet. The links are broken and the data stays with the copy. I can then go back to the template, reset the data and draw the graph for the next item, and copy that to the next free place in the report sheet. And so on until all are done. Then delete the template and save/print to pdf.

Works like a charm. Also explains why copying a sheet (as above) didn't work - the graph drawing links are left behind (I'm guessing that it emulates Excel's Copy as Values)

Thanks again for your steer

Great forum!

Regards

John G0GCD