0


I've created the following macros in Calc which loops through a specified row, searches for the pre-defined headers and then deletes their entire column (including the header), the code works, relevant columns do get deleted, however

I've discovered a flaw. In case any of the headers from the pre-defined list are missing from the file I get an error message "Search key not found" and then some other unrelated column gets deleted instead (for e.g. the code is searching for "Text1", "Text2", "Text3" headers however if "Text2" isn't located an error message would pop-up and some unrelated column is deleted).

There should be some kind of validation check that would automatically skip to the next value in the loop if one isn't present in this file.

Conditions:

  • Headers row is always 7.
  • List item Relevant columns can appear anywhere in the mentioned row.

  • If the header is located the entire column should be deleted (no
    blank column should remain afterwards), if the some headers are missing the code should automatically move on to the next
    search value.

I've found some Excel code examples that should do what I need however they are unusable in my case since Calc/Excel code is slightly different.

Any help is greatly appreciated.

sub DeleteSystemFields
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem switch to active cell A7

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem search conditions

dim args4(17) as new com.sun.star.beans.PropertyValue
args4(0).Name = "SearchItem.StyleFamily"
args4(0).Value = 2
args4(1).Name = "SearchItem.CellType"
args4(1).Value = 0
args4(2).Name = "SearchItem.RowDirection"
args4(2).Value = true
args4(3).Name = "SearchItem.AllTables"
args4(3).Value = false
args4(4).Name = "SearchItem.Backward"
args4(4).Value = false
args4(5).Name = "SearchItem.Pattern"
args4(5).Value = false
args4(6).Name = "SearchItem.Content"
args4(6).Value = false
args4(7).Name = "SearchItem.AsianOptions"
args4(7).Value = false
args4(8).Name = "SearchItem.AlgorithmType"
args4(8).Value = 1
args4(9).Name = "SearchItem.SearchFlags"
args4(9).Value = 65536

rem search criteria parameters - corresponds to args4(10) in the next section

args4(10).Name = "SearchItem.SearchString"
args4(10).Value = ""
args4(11).Name = "SearchItem.Locale"
args4(11).Value = 255
args4(12).Name = "SearchItem.ChangedChars"
args4(12).Value = 2
args4(13).Name = "SearchItem.DeletedChars"
args4(13).Value = 2
args4(14).Name = "SearchItem.InsertedChars"
args4(14).Value = 2
args4(15).Name = "SearchItem.TransliterateFlags"
args4(15).Value = 1280
args4(16).Name = "SearchItem.Command"
args4(16).Value = 3
args4(17).Name = "Quiet"
args4(17).Value = true

rem search values start

args4(10).Name = "SearchItem.SearchString"

args4(10).Value = "Text1" 

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())


args4(10).Name = "SearchItem.SearchString"

args4(10).Value = "Text2"

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

args4(10).Name = "SearchItem.SearchString"


args4(10).Value = "Text3" 

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())


end sub
R3uK
  • 14,417
  • 7
  • 43
  • 77
user1420414
  • 1
  • 1

1 Answers1

0

Not sure but try this

found = dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

If found.result = True  then
    dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())
End If

FOLLOWUP

Try this

args4(10).Name = "SearchItem.SearchString"
args4(10).Value = "Text1"

found = dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

If found.result = True  then

    dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

    dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

End If
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks for the reply, this seem to work, whenever one of search criteria (header name) is missing from my predefined list it doesn't delete a non relevant column instead, however now only the **header** gets deleted and the rest of the column is still there. Do you know how to fix it (in my original macro it was deleting the whole column)? – user1420414 May 29 '12 at 11:20
  • What happens if you try calling this line `dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())` again inside the if part? – Siddharth Rout May 29 '12 at 11:23
  • Like this? `args4(10).Name = "SearchItem.SearchString" args4(10).Value = "TEXT" found = dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4()) If found.result = True then dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4()) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array()) End If` – user1420414 May 29 '12 at 11:31
  • Sorry no, the column itself remains. – user1420414 May 31 '12 at 07:06
  • Unfortunately it's still affecting only the header cell and not the whole column. – user1420414 Jun 02 '12 at 13:25
  • I am afraid this is where I think I have to give in :) I am not an expert with `OO`. I was pleasantly surprised that I could manage to solve 1st part of your problem. I guess you will have to wait for an `OO` expert to help you with the 2nd part of the problem :( – Siddharth Rout Jun 02 '12 at 13:51
  • Thanks for your help, I'll keep searching for a solution. – user1420414 Jun 02 '12 at 14:57