I have data that is exported from another program into an excel spreadsheet. I have a LibreOffice ODS spreadsheet that I am working with where the data from the initial export gets pasted in to Sheet1, some calculations are made, then the data from two specific columns on the Sheet1 gets copied to Sheet2 (including a column of the original data and the column with the calculation results.
My problem is that there are lines within the initial data that gets copied that I don’t need. Those lines contain either “Total”, “Category”, or “Department”, but they are mixed into the initial data in random places depending on the initial report. I can design a formula that checks for those unwanted lines and does not copy them, but this leaves blank lines, which is less than optimal. I can design a filter that will eliminate the lines containing the unwanted data, but it has to be run manually, and my users are less than adept with anything on the computer, so this also is less than optimal. In an effort to eliminate the unwanted data altogether, I am looking into trying to make a macro that will filter out those lines that contain any of those keywords. Due to the capabilities of the users, I would like to eventually make the macro run through the use of a button linked to the macro, but I have that part figured out. It’s the macro itself that has me stumped.
I copied the macro Pierre-Yves Samyn built and posted here, then did some digging to try to find out how to convert it from CONTAINS to the opposite. I found that I should be able to use DOES_NOT_CONTAIN in place of CONTAINS, so I put that in place, changed the keywords and tried to run it. It does not work. I found documentation on DOES_NOT_CONTAIN on the following page:
http://api.libreoffice.org/docs/idl/r…
It also exists here:
https://www.openoffice.org/api/docs/c…
Here is a small sample of the data I am working with:
UF Chocolate Milk .5 gal
UF Intense Chocolate Milk 1 pt
UF Intense Chocolate Milk 1 qt
UF Intense Vanilla Milk 1 pt
UF Milk 1% .5 galu
UF Milk 1% 1 gal
UF Milk 2% .5 gal
UF Milk 2% 1 gal
UF Milk 2% 1 pt
UF Milk 2% 1 qt
UF Milk Homo .5 gal
UF Milk Homo 1 pt
UF Milk Homo 1 qt
UF Milk Homo Gal
UF Milk Skim .5 gal
UF Milk Skim 1 qt
UF Mint Chocolate Chip Milk 1 pt
UF Ult Half & Half 1 qt
Total Category: Dairy
Category: New Age Type: Retail
UF Orange Juice .5 gal
Total Category: New Age
Total Store: 1
Grand Total
Note that the line “UF Orange Juice .5 gal” falls between several lines that contain the keywords I am trying to filter out.
Here is the macro with the changes I made:
sub PysFilter
dim oSheet as object, oRange as object, oFilter as object
dim oFilterField(3) As New com.sun.star.sheet.TableFilterField2
oSheet = thisComponent.Sheets.getByName("Build Up")
oRange = oSheet.getCellRangeByName("A4:A500")
oFilter= oRange.createFilterDescriptor(true)
'oRange.filter(oFilter)
oFilterField(0).Field = 1
oFilterField(0).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(0).StringValue = "Total"
oFilterField(1).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(1).Field = 1
oFilterField(1).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(1).StringValue = "Category"
oFilterField(2).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(2).Field = 1
oFilterField(2).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(2).StringValue = "Department"
with oFilter
.ContainsHeader = true
.CopyOutputData = false
.IsCaseSensitive = false
.UseRegularExpressions = false
.filterFields2 = oFilterField()
end with
oRange.filter(oFilter)
end sub
Can anyone offer any advice? I have taken classes on coding, so I have at least a minor understanding of what this does, but I don’t know the API, so I’m not sure why this isn’t working. For what it’s worth, I have installed the LibreOffice SDK. Per the documentation, I also have the optional Java SDK installed, as well as the required .NET (version 4.6), G++ Compiler, zip, and cat and sed tools. Nothing seems to make a difference. Any help at all will be greatly appreciated!
Thank you!