Ask Your Question

Philip Owen's profile - activity

2018-06-05 19:14:18 +0100 received badge  Famous Question (source)
2017-12-18 10:45:47 +0100 received badge  Notable Question (source)
2017-09-12 07:20:45 +0100 received badge  Popular Question (source)
2017-09-11 22:23:45 +0100 marked best answer Error in script to remove preset columns from Calc by Macro

I am a complete beginner with LibreOffice Calc. I regularly download large spreadsheets, over a hundred columns and up to 75,000 records. They have exactly the same structure every time. I need to remove unused columns, the same ones every time, in order to make the dataset small enough to use with my database management software. (For the curious. It is Lotus Approach and uses *.DBF files. In the long term I am transitioning to Base but need a solution for the next 6 to 9 months).

I have reviewed the Open Office and Libre Office forums. There are macro based solutions to deleting columns. However, I have been unable to get them to work for me.

I have no idea about Basic. I recorded a macro to delete a single column. I substituted the column index, starting at zero and the number of columns to delete from any column index position I happen to be using. Starting with the last group of columns to be deleted, so I don't have to adjust the index after every deletion.

I receive the error message "BASIC runtime error. Object variable not set." Are there any suggestions as to how I deal with this? The code is below.

REM BASIC

Sub Main

End Sub

sub BDeleteColumn 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 ---------------------------------------------------------------------- REM The line below was recorded by the macro recorder. It deleted a single column. I have substituted this line by repetitions of the same command. REM dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array())

REM So I have substituted the First column to be deleted and the number of columns to be deleted in the array. REM I assume that column numbering starts at 0 REM I receive the error message "BASIC runtime error. Object variable not set." REM How do I set an object variable? Where do I find the right syntax? dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(98, 5)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(92, 5)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(81, 2)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(66, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(54, 8)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(47, 5)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(42, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(37, 3)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(35, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(33, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(17, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(14, 1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(6,1)) dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(0, 4))

REM I have tried the "oColumns.removeByIndex(x,y)" command instead of dispatcher.executeDispatch(document, ".uno:DeleteColumns", "", 0, Array(x,y)) but I end up with the same error. Any suggestions? REM The file is ... (more)

2017-09-11 22:23:45 +0100 received badge  Scholar (source)
2017-09-11 22:21:54 +0100 answered a question Error in script to remove preset columns from Calc by Macro

This is what I ended up with. If I had know about the DeleteColumns function, I would have used that. sub DeleteColu

2017-09-11 22:16:47 +0100 commented answer Error in script to remove preset columns from Calc by Macro

Thanks. This is very close to my final solution. See below.

2017-09-11 22:15:28 +0100 commented answer Error in script to remove preset columns from Calc by Macro

Thank yo for doing this. I solved it a different way (see below). Filters are obviously a high priority on my learning c

2017-09-11 14:50:25 +0100 asked a question Error in script to remove preset columns from Calc by Macro

Error in script to remove preset columns from Calc by Macro I am a complete beginner with LibreOffice Calc. I regularly