Spaltenbreite mit Office Script anpassen

[Tobi-GL]

Hallo allerseits,

zuerstmal: Ich bin dummer Endanwender mit ein paar rudimentären VBA-Kenntnissen, also verzeiht mir bitte meine Unwissenheit und blöde Rückfragen, vielen Dank vorab!

Zu meinem Problem:
Ich möchte eine wöchentlich aktualisierte, aber vom Aufbau gleichbleibende Liste idealerweise durch Ausblenden (oder zur Not Löschen) diverser Spalten, Anpassung einiger Spaltenbreiten in unterschiedliche Festwerte sowie Zeilenumbruch in Zeile 1, Linksbündiger Einzug in Spalte A für meinen Gebrauch optimieren. Dazu habe ich über den Excel-Reiter "Automataisieren" das entsprechende Script aufgezeichnet. Wenn ich das nun in einer "jungfräulichen" Liste ausführe wird auch das meiste erledigt, jedoch die Spaltenbreite wird scheinbar willkürlich verändert. Ich habe gelesen, wenn die Spaltenbreite unterschiedlich ist, wird "Null" gesetzt (was immer das heißt), aber das Problem besteht auch, wenn ich für die relevanten, ausgefüllten Spalten die gleiche Breite angebe.

Hier der Script Code:
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Delete range D:D on selectedSheet
  selectedSheet.getRange("D:D").delete(ExcelScript.DeleteShiftDirection.left);
  // Delete range P:W on selectedSheet
  selectedSheet.getRange("P:W").delete(ExcelScript.DeleteShiftDirection.left);
  // Delete range Y:Y on selectedSheet
  selectedSheet.getRange("Y:Y").delete(ExcelScript.DeleteShiftDirection.left);
  // Delete range AD:AF on selectedSheet
  selectedSheet.getRange("AD:AF").delete(ExcelScript.DeleteShiftDirection.left);
  // Set width of column(s) at range A:A on selectedSheet to 12
  selectedSheet.getRange("A:A").getFormat().setColumnWidth(12);
  // Set width of column(s) at range G:G on selectedSheet to 12
  selectedSheet.getRange("G:G").getFormat().setColumnWidth(12);
  // Set width of column(s) at range I:S on selectedSheet to 12
  selectedSheet.getRange("I:S").getFormat().setColumnWidth(12);
  // Set wrap text to true for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setWrapText(true);
  // Set text orientation to 0 for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setTextOrientation(0);
  // Indent set to 0 for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setIndentLevel(0);
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
  // Set wrap text to true for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setWrapText(true);
  // Set text orientation to 0 for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setTextOrientation(0);
  // Indent set to 0 for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().setIndentLevel(0);
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.general for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.general);
  selectedSheet.getRange("63:63").getFormat().setIndentLevel(0);
  // Set text orientation to 0 for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setTextOrientation(0);
  // Indent set to 0 for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setIndentLevel(0);
  // Unmerge range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").unmerge();
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  selectedSheet.getRange("63:63").getFormat().setIndentLevel(0);
  // Set text orientation to 0 for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setTextOrientation(0);
  // Indent set to 0 for range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").getFormat().setIndentLevel(0);
  // Merge range 63:63 on selectedSheet
  selectedSheet.getRange("63:63").merge(false);
}

Und so sieht das (fehlerhafte) Ergebniss aus:    



Kann jemand helfen?

LG

[Klaus-Dieter]

Hallo,

Kann jemand helfen?

ganz bestimmt nicht, wie wäre es, die Datei hier einzustellen?

[Tobi-GL]

Ja, das macht bestimmt Sinn!

Folgendes möchte ich automatiseren:
Spalte D, P - W, Y, AD - AF ausblenden (zur Not löschen)
Spalte A, G, I - AC : Breite 12
Spalte E: Breite 5
Spalte F: Breite 20
Zeile 1: Textumbruch
Spalte A: Linksbündig ausrichten

Ist das machbar?
  Test.xlsx (Größe: 10,11 KB / Downloads: 6)

[Fennek]

Hallo,

der gezeigte Code ist nicht VBA, wäre eine Lösung mit VBA möglich?

Die Datei ist eine echte Excel-Datei, also Libre-Office ist nicht erkennbar.

Die benutzten Excel-Versionen "versions| V 4/7" an einem Tag sind etwas ungewöhnlich.

mfg

PS: Mit Copy/paste werden alle Formate übertragen. In der Beispieldatei ein zweites Sheet anlegen und von dort den Code starten:

Code:

Dim WS As Worksheet

Set WS = ActiveSheet
Sheets(1).Range("A1:AF1").Copy
WS.Range("A1").PasteSpecial xlPasteAll

[maninweb]

Hallo,

das Problem entsteht dadurch, dass die Angabe in setColumnWidth() nicht dem entspricht, was Du in der Oberfläche siehst.
Bei mir entspricht setColumnWidth(67) der Breite 12 in Excel und 89 Pixel. Eine Umrechnung ergibt dann den Wert 75 in
setColumnWidth() für 100 Pixel (Excel-Breite = 13,57)

Das kann sich allerdings je nach Rechner unterscheiden, da das von der Standardschriftart abhängt. Dass das nirgendwo
für Office-Scripts vermerkt wird, weiß MS mittlerweile auch. Siehe dazu den zweiten Link unten.

https://learn.microsoft.com/de-de/office...umn-widths
https://github.com/OfficeDev/office-js/issues/3036

Wie bereits von Fennek geschrieben, Dein Code ist kein VBA, sondern JavaScript. Macht ggf. dann Sinn, wenn die
Datei auf OneDrive laufen soll oder in der Cloud verwendet wird (z.B. im Browser), wo kein VBA geht.
Der Eintrag Automatisieren im Menüband bezieht sich nicht auf VBA. VBA wäre Alt+F11.

Gruß

[Tobi-GL]

Ja, stimmt, ich habe die Automatisierung mit der neuen gleichnamigen Funktion aufgezeichnet, da ein VBA-Code schlecht in einem Team von mehreren Leuten für mehrere Dokumente zu nutzen ist.

Wenn ich das jetzt richtig verstanden hab, muss ich die Schriftart/-größe anpassen, damit Excel die Spaltenbreite richtig interpretieren kann....? Hab´s mal mit Arial verucht -> Fehlanzeige!

[schauan]

Hallöchen,

ist natürlich seltsam, dass Excel die Vorgabe eines bestimmten Breitenwertes in OfficeScript nicht umsetzt. Excel ist es ja egal, ob ein Text passt oder nicht - man sieht nur ggf. nicht alles, wenn die Zelle daneben belegt ist. Kann man in der Webapp manuell auch problemlos machen.

[maninweb]

Hallo,

@Tobi-GL: nein, Du musst nicht Excel anpassen. Es empfiehlt sich Deine Angabe in setColumnWidth() so umzurechnen,
dass es der Spaltenbreite in Excel entspricht. Wie in meinem vorherigen Beitrag geschrieben. Bspw. 75 / 100 / 13,57.
Das müsste für die Standard-Einstellungen von Excel Ok sein. Ansonsten den Faktor selbst ermitteln.

Gruß

[Tobi-GL]

Moin zusammen,

sorry für das lange Nicht-Antworten...Stress im Job!

Und nochmal sorry für mein Brett vor dem Kopf. Ich habe die grundsätzliche Problematik verstanden (glaub ich), aber die Berechnung für den einzusetzenden Wert in setColumnWidth() erschließt sich mir noch nicht. Wäre einer so nett, mir das mal an einem Beispel vorzurechnen?

Ja, ich weiß, es ist schlimm mit diesen stümperhaften Amateuren ...


 Danke vorab für eure Unterstützung!

[schauan]

Hallöchen,

die Berechnung kannst Du doch aus den Angaben von maninweb per "Dreisatz" ableiten.

Allerdings ist mir trotzdem noch nicht klar, warum. Du hast doch geschrieben, dass Du einen festen Wert einsetzen willst und in Deinem Code war der überall 12. Wenn 12 nicht übernommen wird, hat das nach meinem Verständnis andere Ursachen als die "Berechnung" der 12.
Dieser Wert hat lt. Aufgabe anscheinend nix mit den Zellinhalten zu tun. Wie ich schon schrieb, Excel-Desktop interessiert das auch nicht unbedingt. Einschränkung: Die Spaltenbreite ist mit AutoFit eingestellt. Vielleicht hat OfficeScript mit fester Breite ein Problem, wenn im Originalfile Autofit eingestellt ist. Könnte also sein, wenn Du 14 berechnest, dass die auch nicht funktioniert - oder ein anderer Wert auch nicht.

Tut sich eigentlich mit dem AutoFit von OfficeScript was an der Spatenbreite?
( ....getFormat().autofitColumns() )