Ask Your Question
0

Error in script to remove preset columns from Calc by Macro

asked 2017-09-11 14:50:25 +0100

this post is marked as community wiki

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 ... (more)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2017-09-11 15:38:44 +0100

librebel gravatar image

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

please try instead the following approach :

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
edit flag offensive delete link more

Comments

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

Philip Owen gravatar imagePhilip Owen ( 2017-09-11 22:16:47 +0100 )edit
0

answered 2017-09-11 22:21:54 +0100

Philip Owen gravatar image

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

edit flag offensive delete link more
0

answered 2017-09-11 15:52:49 +0100

JohnSUN gravatar image

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
oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, OpenProp())

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)

edit flag offensive delete link more

Comments

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

Philip Owen gravatar imagePhilip Owen ( 2017-09-11 22:15:28 +0100 )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.

JohnSUN gravatar imageJohnSUN ( 2017-09-12 07:31:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-11 14:50:25 +0100

Seen: 182 times

Last updated: Sep 11 '17