OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Delete specific columns based on their header.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
MX
Newbie
Newbie


Joined: 26 May 2012
Posts: 1

PostPosted: Sat May 26, 2012 4:01 pm    Post subject: Delete specific columns based on their header. Reply with quote

Hi,
I've created the following macros 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.
    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.



Any help is greatly appreciated.


Code:
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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun May 27, 2012 2:31 am    Post subject: Reply with quote

Stop recording stupid displatch macros. This can not work properly. Why cant you simply use the software as it has been designed to be used?
Use the find/replace tool to find the matching headers. Column deletion works with a multiple selection.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group