# Error in script to remove preset columns from Calc by Macro [closed]

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 ...

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-22 07:47:00.860444

Sort by » oldest newest most voted

The elements in the arguments array passed to executeDispatch() should be of type com.sun.star.beans.PropertyValue.

Sub Main
DeleteColumns( 98, 5 )
DeleteColumns( 92, 5 )
DeleteColumns( 81, 2 )
DeleteColumns( 66, 1 )
DeleteColumns( 54, 8 )
DeleteColumns( 47, 5 )
DeleteColumns( 42, 1 )
DeleteColumns( 37, 3 )
DeleteColumns( 35, 1 )
DeleteColumns( 33, 1 )
DeleteColumns( 17, 1 )
DeleteColumns( 14, 1 )
DeleteColumns( 6,1 )
DeleteColumns( 0, 4 )
End Sub

Sub DeleteColumns( lColumnIndex As Long, lCount As Long )
REM Deletes <lCount> Columns from the current Sheet, starting at column <lColumnIndex>.
Dim oSheet As Object, oColumns As Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oColumns = oSheet.getColumns()
oColumns.removeByIndex( lColumnIndex, lCount )
End Sub

more

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

( 2017-09-11 22:16:47 +0200 )edit

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

sub DeleteColumns rem ---------------------------------------------------------------------- rem Manually remove all blank sheets and save *.csv file before starting this. (I can do this automatically now) rem define variables dim document as object dim sheet as object

document = ThisComponent sheet = document.sheets(0)

        sheet.columns.removeByIndex(98, 5)
sheet.columns.removeByIndex(92, 5)
sheet.columns.removeByIndex(81, 2)
sheet.columns.removeByIndex(66, 1)
sheet.columns.removeByIndex(54, 8)
sheet.columns.removeByIndex(47, 5)
sheet.columns.removeByIndex(42, 1)
sheet.columns.removeByIndex(37, 3)
sheet.columns.removeByIndex(35, 1)
sheet.columns.removeByIndex(33, 1)
sheet.columns.removeByIndex(17, 1)
sheet.columns.removeByIndex(14, 1)
sheet.columns.removeByIndex(6,  1)
sheet.columns.removeByIndex(0,  4)


end sub

more

If the file is in *.csv format then you NOT NEED delete columns AFTER load, you can do it BEFORE. Please look this page - Filter Options

With this info your macro will be very small, something like as

Dim sUrl As String
sUrl = convertToURL("C:\FakePath\some_data.csv")
Dim OpenProp(1) as New com.sun.star.beans.PropertyValue
OpenProp(0).name="FilterName"
OpenProp(1).name="FilterOptions"
OpenProp(0).value="Text - txt - csv (StarCalc)"
OpenProp(1).value="44,34,25,1,1/9/2/9/3/9/4/9/7/9/15/9/18/9/36/9/38/9/39/9/40/9/43/9/48/9/49/9/50/9/51/9/52/9/55/9/56/9/57/9/58/9/59/9/60/9/61/9/62/9/67/9/82/9/83/9/93/9/94/9/95/9/96/9/97/9/99/9/100/9/101/9/102/9/103/9,1031,false,true"
If Not FileExists(sUrl) Then Exit Sub


In other words, you just need to list the values of "column number / 9 (hidden)" and use this string as the CSV file import filter parameter.

NB Thoroughly check this code on various data - there is a possibility that not all rows will be read into the table (see bug 90653)

more

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

( 2017-09-11 22:15:28 +0200 )edit

I know that someday later my variant of macro will come in handy too. ;-) Nevertheless, remember the reading error of the CSV-file! Even if you do not explicitly use the filter, Calс uses it all the time. And so there is no certainty that all lines of your file are read into a table. This happens on large and very large files. Well, now you know.

( 2017-09-12 07:31:03 +0200 )edit