Ask Your Question

wrighch's profile - activity

2020-10-29 14:09:37 +0100 commented answer How to set SortDescriptor for a DatabaseRange

Thanks for your input @newbie-02. You are right: It's difficult. If any relevant documentation exists, I haven't found

2020-10-29 14:08:17 +0100 commented answer How to set SortDescriptor for a DatabaseRange

Thanks for your input @newbie-02. You are right: It's difficult. If any relevant documentation exists, I haven't found

2020-10-28 20:45:23 +0100 asked a question How to set SortDescriptor for a DatabaseRange

How to set SortDescriptor for a DatabaseRange I have a workbook in which I have used Calc's menu to define a DatabaseRan

2020-10-25 18:16:48 +0100 asked a question Find AutoFilter State from macro

Find AutoFilter State from macro In a simple Calc Sheet with AutoFilter enabled, both this question from 2013 and this o

2020-10-22 14:24:01 +0100 received badge  Notable Question (source)
2020-10-21 21:47:55 +0100 commented answer calc access "__Anonymous_Sheet_DB__0" from macro

Thanks for the reply @newbie-02. As you might note from my recent post here I accepted an answer that allowed me to do

2020-10-21 14:00:27 +0100 marked best answer Macro to manipulate an AutoFilter without a Database Range

I have a classic simple spreadsheet with several columns and several rows.

image description

The first row of data contains field names – Labels. The remaining rows are filled with data. I manually select the labels in the cells in the first row of data, then use the Calc menu to create an AutoFilter by clicking Data > AutoFilter. This creates the simple filter with pull-down controls in each column as expected. The range for the filter created in this way implicitly includes only the rows which contain data – rows 7 to 11. And it works exactly as expected.

Sometimes, after manipulating the filter selections for the various columns, I end up with a somewhat complex filter comprising different settings for more than a few columns. I’d like a macro, configured to the ‘execute action’ of the ‘Clear Filter’ Button, to simply clear all the filter settings and revert to an unfiltered display.

Note, to the extent that it is important, that the first several rows of the sheet DO NOT include ‘data’ of interest to the filter. Note also that I have NOT defined a Calc Database using Data > Define Range. I simply selected the Labels on row 6 and created the AutoFilter.

I know that I can create a Database Range and use a FilterDescriptor and FilterFields to do all sorts of things, including clearing the filter settings. But is a Database Range absolutely required? I dislike creating names and data that are not absolutely required because they increase the complexity of a workbook and end up making maintenance more difficult. Is it possible to manipulate these AutoFilter settings WITHOUT CREATING A DATABASE RANGE?

This recent post by @newbie-02 asks essentially the same question. The one suggested answer, posted by @erAck, indicates that some sort of a hidden Database Range is created by Calc. He then refers to an apparently unpublished XUnnamedDatabaseRange interface. Though I dislike the idea of using unpublished APIs, this answer is intriguing enough to pursue, if only to give me a better understanding of how to manipulate Calc’s filters. Sadly, the answer gets into a level of abstraction that is beyond my abilities. Can anyone offer a code example that would instantiate the XunnamedDatabaseRange interface and allow the use of its hasByTable and getByTable methods?

2020-10-21 13:59:49 +0100 commented answer Macro to manipulate an AutoFilter without a Database Range

The answer by @erAck in the link I provided in my OP (this link) indicates: "These anonymous ranges can not be enumerate

2020-10-21 13:52:09 +0100 commented answer Macro to manipulate an AutoFilter without a Database Range

Very nice example @mauricio. But I decided to go with the code @Schiavinatto proposed.

2020-10-20 23:32:08 +0100 received badge  Popular Question (source)
2020-10-20 20:46:30 +0100 commented question Macro to manipulate an AutoFilter without a Database Range

Good question @mauricio. But I don't want my macro to do anything more than clear the filters. Setting the AutoFilter

2020-10-20 20:10:44 +0100 commented answer Macro to manipulate an AutoFilter without a Database Range

Very nice @schiavinatto. Your example is elegant in its simplicity. It's wonderful when someone helps me move outside

2020-10-20 19:30:15 +0100 asked a question Macro to manipulate an AutoFilter without a Database Range

Macro to manipulate an AutoFilter without a Database Range I have a classic simple spreadsheet with several columns and

2020-10-20 14:55:15 +0100 commented answer calc access "__Anonymous_Sheet_DB__0" from macro

@newbie-02, I'm trying to do exactly the same thing as you -- use a macro to manipulate an AutoFilter which has no expli

2020-10-20 13:05:56 +0100 marked best answer Macro to create multi-value Calc AutoFilter

I'm trying to develop a macro that allows me to define and save AutoFilter settings so that I can quickly impose complex filters on a sheet. The sheet holds a database with 17 columns, Headers in the first row, and AutoFilter in place. Here is some prototype code that imposes a filter showing rows where column A EQUALS "Value1" and column K CONTAINS "Value2":

Sub CreateFilter

Dim theRange As Object          ' A Range Object
Dim theFilterDesc As Object     ' A FilterDescriptor Object
Dim theFilterFields() As Object     ' An Array of FilterField objects
Dim aFilterField As Object      ' A single item for the FilterFields Array

'   Get the Database Range we're going to filter
theRange = ThisComponent.getPropertyValue("DatabaseRanges").getByName("ImportDatabase")

ReDim theFilterFields(1)        ' Redimension the FilterFields array

aFilterField = New com.sun.star.sheet.TableFilterField  ' Create a FilterField
aFilterField.Connection = 0     ' 0 = AND, 1 = OR (irrelevant for item 0)
aFilterField.IsNumeric = FALSE      ' Define an EQUAL filter
aFilterField.StringValue = "Value1"
aFilterField.Field = 0
aFilterField.Operator = com.sun.star.sheet.FilterOperator.EQUAL
theFilterFields(0) = aFilterField   ' Add the first FilterField to the FilterFields array

aFilterField.Connection = 0     ' Define a CONTAINS filter
aFilterField.IsNumeric = FALSE  
aFilterField.StringValue = "Value2"
aFilterField.Field = 10
aFilterField.Operator = com.sun.star.sheet.FilterOperator2.CONTAINS
theFilterFields(1) = aFilterField   ' Add the second FilterField to FilterFields array

theFilterDesc = theRange.getFilterDescriptor(TRUE)  ' Get an EMPTY FilterDescriptor
With theFilterDesc          ' Set
    .ContainsHeader = TRUE      '  the
    .CopyOutputData = FALSE     '   Properties
    .IsCaseSensitive = FALSE    '    of the
    .UseRegularExpressions = FALSE  '     FilterDescriptor
End With                '      itself
theFilterDesc.setFilterFields(theFilterFields)  ' Place the FilterFields array into the descriptor
theRange.refresh()          ' Finally, invoke the filter

End Sub

That code works fine when trying to filter Column A to a SINGLE value. The issue I'm struggling with is trying to create a filter where, for example, Column A can equal any of MULTIPLE values.

If I use the Calc user interface to manually create and apply such a filter, I simply click the Column-A AutoFilter pull-down, and check the boxes to select the multiple values of interest.

Examining the FilterDescriptor that Calc creates for that filter, I see that the multiple values are declared within theRange.FilterDescriptor.FilterFields3(0).Values(n), where each Values(n) declares the properties for a single value selected with a checked box.

I simply cannot figure out how to properly declare or populate a TableFilterField for such a filter or how to add a TableFilterField3 with multiple Values to theFilterFields array.

Additional Info added following my initial post:

I can create a TableFilterField3 and set its properties using:

Dim aFilterField3 As Object 
aFilterField3 = New com.sun.star.sheet.TableFilterField3
aFilterField3.Connection = 0                                        
aFilterField3.Field = 10
aFilterField3.Operator = com.sun.star.sheet.FilterOperator2.EQUAL

And I could conceivably add it using theFilterDesc.setFilterFields3(theFilterFields). But aFilterField3 gets created with an empty array for its aFilterField3.Values property and I cannot figure out how to re-dimension that property so I can populate it with values. And, even if I could dimension the Values property array to allow me to assign a separate Values entry for each of the multiple ... (plus)

2020-10-20 12:42:12 +0100 received badge  Famous Question (source)
2020-10-17 19:48:13 +0100 marked best answer Display update incomplete after cell-invoked macro Function modifies multiple cells

Disclaimer: I realize the question is rather vague and open-ended? So, if this is not the appropriate forum in which to ask such questions, please let me know and I’ll happily re-post elsewhere. Also, since the issue is complex by its very nature, providing a Simple Self-Contained Example is a contradiction in terms. I’m asking for thoughts and suggestions from others who understand the interactions between the calculation engine and the Basic runtime library better than I do. That said...

I have written a Basic macro Function MyFunction. Cell A2 contains the formula =MyFunction(A1). When I manually enter a new value in cell A1, MyFunction is invoked and MyFunction’s return value then appears in cell A2 as expected.

However, in addition to displaying the return value in the cell from which it is invoked, I would like MyFunction to make changes to multiple cells on multiple sheets. When I do that, the other cells are properly updated per the macro code as expected. But Calc sometimes (but not always) fails to display the changes to one or more of those other cells until I manually toggle to another sheet within the workbook and back. I am told this is because Calc cannot know which cells the macro code might modify and, therefore, cannot determine which other cells may need to be re-calculated or re-displayed and the correct order in which to perform the re-calculations. Invoking ThisComponent.calculateAll does not seem to help.

Is it possible to force Calc to refresh the display of all cells? Is what I’m trying to do impossible? Any suggestions would be appreciated.

2020-10-17 19:48:03 +0100 commented answer Display update incomplete after cell-invoked macro Function modifies multiple cells

In my comment of 04 Oct, I promised more info. Here it is. I now believe my 04 Oct statement that the “prohibition is

2020-10-17 17:17:48 +0100 edited answer How to remove focus from a Calc ListBox Control

IThe following discussion replaces my original answer to this question. My first ‘solution’ was to preceded the UNO:GoT

2020-10-17 17:17:20 +0100 edited answer How to remove focus from a Calc ListBox Control

I found an answer that appears to work. I merely preceded the above "UNO:GoToCell" dispatch command with an "UNO:Toggle

2020-10-16 21:28:10 +0100 edited answer How to remove focus from a Calc ListBox Control

I found an answer that appears to work. I merely followed the above "UNO:GoToCell" dispatch command with an "UNO:FocusC

2020-10-16 19:49:09 +0100 marked best answer How to remove focus from a Calc ListBox Control

I have placed a ListBox Control on a Calc Sheet and assigned a Basic Macro to the control's 'Changed' event. The last thing the macro does is place the cursor on a desired cell on the sheet using: dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args()) Everything works as desired except: After the macro completes execution, the ListBox sometimes retains focus, but sometimes it does not. By 'retains focus' I mean that the ListBox displays the dotted-line around the box (indicating focus) and the selected item shown in the box is highlighted with a blue background. Since the Control still has focus, pressing the up/down arrow keys causes the previous/next item in the list to be selected and triggers the 'Changed' event again, and the ListBox retains focus. The Control's TabStop property is set to 'No'. So when the ListBox has focus, pressing the Tab key does not remove focus from the Control.

After macro completion, if I manually use the mouse to select any cell on the sheet, the ListBox immediately loses focus, the dotted lines in the Control disappear, the background of the selected item reverts to the white background, and pressing the arrow keys moves the 'cursor' to the next cell above or below the selected cell.

Behavior is the same regardless of if I select a ListBox item using the mouse to pull-down and click a selection, or if I use the arrow keys to simply move up or down in the selection list. I have tried using ThisComponent.CurrentController.Select(cell) in combintion with the above 'dispatch' command and separately with no change in behavior: Sometimes the ListBox retains focus when the macro completes, and sometimes it does not.

Can anyone suggest a way for me to dependably remove focus from the ListBox Control after it is used to select an item? I have been unable to identify a specific sequence of actions or macro code that will dependably predict whether the control will retain focus or not.

2020-10-16 19:49:09 +0100 received badge  Scholar (source)
2020-10-16 19:48:58 +0100 answered a question How to remove focus from a Calc ListBox Control

I found an answer that appears to work. I merely changed the UNO dispatch command from "UNO:GoToCell" to "UNO:FocusCell

2020-10-16 14:05:31 +0100 asked a question How to remove focus from a Calc ListBox Control

How to remove focus from a Calc ListBox Control I have placed a ListBox Control on a Calc Sheet and assigned a Basic Mac

2020-10-05 15:15:08 +0100 received badge  Notable Question (source)
2020-10-04 18:56:08 +0100 commented answer Display update incomplete after cell-invoked macro Function modifies multiple cells

I have just begun trying to create an example. But my existing workbook is so extensive that it will take me some time

2020-10-04 18:56:08 +0100 received badge  Commentator
2020-10-04 16:08:15 +0100 received badge  Popular Question (source)
2020-10-04 15:39:29 +0100 commented answer Display update incomplete after cell-invoked macro Function modifies multiple cells

Thank you for the link. Based on my various attempts, it would seem that the referenced prohibition is more general and

2020-10-04 13:44:41 +0100 received badge  Student (source)
2020-10-04 13:36:21 +0100 edited question Display update incomplete after cell-invoked macro Function modifies multiple cells

Cell display incorrect after cell-invoked macro Function modifies multiple cells Disclaimer: I realize the question is r

2020-10-04 13:23:20 +0100 asked a question Display update incomplete after cell-invoked macro Function modifies multiple cells

Cell display incorrect after cell-invoked macro Function modifies multiple cells Disclaimer: I realize the question is r

2020-08-30 15:05:23 +0100 received badge  Enthusiast
2020-08-29 15:55:13 +0100 commented question Macro to create multi-value Calc AutoFilter

Thanks again @newbie-02. But tdf#70883 doesn't apply to what I'm doing. It's Title ("uno: copy" via the dispatcher doe

2020-08-28 21:10:25 +0100 commented answer Macro to create multi-value Calc AutoFilter

@mauricio provided an answer which works. But it did not answer my question. Per my Original Post: "(my original) code w

2020-08-28 17:34:25 +0100 received badge  Self-Learner (source)