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 14 hours ago 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