 |
HowTo: |
Excel-Dokumente erzeugen per VBscript |
Wenn man per VBScript ein Excel-Sheet erzeugen will, so ist das relativ einfach. Man verwendet
das COM-Objekt von Excel namens Excel.Application. Die Fernsteuerung per COM-Objekt
nennt sich dann auch Excel Automation. Selbstverständlich klappt das nur, wenn Excel auf dem
Rechner installiert ist, auf dem das Script dann später läuft.
Innerhalb des Skriptes kann man dann alle Objekte, Collections, Methoden und Properties benutzen,
so als ob man innerhalb von Excel VBA programmieren würde. Der Clou: Man kann mit dem Makro-Recorder
Aktionen aufzeichnen und den erzeugten Code dann in einem VBScript verwenden.
Allerdings muß man im VBScript noch extra das Objekt angeben, desssen Methode man aufruft.
In Excel-VBA kann man z.B. folgende Zeile direkt aufrufen:
Range("A1").Font.Colorindex = 3
In einem VBScript hingegen müßte man zuerst das Objekt der Excel-Applikation erzeugen, dann ein Excel-Dokument (Workbook), dann ein Excel-Sheet (Tabellenblatt). Danach kann man die Methoden dieses Excel-Sheet-Objektes verwenden - in der gleichen Syntax wie in VBA - nur eben nicht allein für sich, sondern ausgehend von dem Objekt.
set xlo = CreateObject("Excel.Application")
set xlw = xlo.Workbooks.Add()
set xls = xlo.Sheet(1)
xls.Range("A1").Font.Colorindex = 3
Anmerkung: Beim Umgang mit Excel muss man sich daran gewöhnen, dass alle Abzählungen
nicht bei 0 beginnen, sondern bei 1. Dies getrifft z.B. die Auswahl der Workbooks, der Worksheets,
aber auch die der Zeilen und Spalten!
Eine gute Beschreibung aller Objekte und Methoden findet man im hier:
MSDN Excel2007 Developer Reference
Das folgende Script demonstriert einen kleinen Ausschnittl der Möglichkeiten, die einem die Excel-Automatisierung bietet:
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"
xlsFill xls, 1, "Dies; ist; die; Kopfzeile;Bla;Blubb;Dum;Dee;Dum"
xlsFill xls, 2, "1;2;3;4;5;6;7;8;9"
xlsFill 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 xlsFill(byRef 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
Erhalten Sie unter Excel 2007 beim Aufruf der Methode ...Workbooks.Add() den Fehler Error: 0x80028018 (-2147647512) / Description: Old Format or Invalid Type Library erhalten, so liegt ein Mismatch zwischen der Ländereinstellung des Office-Paketes und Ihres Betriebssystems vor. Lesen Sie mehr dazu im Microsoft-KnowledgeBase-Artikel 320369.
 |
Download von ExceOutput.vbs |