Error in script to remove preset columns from Calc by Macro

English
, , ,
#1

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 in *.csv format. There is (or can be) only one sheet.

end sub

#2

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
#3

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

#4

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)

#5

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

#6

I know that someday later my variant of macro will come in handy too. :wink: 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.

#7

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

#8

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.