[Solved] Search multiple words at once

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

[Solved] Search multiple words at once

Post by Math »

Greetings ,

         here is an excel vba code that searches for words "Palavra(0), Palavra(1), Palavra(2) ... etc", in column "C" of worksheet 6 .
         when you find any of the words, then write "Atenção" in column "AA" .
         I need to convert this vba code to a LibreOffice BASIC code, that is, I need to make it work in LibreOffice .

Code: Select all

Sub search_words()
Dim Col As Variant
Dim Palavra(3) As String

Let Palavra(0) = "AM"
Let Palavra(1) = "FM"
Let Palavra(2) = "Radio"

With Sheets("Plan6")
    For x = 2 To .Cells(Cells.Rows.Count, "C").End(xlUp).Row
        If .Cells(x, "C") Like "*" & Palavra(0) & "*" Or .Cells(x, "C") Like "*" & Palavra(1) & "*" _
            Or .Cells(x, "C") Like "*" & Palavra(2) & "*" Then
            .Cells(x, "AA") = "Atenção"
        End If
    Next
End With
End Sub
Note :
         1) there may be more than three words to search for .
         2) must also search for parts of the word, that is, search for sub-words or pieces of words .
         3) need to search on filtered lines with auto filter, ie in visible lines .


hugs .
Last edited by Math on Sun Jan 06, 2019 8:09 pm, edited 2 times in total.
LibreOffice 5.4.4.2 on Windows 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search multiple words at once

Post by JeJe »

Code: Select all

for i = 0 to 2
if instr(1,cell.string,Palavra(i)) <>0 then
cell2.string="Atenção"
exit for
end if
next
Add an outer loop for your cells using getCellRangeByName or getCellByPosition.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Search multiple words at once

Post by Sébastien C »

Hello everybody and Happy New year for all; I’m back...

Very interesting questions that open up worlds. But JeJe passed before me while writing... However, I think I will go further in my answer since JeJe do not deal with the issue of the problem dealt with by [Cells.Rows.Count, "C"] of VBA...

You will find below the translation of your macro. It SEEMS MUCH easier in VBA but do not rely too quickly on appearances. It is certain that the function knowLastLine is a real torture since I do not know the equivalent of [Cells.Rows.Count, "C"]. But the possibility of using a native function of Calc is a topic mentioned recently on this forum, and, in your case, it is very timely. For example, just like in the spreadsheet as shown below, you can use regular expressions and go much further in power than with the somewhat limited InStr instruction of standard Basic.

Similarly, I find more elegant to put your words in a table in a line, rather than declare a list of variables. Finally, I do not like the "IF" with "OR" without end as a condition separator. I prefer to rest the question as many times as there are words, for example in a "FOR" loop.

However...
You would win, it seems to me, to know the SEARCH function of the spreadsheet (PROCURAR/PESQUISAR en português/bresil)
as well as the regular expressions that it accepts without the slightest worry... With a little "Atenção", you should open doors that would allow you to do without complicated macros...

So in the attached file (which contains the translated macro of your VBA), I added things that you can study in column G. Your spreadsheet will automatically translate the functions of the formulas contained in the cells of this column in your language (the Portuguese I think, no?). But they were seized in French to be read in English...

God forbid us to speak German as elsewhere!

abraçar
:lol:

Code: Select all

Option Explicit

Sub searchWords()
 Dim    myCell As Object
 Dim myColumnC As Long, myColumnAA As Long
 Dim    myLine As Integer,  myWord As Integer
 Dim Palavra() As String,  mySheet As String, myWarning As String

    Palavra = array("AM", "FM", "Radio")
    mySheet = "Plan6"
  myColumnC = 2                                                             '  2 is column  "C" (index = 0)
 myColumnAA = 26                                                            ' 26 is column "AA" (index = 0)
  myWarning = "Atenção"

 For myLine = 1 to knowLastLine(myColumnC)
  myCell = thisComponent.Sheets.getByname(mySheet).getCellByPosition(myColumnC, myLine)

  If uBound(myCell.queryVisibleCells.rangeAddresses) > -1 Then              ' If the cell is visible.
   For myWord = 0 to uBound(Palavra)
    If mySearchNatCalcCellfunction(Palavra(myWord), myCell.string) > 0 Then ' If the word is IN the string's cell.
     thisComponent.Sheets.getByname(mySheet).getCellByPosition(myColumnAA, myLine).string = myWarning
     Exit For
    End if
   Next myWord
  End if
 Next myLine
End Sub


' Use the native function SEARCH (PROCURAR en português) of Calc.
Function mySearchNatCalcCellfunction(myFindText As String, myText As String)
 Dim myFunctionAccess As Object

 On Error Goto myError

            myFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
 mySearchNatCalcCellfunction = myFunctionAccess.callFunction("SEARCH", Array(myFindText, myText))
 Exit Function

 myError:
 mySearchNatCalcCellfunction = -1
End Function


' Go around the world to know the Grail (Vá ao redor do mundo para conhecer o Graal).
Function knowLastLine(myColumn As Long)
 Dim      myDispatcher As Object
 Dim knowLastLineSheet As Long
 Dim      myOptions(1) As New com.sun.star.beans.PropertyValue

      myDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 myOptions(0).Name = "Sel" : myOptions(0).value = false

 With thisComponent.currentController                                     ' [Ctrl] + [End] for going deeply...
  myDispatcher.executeDispatch(.Frame, ".uno:GoToEndOfData", "", 0, myOptions())

  ' Go to the column with the API (yeah!).
  knowLastLineSheet = thisComponent.currentSelection.cellAddress.row + 1  ' Note the End of datas plus one line.
  thisComponent.currentController.select(thisComponent.currentSelection.spreadsheet.getCellByPosition(myColumn, knowLastLineSheet))

  ' Go to Up.
  myOptions(0).Name = "By" : myOptions(0).Value = 1 : myOptions(1).Name = "Sel" : myOptions(1).Value = false
  myDispatcher.executeDispatch(.Frame, ".uno:GoUpToStartOfData", "", 0, myOptions())
  

  knowLastLine = thisComponent.currentSelection.cellAddress.row           ' Note the last line of the block with the API (yeah!).
  ' Return up with [Ctrl] + [↖] (Home).
  myDispatcher.executeDispatch(.Frame, ".uno:GoToStart"    , "", 0, myOptions())
 End With
End Function
Attachments
searchWords.ods
(11.65 KiB) Downloaded 295 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Search multiple words at once

Post by Zizi64 »

Dear Math,

Why you never try to convert/rewrite your silly VBA codes first? :crazy:
Study the API functions, and try it itself!! Please upload your StarBasic/API code first.
(We will help you.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search multiple words at once

Post by JeJe »

I was doing the middle way between Sébastien C and Zizi64... deliberately providing a partial solution. There's the old saying about teaching a man to fish rather than giving one fish.

Getting stuck and spending maybe hours looking for the solution yourself may seem like a waste of time compared with posting in a forum... it isn't though... you learn how to work things out yourself... which in the long run is more valuable...

Edit:

I'll add that there have been several occassions when I've been stuck and thought about posting for some help.. but decided not to and to perservere... and then found what I wanted... and its probably taken less time than posting and waiting for a reply would have. The solutions for OO are nearly always either there in the documentation or someone else has posted a similar question before.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Search multiple words at once

Post by mikele »

Hi math,
you can use a searchdescriptor (similar to the replaceDescriptor, recommended to you here viewtopic.php?f=20&t=96169#p460789)
to find all the cells with the required content.
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

French cooking

Post by Sébastien C »

@Zizi64, JeJe :
So it seems that the doors opened by our friend Math are infinitely more multiple than those leading only to code... So I must clarify one thing... I am aware that I went further than a partial solution consciously deliberated by JeJe. The question raised is therefore in the educational option chosen by me, just as deliberately... There are those who think that it is imperative to provide an effort, and others who opt for the incentive to desire... without forgetting to serve myself because, for me, "the horrible function knowLastLine" that I provided can still be subject to debate.

Let's go back, if you will, in more procedural ways:

Our friend Math asks a question here, as he has asked many times elsewhere (and not only on the English-speaking forum), a bit like "doing his market".

The first option, the most common, the one chosen systematically on the French-speaking forum, is to reproach or even insult him. To me, it's totally ineffective.
The second option, the one I choose, is diametrically opposed. I take HIS problem to make MY problem. I sprinkle all the thoughts a little salty, a little fun, just to give taste, as in cooking (French, thank you, I know).

Either our friend is a gourmet (same word in French) and he will understand that the dish can be tasted, appraise, extend. He will provide the effort and may even ask us more since as Zizi64 says very well:
Zizi64 wrote:(We will help you.)
Either our friend is a greedy ("gourmand" in French): he will serve hisself, but he is clearly an idiot. Let us bring him in this case our amused compassion, of that which is much more insupportable than the reproaches uttered which are not heard.

In both cases why not extend the question on what is the subject of this forum, namely, THE CODE??? If the pupil is poor enough not to want to understand the master's teaching, does he have to share this same poverty by persuading himself that he has nothing to learn or to give to his fellows? In other words, to respond to the rudeness of our friend Math, is it not possible to understand that if I insisted on "the horrible function knowLastLine", it is, also, to give, to each, the possibility of keeping a certain height of speech without falling into the reproaches that, in any case, have already been made elsewhere?

Rest assured however: if I intervene here, it is because unlike the French part of this forum, I judge normal and intelligent moderation, I mean capable of something other than systematic repressive. But we would go further in the subtlety that everyone would be, in my humble opinion, more winners than we are yet, anyway...

Dear Zizi64, JeJe (and other Villeroy...), is French cuisine really the one to disgust you?

:lol:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search multiple words at once

Post by JeJe »

I wasn't trying to criticise anyone, either Math's post or anyone giving a full solution to it. I think Zizi64 is giving some good advice... its critical but well intentioned.

There seem to be very few people writing macros in OO, despite a lot of users. That's probably the steep learning curve - so anyone who's making an effort to learn should just be encouraged. People are lucky in the high ratio of helpers here to people who post problems... three approaches to solving this problem in this thread for example. In a lot of other places its many posts going unanswered. People learn from both the questions and people's answers.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Search multiple words at once

Post by Sébastien C »

@JeJe
I agree all of what you write here! And it is not because I propose a third educational solution, different of yours and the one of Zizi64, that I consider you both as bad teachers... And I also know that I am read by the one that I criticize maybe more than you two... But in the case of my two previous posts of this thread, I give our friend Math two possibilities: either by macro, or without macro. Then, I inform him that we can consider too that between consumerism and sharing, the nuance of taste can be to his advantage. It would take our friend Math for a fool to imagine someone who knows how to ask questions in French or in English, while his language is Portuguese, may not be able to read not only the words, but everything which is their true meaning. In that, I do not think I disrespect our friend Math, but just only situate myself in a perspective that is a little harder than the one expressed by Zizi64... This may make you reconsider the fact that, contrary to what you seemed to believe, I never forget to give without waiting in return...
:mrgreen: :lol: ;)
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Search multiple words at once

Post by Math »

SOLVED .

                   thank you very much sr. Sébastien C, your solution was great and perfect . :bravo:

Congratulations on your knowledge ! :bravo:

                   Thank you very much for your help, I can now continue with my project . :super:


hug friend .
LibreOffice 5.4.4.2 on Windows 7
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

You see me happy for you Math. :D

However, even if it is assumed that you will have little desire to help us for simplify "the horrible function knowLastLine" that I have reluctantly provided, can you tell us if you have taken the trouble to explore the solution without macro explained above?

I say that because, you know how it is: we too can be interested in your own experiences returns...

:mrgreen:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] Search multiple words at once

Post by mikele »

Hi,
another solution (without the dispatcher):

Code: Select all

Sub Main
	oDoc=ThisComponent
	oTab=odoc.sheets(6)
	oRg=oTab.columns(2)
	oDesc = oRg.createsearchDescriptor()
	oDesc.setSearchString("AM|FM|Radio")
	oDesc.SearchRegularExpression = true
	afound =oRg.findall(oDesc).rangeAddresses
	for i=0 to ubound(afound)
		for k=afound(i).startrow to afound(i).endrow
			oTab.getcellbyposition(26,k).string="Atenção"
		next
	next
End Sub
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

Code: Select all

oRg=oTab.columns(2)
I did not knew that!

Code: Select all

afound =oRg.findall(oDesc).rangeAddresses
A GREAT IDEA !!!! I am so happy!

Many thanks mikele, it is SO MUCH BETTER of my produce!!! I love to learn !
:bravo: :bravo: :bravo:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] Search multiple words at once

Post by mikele »

Hi Sébastien,
I love to learn
me too ;)

Code: Select all

oRg=oTab.columns(2)
is the short way of

Code: Select all

oRg=oTab.columns.getByIndex(2)
You can also do this:
or

Code: Select all

oRg=oTab.columns.getByName("C")
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

Of course mikele; of course! But if it is acquired, the consequence of your notification is that I can then make a drastic reduction of my horrible function knowLastLine()!!! It's just huge for me, in many other contexts than the one exposed by our friend Math. Because it allows an efficient equivalent of VBA [Cells.Rows.Count, "C"] without having to go around the world with the dispatcher to discover the Grail. This is an idea I have been looking for a long time ago with the API. And it obviously works with both columns and lines. This is something that can be very often searched for and whose VBA expression is very simple. When we do operations on the lines and there are empty cell "holes", it becomes extremely useful to know where it is necessary to stop!

I just wrote a function knowLastCell() in the "Code Snippets" chapter of this forum that takes into account both types (search on a column or on a line). It is very simple since you just have to get the last arrayAddresses array. And that is very influenced by your proposal...

Many thanks again !
:)
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] Search multiple words at once

Post by mikele »

Hi,
there is another way to find this out.
Instead of

Code: Select all

 myDescriptor = myBlock.createsearchDescriptor()
  myDescriptor.setSearchString(".") : myDescriptor.searchRegularExpression = True
       myFound = myBlock.findall(myDescriptor).rangeAddresses

  With myFound(uBound(myFound))
   Select Case myType
    Case 0 : knowLastCell = .endRow
    Case 1 : knowLastCell = .endColumn
   End Select
  End With
use simply

Code: Select all

myFound = myBlock.queryemptycells.rangeAddresses

  With myFound(uBound(myFound))
   Select Case myType
    Case 0 : knowLastCell = .startRow-1
    Case 1 : knowLastCell = .startColumn-1
   End Select
  End With
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

Always interesting; I did not knew this queryEmptyCells's object again...

A link was already pointing to this thread that will recall the first draft in your own precedent post. It still presents the interest, through the use of regular expressions of the search, to be able to look for something other than non-empty cells (eg pure numbers). But the queryemptycells's object is obviously more suitable for the Code Snippet's title. I have, off course, updated.

Failing to know if our friend Math has progressed in his study of his column AA without macro, this small function seems to me to be very useful in a considerable number of contexts ...

Vielen dank mikele!
:)
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Search multiple words at once

Post by Zizi64 »

Dear Zizi64, JeJe (and other Villeroy...), is French cuisine really the one to disgust you?
I never will eat snail... :D :lol:
My favorite is the pig. ;) :super:

Read: I will not post anything to Math (helps or criticism) - even if his behavior is very annoying for me, when I read his posts.
I will not post anything to him, until I can not see ANY effort to learning the things what are basicly necessary for him, for his JOB.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

Zizi64 wrote:I never will eat snail... :D :lol:
You are wrong ; with a butter and parsley sauce, it's absÔlutely delÎcious!!!
Zizi64 wrote:I can not see ANY effort to learning the things
I finally understood ... but a little late I have to confess. :roll: I sweat blood and water with the English language just for try to be understandable. :knock:

Thanks to mikele for making this thread an object of instruction :super: Even if I understand your annoyances, it remains that we are still the winners ...
:fist: :fist: :fist:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Search multiple words at once

Post by Zizi64 »

Zizi64 wrote:
I can not see ANY effort to learning the things

I finally understood ... but a little late I have to confess. :roll: I sweat blood and water with the English language just for try to be understandable. :knock:
English words in Hungarian word order an with Hungarian logic... Sorry, I can not thinking in English. I can thinking only in Hungarian... (And it was translated without the Google translator...)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] Search multiple words at once

Post by mikele »

Most important: we understand each other :super:
It's great to experience this world wide teamwork!
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] Search multiple words at once

Post by Sébastien C »

Zizi64 wrote:Sorry, I can not thinking in English. I can thinking only in Hungarian...
You do not have, obviously, the slightest apologise to say, since you know, of course, how it is perfectly shared!

Fun to see that finally, it's also a bit like the teaching of mikele. I did not know the objects columns (from sheets) or queryemptycells (from columns or rows). Xray (or RMI of course) can learn vocabulary from a language (here, the API). But they are just objects in the form of words. The thought of mikele (or yours Zizi64) is one of those forged by years of practice in your profession... And this is where we can judge that I am (really) not a computer scientist but a pÔÔr organ builder, certainly "enlightened amateur" of these things of computer science, but just able to spawn horrors to circumvent my ignorance. In this case, mikele has less opened the door of the objects columns and queryemptycells than the "object thinking", in itself.

Indeed, he first defines a search area, then acts on it, which again returns a sub-area. This cascade of ranges is a thought that I am far from controlling; I still have very procedural reflexes inherited from my youth, very GWBasic... It's quite fascinating to realize that what is most important, in a language, even formal, is not the words themselves, but their structure and architecture that makes sense. And to speak of cooking, finally, with words that have nothing to do with relational psychology, was to go beyond the barrier of our common languages to go to the real object. This litteraire form is finally more powerful than use smileys...And this allowed mikele to intervene, which, judged "from Sirius's point of view" (so, in height), is also something, almost recursive programming...

For any simple "amateur" that I stay, even "enlightened", this thread brings to others this supernumerary information of the poverty of consumption. The one who did not bother to answer the simple questions they were asked, has lost a lot by his consumerism, which is now likely to be not answered by myself if I have to see it again. The big winner of this thread, the one who received the most, is myself, not because I (anyway) asked you very clearly to tell me more about my horrible function, but because I made the effort to read you!

Bravo to all, even to those who will never risk eating snails!!!
:bravo:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply