Spaltenbreite in mehreren Tabellenblättern festlegen

[Woatze]

Hallo!
Ich möchte gerne in einer Calc-Datei die Breite von verschiedenen Spalten festlegen.
So sollen in den Tabellenblättern 4-16 die Spalten folgende Breite haben:
A = 1cm
B = 1,5cm
die Spalten C bis H alle 2cm
I = 5cm
J = 1cm
K = 3cm
L - AE 0,8cm
AF - AK 0,5cm
AL - AS 1cm.

Ist das irgendwie machbar?

Vielen Dank schon mal für die Hilfe,
Woatze

[komma4]

Ist das irgendwie machbar?

Bemühe die (Google-)Suche: spaltenbreite basic site:de.openoffice.info 223 Treffer

[Woatze]

Hallo!

Ich hab jetzt nachfolgenden Code zusammengebastelt. Jetzt werden auf dem gerade aktiven Tabellenblatt die Breiten der Spalten entsprechend angepasst. Da das ganze doch recht umfangreich ist, wär meine Frage: Kann man das irgendwie einfacher (kürzer) fassen?
Da sich ja alles eigentlich immer wiederholt, dachte ich ob es vielleicht eine Möglichkeit gibt, wo man vorab die Werte angeben kann und dann eine Schleife abgearbeitet wird.

Code: Alles auswählen

Sub BreiteSetzen

    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 ----------------------------------------------------------------------
    dim argsA(0) as new com.sun.star.beans.PropertyValue
    argsA(0).Name = "ToPoint"
    argsA(0).Value = "A1:A1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsA())
    rem ----------------------------------------------------------------------
    dim argsAA(0) as new com.sun.star.beans.PropertyValue
    argsAA(0).Name = "ColumnWidth"
    argsAA(0).Value = 3000
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsAA())

    rem ----------------------------------------------------------------------
    dim argsb(0) as new com.sun.star.beans.PropertyValue
    argsB(0).Name = "ToPoint"
    argsB(0).Value = "B1:B1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsB())
    rem ----------------------------------------------------------------------
    dim argsBB(0) as new com.sun.star.beans.PropertyValue
    argsBB(0).Name = "ColumnWidth"
    argsBB(0).Value = 380
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsBB())

    rem ----------------------------------------------------------------------
    dim argsC(0) as new com.sun.star.beans.PropertyValue
    argsC(0).Name = "ToPoint"
    argsC(0).Value = "C1:H1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsC())
    rem ----------------------------------------------------------------------
    dim argsCC(0) as new com.sun.star.beans.PropertyValue
    argsCC(0).Name = "ColumnWidth"
    argsCC(0).Value = 1440
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsCC())

    rem ----------------------------------------------------------------------
    dim argsI(0) as new com.sun.star.beans.PropertyValue
    argsI(0).Name = "ToPoint"
    argsI(0).Value = "I1:I1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsI())
    rem ----------------------------------------------------------------------
    dim argsII(0) as new com.sun.star.beans.PropertyValue
    argsII(0).Name = "ColumnWidth"
    argsII(0).Value = 4720
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsII())

    rem ----------------------------------------------------------------------
    dim argsJ(0) as new com.sun.star.beans.PropertyValue
    argsJ(0).Name = "ToPoint"
    argsJ(0).Value = "J1:J1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsJ())
    rem ----------------------------------------------------------------------
    dim argsJJ(0) as new com.sun.star.beans.PropertyValue
    argsJJ(0).Name = "ColumnWidth"
    argsJJ(0).Value = 500	
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsJJ())
		
    rem ----------------------------------------------------------------------
    dim argsK(0) as new com.sun.star.beans.PropertyValue
    argsK(0).Name = "ToPoint"
    argsK(0).Value = "K1:K1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsK())
    rem ----------------------------------------------------------------------
    dim argsKK(0) as new com.sun.star.beans.PropertyValue
    argsKK(0).Name = "ColumnWidth"
    argsKK(0).Value = 2340	
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsKK())

    rem ----------------------------------------------------------------------
    dim argsL(0) as new com.sun.star.beans.PropertyValue
    argsL(0).Name = "ToPoint"
    argsL(0).Value = "L1:L1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsL())
    rem ----------------------------------------------------------------------
    dim argsLL(0) as new com.sun.star.beans.PropertyValue
    argsLL(0).Name = "ColumnWidth"
    argsLL(0).Value = 550
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsLL())

    rem ----------------------------------------------------------------------
    dim argsM(0) as new com.sun.star.beans.PropertyValue
    argsM(0).Name = "ToPoint"
    argsM(0).Value = "M1:AE1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsM())
    rem ----------------------------------------------------------------------
    dim argsMM(0) as new com.sun.star.beans.PropertyValue
    argsMM(0).Name = "ColumnWidth"
    argsMM(0).Value = 1750
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsMM())

    rem ----------------------------------------------------------------------
    dim argsAF(0) as new com.sun.star.beans.PropertyValue
    argsAF(0).Name = "ToPoint"
    argsAF(0).Value = "AF1:AS1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsAF())
    rem ----------------------------------------------------------------------
    dim argsAFAF(0) as new com.sun.star.beans.PropertyValue
    argsAFAF(0).Name = "ColumnWidth"
    argsAFAF(0).Value = 1140
    dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsAFAF())
			
End Sub

Grüße
Woatze

[Stephan]

z.B.:

Code: Alles auswählen

Sub BreiteSetzen

    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")

    Spalten = Array("A1:A1", "B1:B1", "C1:H1", "I1:I1", "J1:J1", "K1:K1", "L1:L1", "M1:AE1", "AF1:AS1")
    Breiten = Array(3000, 380, 1440, 4720, 500, 2340, 550, 1750, 1140)
    
    rem ----------------------------------------------------------------------
    dim argsA(0) as new com.sun.star.beans.PropertyValue
    argsA(0).Name = "ToPoint"
    rem ----------------------------------------------------------------------
    dim argsAA(0) as new com.sun.star.beans.PropertyValue
    argsAA(0).Name = "ColumnWidth"

    For i = LBOUND(Spalten()) To UBOUND(Spalten())
       argsA(0).Value = Spalten(i)
       dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, argsA())
    
       argsAA(0).Value = Breiten(i)
       dispatcher.executeDispatch(document, ".uno:ColumnWidth", "", 0, argsAA())
   Next i

End Sub

Gruß
Stephan

[Woatze]

Guten Morgen, Stephan!
Vielen Dank für deine Hilfe. Hab ich mir schon gedacht, dass es einfacher geht, aber so weit reichen meine Fähigkeiten leider (noch) nicht.
Viele Grüße und vielen Dank,
Woatze