Greetings ,
I'm working on a LibreOffice 5.4.4.2 file, whose approximate size is 30 MB .
I am using a macro that filters the information in the "A3:CA5000" cells of the "2.1Pedidos para Fat" spreadsheet based on criteria established in the "A1:CA2" cells that are placed in the "Data_Pedidos" worksheet, after Filtering, the values paste in the "Pedidos_Pendentes" worksheet from cell "A1" .
But, is too slow to do the operation, the macro is running more than an hour to complete the operation .
need to find an alternative to Minimize the time the macro takes to do the operation .
hugs.
[Solved] Advanced Filter Macro is Too Slow
[Solved] Advanced Filter Macro is Too Slow
Last edited by Math on Fri Jan 25, 2019 6:14 pm, edited 4 times in total.
LibreOffice 5.4.4.2 on Windows 7
Re: Advanced Filter Macro is Too Slow
Have you timed how long it takes to do the same operation using the manual controls (ie: using Data -> Filter) rather than the macro?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Advanced Filter Macro is Too Slow
Please upload small example files showing the filter rules and the data being filtered so that what you are doing can be analysed.
Also, please precisely explain your objective as there may be a better or faster way of doing what you want to do. For example, can you use any of the Database-specific functions described on page 339 of the Calc Guide?
Are any of the example filter macros given in Chapter 13 - Calc as a Simple Database of any help? For example, Listing 16 is
Listing 17 says
Also, please precisely explain your objective as there may be a better or faster way of doing what you want to do. For example, can you use any of the Database-specific functions described on page 339 of the Calc Guide?
Are any of the example filter macros given in Chapter 13 - Calc as a Simple Database of any help? For example, Listing 16 is
A simple sheet filter using two columns
Listing 16 demonstrates a more advanced filter that filters two columns and uses regular expressions. Some unexpected behavior occurred while working with Listing 16. Although you can create a filter descriptor using any sheet cell range, the filter applies to the entire sheet.
Code: Select all
Sub SimpleSheetFilter_2()
Dim oSheet ' Sheet to filter.
Dim oRange ' Range to be filtered.
Dim oFilterDesc ' Filter descriptor.
Dim oFields(1) As New com.sun.star.sheet.TableFilterField
oSheet = ThisComponent.getSheets().getByIndex(0)
oRange = oSheet.getCellRangeByName("E12:G19")
REM If argument is True, creates an
REM empty filter descriptor.
oFilterDesc = oRange.createFilterDescriptor(True)
REM Setup a field to view cells with content that
REM start with the letter b.
With oFields(0)
.Field = 0 ' Filter column A.
.IsNumeric = False ' Use a string, not a number.
.StringValue = "b.*" ' Everything starting with b.
.Operator = com.sun.star.sheet.FilterOperator.EQUAL
End With
REM Setup a field that requires both conditions and
REM this new condition requires a value greater or
REM equal to 70.
With oFields(1)
.Connection = com.sun.star.sheet.FilterConnection.AND
.Field = 5 ' Filter column F.
.IsNumeric = True ' Use a number
.NumericValue = 70 ' Values greater than 70
.Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
End With
oFilterDesc.setFilterFields(oFields())
oFilterDesc.ContainsHeader = False
oFilterDesc.UseRegularExpressions = True
oSheet.filter(oFilterDesc)
End Sub
Applying an advanced filter using a macro is simple (see Listing 17). The cell range containing the filter criteria is used to create a filter descriptor, which is then used to filter the range containing the data.
Code: Select all
Sub UseAnAdvancedFilter()
Dim oSheet 'A sheet from the Calc document.
Dim oRanges 'The NamedRanges property.
Dim oCritRange 'Range that contains the filter criteria.
Dim oDataRange 'Range that contains the data to filter.
Dim oFiltDesc 'Filter descriptor.
REM Range that contains the filter criteria
oSheet = ThisComponent.getSheets().getByIndex(1)
oCritRange = oSheet.getCellRangeByName("A1:G3")
REM You can also obtain the range containing the
REM filter criteria from a named range.
REM oRanges = ThisComponent.NamedRanges
REM oRange = oRanges.getByName("AverageLess80")
REM oCritRange = oRange.getReferredCells()
REM The data that you want to filter
oSheet = ThisComponent.getSheets().getByIndex(0)
oDataRange = oSheet.getCellRangeByName("A1:G16")
oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oDataRange.filter(oFiltDesc)
End Sub
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Re: Advanced Filter Macro is Too Slow
[Solved]
> This topic has been solved .
hugs .
> This topic has been solved .
hugs .
LibreOffice 5.4.4.2 on Windows 7
Re: [Solved] Advanced Filter Macro is Too Slow
Why? He only wants someone to do his work.John_Ha wrote:Please upload small example files showing the filter rules and the data being filtered so that what you are doing can be analysed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Advanced Filter Macro is Too Slow
I used the macro sub UseAnAdvancedFilter of the sr. John_Ha
thank you very much sr. John_Ha
friend hug .
thank you very much sr. John_Ha
friend hug .
LibreOffice 5.4.4.2 on Windows 7
Re: [Solved] Advanced Filter Macro is Too Slow
Magic spells copied from the internet
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Advanced Filter Macro is Too Slow
When all else fails reading the manual is often your best bet.Math wrote:I used the macro sub UseAnAdvancedFilter of the sr. John_Ha
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.