[Solved] Advanced Filter Macro is Too Slow

Creating a macro - Writing a Script - Using the API

[Solved] Advanced Filter Macro is Too Slow

Postby Math » Thu Jan 24, 2019 1:21 am

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.
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
Math
 
Posts: 86
Joined: Mon Oct 29, 2018 6:32 pm

Re: Advanced Filter Macro is Too Slow

Postby RusselB » Thu Jan 24, 2019 2:05 am

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5080
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Advanced Filter Macro is Too Slow

Postby John_Ha » Thu Jan 24, 2019 2:33 am

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
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   Expand viewCollapse view
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


Listing 17 says
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   Expand viewCollapse view
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
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6612
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Advanced Filter Macro is Too Slow

Postby Math » Fri Jan 25, 2019 5:53 pm

[Solved]

> This topic has been solved . :)


hugs .
LibreOffice 5.4.4.2 on Windows 7
Math
 
Posts: 86
Joined: Mon Oct 29, 2018 6:32 pm

Re: [Solved] Advanced Filter Macro is Too Slow

Postby Villeroy » Sat Jan 26, 2019 5:00 pm

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.

Why? He only wants someone to do his work.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26720
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Advanced Filter Macro is Too Slow

Postby Math » Sun Jan 27, 2019 9:10 pm

I used the macro sub UseAnAdvancedFilter of the sr. John_Ha

thank you very much sr. John_Ha :super:


friend hug .
LibreOffice 5.4.4.2 on Windows 7
Math
 
Posts: 86
Joined: Mon Oct 29, 2018 6:32 pm

Re: [Solved] Advanced Filter Macro is Too Slow

Postby Villeroy » Sun Jan 27, 2019 11:00 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26720
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Advanced Filter Macro is Too Slow

Postby John_Ha » Mon Jan 28, 2019 1:24 am

Math wrote:I used the macro sub UseAnAdvancedFilter of the sr. John_Ha

When all else fails reading the manual is often your best bet. :super:
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6612
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests