'_____________________________________________________________________________ Excel Konstanten Const xlAnd = 1 ' Enum XlAutoFilterOperator Const xlOr = 2 Const xlTop10Items = 3 Const xlBottom10Items = 4 Const xlTop10Percent = 5 Const xlBottom10Percent = 6 Const xlAscending = 1 ' Enum XlSortOrder Const xlDescending = 2 Const xlSortColumns = 1 ' Enum XlSortOrientation Const xlSortRows = 2 Const xlSortNormal = 0 ' Enum XlSortDataOption Const xlSortTextAsNumbers = 1 Const msoScaleFromTopLeft = 0 ' Enum MsoScaleFrom Set wso = CreateObject("Wscript.Shell") '_____________________________________________________________________________ Excel starten Set xlo = CreateObject("Excel.Application") xlo.Visible = True '_____________________________________________________________________________ Umgang mit Excel Sheets Set xlw = xlo.Workbooks.Add() xlw.Sheets(1).Name = "Summary" xlw.Sheets(2).Name = "Sheet1" xlw.Sheets(3).Delete '_____________________________________________________________________________ Arbeit in einem Sheet Set xls = xlw.Sheets(1) xls.Cells(5,1) = "TestTest" xlFill xls, 1, "Dies; ist; die; Kopfzeile;Bla;Blubb;Dum;Dee;Dum" xlFill xls, 2, "1;2;3;4;5;6;7;8;9" xlFill xls, 3, "2;4;6;8;10;12;13;14;15" '______________________________________________________________________________ AutoFill-Beispiel xls.Range("A2:I3").AutoFill xls.Range("A2:I100") '______________________________________________________________________________ Kommentar einfügen xls.Cells(2,2).AddComment xls.Cells(2,2).Comment.Text "Hallo" & Chr(10) & "Dies ist ein Kommentar" xls.Cells(2,2).Comment.Shape.ScaleWidth 3, False, msoScaleFromTopLeft xls.Cells(2,2).Comment.Shape.ScaleHeight 3, False, msoScaleFromTopLeft xls.Cells(2,2).Comment.Shape.TextFrame.Characters.Font.Name = "courier" xls.Cells(2,2).Comment.Shape.TextFrame.Characters.Font.Size = 12 '______________________________________________________________________________ Fenster Fixierung xls.Activate xlo.ActiveWindow.SplitColumn = 2 xlo.ActiveWindow.SplitRow = 1 xlo.ActiveWindow.Split = True xlo.ActiveWindow.FreezePanes = True '______________________________________________________________________________ AutFilter xls.Range("E:E").AutoFilter 1, ">100" 'alle Elemente > 100 xls.Range("E:E").AutoFilter 1, "10", xlBottom10Items 'die untersten 10 Elemente '______________________________________________________________________________ Sortierung xls.Range("A1:I100").Sort xls.Range("B2"), xlDescending '______________________________________________________________________________ Speichern / Doc Schließen xlFileName = "test.xls" xlFilePath = wso.CurrentDirectory If Not (Right(xlFilePath, 1) = "\") Then xlFilePath = xlFilePath & "\" xlFilePath = xlFilePath & xlFileName xlw.SaveAs(xlFilePath) xlw.Close '______________________________________________________________________________ Bestehendes Doc Öffnen xlo.Workbooks.Open xlFilePath Set xlw = xlo.Workbooks.Item(xlFileName) '______________________________________________________________________________ Sheet hinzufügen/plazieren xlw.Sheets.Add Set xls = xlw.Sheets(1) xls.Name = "Sheet 2" xls.Move xlw.Sheets(xlw.Sheets.Count) ' we can only use "before" param here! xlw.Sheets(xlw.Sheets.Count).Move xls ' so place our sheet before last and vice versa ! xls.Select Set xls = xlw.Sheets("Summary") xls.Select '______________________________________________________________________________ Doc Sichern und schließen xlw.Save xlw.Close '______________________________________________________________________________ Excel Schließen xlo.Quit Set xlo=Nothing '______________________________________________________________________________ Sub xlsFill 'Füllt eine Zeile in einem Excel-Sheet mit Werten, die als Semikolon-getrennter String übergeben werden 'vars xls: excel worksheet ' col: column number ' values: semicolon-separated string ' Sub xlFill(xls, col, values) Dim v, i v = Split(values, ";") For i=0 To UBound(v) xls.Cells(col, i+1) = v(i) Next End Sub '______________________________________________________________________________Function xlsRow 'Gibt zu einer gegebenen Spaltennummer ide Excelbezeichnung in Buchstaben zurück, z.B. "AE" 'Diese Angabe benötigt man bei Verwendung der Range-Eigenschaft ' Function xlRow(i) Dim r If (i<27) Then r = Chr(i + 64) Else r = Chr(Int(((i-1) / 26)) + 64) + Chr((((i-1) Mod 26) + 1) + 64) End If xlRow=r End Function