I have a template document (.ots) with a macro that exports and saves the new file as .ods to a desired location. What i want to do is create a new document that gives me the average values across all the exported files, given that the number of files it’ll need to reference will increase as i use the template document more often so there will be more exported files over time. Is there any way to do this, like “average all the values in this given range on all the .ods documents in this folder”
the following macro returns the average of all the values in a given range on all the .ods documents in a given folder:
REM ***** BASIC ***** Option Explicit Function AverageFolder( strFolderPath As String, strCellRanges As String, Optional bCountBlankAsZero As Boolean ) As Double REM Returns the average of the given CellRanges in all spreadsheet files within a given Folder. REM <strFolderPath>: The full path to the Folder containing the spreadsheet files. REM This string should end with the path separator ( e.g. "/home/username/Desktop/MyFolder/" ). REM <strCellRanges>: String representing the CellRanges to be averaged. REM NB. The Sheet Name should be included for each CellRange, e.g. "Sheet1.A1:D5;Sheet2.A1:D5". REM NB. All referenced sheets must be present in all the spreadsheets within the given folder. REM <bCountBlankAsZero>: [OPTIONAL] Boolean indicating if Empty cells should be counted as 0 in the sum. REM True = Empty cells are counted as 0; REM False ( = Default ) = Empty cells are not counted in the sum. REM Example call from Calc: =AVERAGEFOLDER( "/home/username/Desktop/MyFolder/"; "$Sheet1.$A$1:$D$5"; 1 ) On Local Error Resume Next Dim oFileAccess as Object oFileAccess = createUnoService( "com.sun.star.ucb.SimpleFileAccess" ) If oFileAccess.Exists( strFolderPath ) AND oFileAccess.IsFolder( strFolderPath ) Then Dim oFunctionAccess As Object oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" ) Dim sFiles() As String sFiles = oFileAccess.getFolderContents( strFolderPath, False ) REM get files only. Dim aProps(0) As New com.sun.star.beans.PropertyValue REM Open the file invisible. aProps(0).Name = "Hidden" aProps(0).Value = True Dim dSum as Double, dAverage As Double Dim lNum as Long, lCountBlank As Long Dim i As Integer, j As Integer Dim oDoc As Object For i = 0 to uBound( sFiles ) oDoc = StarDesktop.loadComponentFromURL( sFiles(i), "_blank", 0, aProps() ) If Not IsNull( oDoc ) Then REM Check if doc is a valid spreadsheet: If oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then Dim aCellRanges() as Object REM This next line yields an IllegalArgument exception when <strCellRanges> REM contains a Sheet name that is not actually present in the spreadsheet. REM In that case this Function returns ZERO ( 0 ), REM ... provided that the line "On Local Error Resume Next" is not commented out. aCellRanges = oDoc.getSheets().getCellRangesByName( strCellRanges ) If uBound( aCellRanges ) >= 0 Then dSum = dSum + oFunctionAccess.CallFunction( "SUM", aCellRanges ) lNum = lNum + oFunctionAccess.CallFunction( "COUNTA", aCellRanges ) If Not IsMissing( bCountBlankAsZero ) And bCountBlankAsZero Then REM COUNTBLANK wants a single range ... For j = 0 To uBound( aCellRanges ) lNum = lNum + oFunctionAccess.CallFunction( "COUNTBLANK", Array( aCellRanges(j) ) ) Next j End If End If End If oDoc.Close( True ) End If Next i AverageFolder = dSum / lNum End If End Function
Virtually untested, please try if the above macro works for you…
With regards, lib
Thank you, you’ve been a life saver <3
you’re welcome @sleeperstakes,
If this answers your question, then please click on the mark until it turns green ( at the upper left area of the answer ).