OpenOffice.org: macro help

问题:

In OOo Calc

I need to copy a column (only the values, not the format) from one sheet to another (in the same worksheet) using a macro assigned to a button.

I browsed a bit around but found nothing significant :-(

回答1:

To answer the original question:

use a data array, which will be significally faster on large ranges of cells

Source = ThisWeek.getCellRangeByName("H12:H206")
source_data = Source.getDataArray()

Target = Steering.getCellRangeByName("M12:AU206").setDataArray(source_data())

回答2:

OK, I could build the answer and started learning OOo Basic, which I managed to avoid until now ;-)

I give it as is.

Sub UpdateThisWeek

Dim Doc As Object
Dim ThisWeek As Object
Dim Steering As Object
Dim Source As Object
Dim Target As Object
Dim Week as Integer

Doc = ThisComponent
ThisWeek = Doc.Sheets.getByName("This week")
Steering = Doc.Sheets.getByName("Steering")
Week = Steering.getCellByPosition(6,4).Value
Source = ThisWeek.getCellRangeByName("H12:H206")
Target = Steering.getCellRangeByName("M12:AU206").getCellRangeByPosition(Week-19,0,Week-19,194)

Dim i, s
For i = 0 To 194
    s = Source.getCellByPosition(0, i).Value
    If s > 0 Then
        Target.getCellByPosition(0, i).Value = s
    Else
        Target.getCellByPosition(0, i).String = ""
    End If
Next i

End Sub