[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I get this macro to work with libreoffice calc?

Posted on 2011-10-28
3
Medium Priority
?
2,912 Views
Last Modified: 2012-05-12
Hello Experts.

I have the following macro that works with Excel, but I would like to have a version that works with libreOffice as well.  I get an error with "dim r as Range" when I try to run it in libreOffice Calc.  It says "Basic syntax error.  Unknown data type Range".  Here's the macro:

Sub RemoveSelectColumns()
    Dim iColumn As Integer
    Dim strCell As String
    Dim strColumnName As String
    Dim r As Range
    
    iColumn = 1
    Do
        strCell = Cells(1, iColumn).Value
        If strCell = "" Then
            Exit Do
        End If
        If UCase(strCell) = "MOE" Then
            ' case-independent
            strColumnName = Split(Cells(1, iColumn).Address, "$")(1)
            Columns(strColumnName & ":" & strColumnName).Delete Shift:=xlToLeft
        Else
            iColumn = iColumn + 1
        End If
    Loop
End Sub

Open in new window


Any help would be greatly appreciated.
0
Comment
Question by:ferguson_jerald
3 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 37045790
I think you use

oSheets = ThisComponent.getSheets()
r
= oSheet.getCellRangeByName("A2:C5")

for example.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37046758
Delete line 5. You never use r anyway.
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 37048639
The LibreOffice/OpenOffice objects are different and need to be handled differently.  Try this code as I think it does what the Excel Macro did.  If not, let us know:

Sub RemoveSelectColumns()
    Dim iColumn As Integer
    Dim strCell As String
    Dim strColumnName As String
    Dim objSheet as object
    
    ' This Gets the First WorkSheet
    objSheet = ThisComponent.getSheets().getByIndex(0)

    ' Set to First Column
    iColumn = 0
    Do
        strCell = objSheet.getCellbyPosition(iColumn, 0).String
        If strCell = "" Then
            Exit Do
        End If
        If UCase(strCell) = "MOE" Then
        	' Remove the current Column
		objSheet.getColumns().removeByIndex(iColumn, 1) 
        Else
            iColumn = iColumn + 1
        End If
    Loop
End Sub

Open in new window


-Bear
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question