Grundlagen Excel-Fernsteuerung - PivotTabelle erstellen

MemoAnMichSelbst · 19. Juni 2013, 07:45

Hi,

ich weiß ich es gibt zu dem Thema ne Info in der MSDNA aber ich bin ehrlich... Ich hätte gerne eine Art Tutorial in dem erklärt wird, wie man via VB.Net in Excel 2003 eine Pivot-Tabelle erstellt.

Zu meinem Problem:
Ich habe ne csv-Datei, welche ich in eine Excel-Datei umwandeln muss (soweit kein Problem).
Nun sollen diese Daten genutzt werden, um eine PivotTabelle in der neuen Excel-Datei in einer neuen (eigenen) Tabelle zu erstellen.

Nun zu meinen Fragen:
Wie kriege ich es hin, 1. dass er mir die PivotTabelle zwar in der gleichen Excel-Datei, aber einer anderen (neuen) Tabelle anlegt und dann alle Daten (sowas wie Strg+A) als Basis nimmt.
Nun hat die Tabelle nur vier Spalten. Die ersten zwei sollen in den "Zeilenbereich", die dritte in den "Spaltenbereich" und die Vierte in den "Datenbereich".

Die anschließende Formatierung sieht vor, dass die Zwischenergebnisse Grün und das Endergebnis Gelb als Hintergrundfarbe erhalten. Das wäre aber dann der letzte Feinschliff und ist erst einmal irrelevant.

ErfinderDesRades · 19. Juni 2013, 07:55

und du meinst, das codet dir jetzt jemand?

also was du versuchen kannst ist, es mal von Hand zu machen, und dabei den Makro-Rekorder mitlaufen zu lassen.
Das Makro taugt dann zwar nix, aber immerhin tauchen da einige der dafür notwendigen Aufrufe auf.

MemoAnMichSelbst · 19. Juni 2013, 08:01

Den guten Makro Recorder habe ich schon verwendet

Aber der Gute arbeitet ja prinzipiell mit "spätem Binding" XD

Und nein, ich suche niemanden der sowas Codet, das wär der falsche Bereich. Ich suche, wie schon gepostet, ein gescheites Tutorial, in dem (unter Einhaltung von Option Strict ON!!!!!! - Scheint in den gefundenen Tuts nicht so gefragt zu sein) erklärt wird, wie man eine Pivot-Tabelle mit VB.Net in Excel generieren kann

EDIT: Ich möchte das Ganze ja verstehen. Wird schließlich nicht die letzte PivotTabelle meines Lebens sein

Aber ich würde gerne mal so nen funktionierendes Beispiel haben, mit dem ich mich beschäftigten kann und das erläutert ist.

ErfinderDesRades · 19. Juni 2013, 08:58

jeden Strict Off-Code kann man in sauberen Code umwandeln. Also Makro-Recorder-Code ist durchaus eine Grundlage.

petaod · 19. Juni 2013, 12:42

MemoAnMichSelbst schrieb:

Den guten Makro Recorder habe ich schon verwendet
Aber der Gute arbeitet ja prinzipiell mit "spätem Binding"

Hast du es denn geschafft, mit Option Strict Off eine Lösung zu erarbeiten?
Wenn du da einen lauffähigen Code hast, helfen wir dir gerne, den mit Option Strict On zum Laufen zu bringen (falls du es nicht selbst schaffst).

MemoAnMichSelbst · 20. Juni 2013, 08:20

Hallo,

danke erstmal für die Beiträge.

Mittlerweile habe ich einen Strict Off Code erstellt, welcher mir eine Pivot Tabelle generiert.
Wobei hier immer spätes binding betrieben wird, was er ja so nicht sonderlich mag.
Sprich, ich hab das alles in ne Strict Off Modul ausgelagert.
Nun geht es daran das ganze sauber umzuschreiben.
Wobei mir da wohl noch ein paar Grundlagen fehlen.

Ich bin etwas erstaunt, dass sämtliche Tutotials auf die ich gestoßen bin (und es gibt da ja ne ganze Menge) alle Strict off programmiert sind.

Für die Interesiserten der Code:

Spoiler anzeigen

VB.NET-Quellcode

Option Strict Off
Imports Microsoft.Office.Interop
Module mdlStricktOff
Friend Function PivotTabelleUmsatzAuswertung(ByVal ExcelA As Microsoft.Office.Interop.Excel.Application, ByVal Workbook As Microsoft.Office.Interop.Excel.Workbook) As Microsoft.Office.Interop.Excel.Application
ExcelA.Visible = True
Dim xlSheet As Excel.Worksheet
xlSheet = CType(Workbook.Worksheets(1), Excel.Worksheet)
Dim LastCell As String
LastCell = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Address
xlSheet = CType(ExcelA.Worksheets(1), Excel.Worksheet)
Dim xlRange As Excel.Range = CType(xlSheet, Excel.Worksheet).Range("A1:D" & LastCell.Split("$")(2))
xlSheet = CType(Workbook.Worksheets.Add(), Excel.Worksheet)
xlSheet.Name = "Umsatzauswertung"
Dim xlRange2 As Excel.Range = CType(xlSheet, Excel.Worksheet).Range("A1")
Dim ptCache As Excel.PivotCache = Workbook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
Dim ptTable As Excel.PivotTable = xlSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Auswertung")
Workbook.ActiveSheet.PivotTables(1).PivotFields("Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField
Workbook.ActiveSheet.PivotTables(1).PivotFields("Teilegruppe").Orientation = Excel.XlPivotFieldOrientation.xlRowField
Workbook.ActiveSheet.PivotTables(1).PivotFields("Monat").Orientation = Excel.XlPivotFieldOrientation.xlColumnField
Workbook.ActiveSheet.PivotTables(1).PivotFields("Umsatz").Orientation = Excel.XlPivotFieldOrientation.xlDataField
Workbook.ActiveSheet.PivotTables(1).PivotFields("Umsatz").Name = "Gesamtergebnis"
xlSheet.PivotTables("Auswertung").PivotSelect("Name[All;Total]", 0, True)
With ExcelA.Selection.Interior
.ColorIndex = 35
.Pattern = 1
End With
xlSheet.PivotTables("Auswertung").PivotSelect("'Column Grand Total'", 0, True)
With ExcelA.Selection.Interior
.ColorIndex = 36
.Pattern = 1
End With
ExcelA.Cells.Select()
ExcelA.Selection.Font.Bold = True
ExcelA.Selection.Font.Size = 8
ExcelA.Selection.Style = "Comma"
ExcelA.Columns("A:B").Select()
ExcelA.Selection.NumberFormat = "General"
ExcelA.Rows("2:2").Select()
ExcelA.Selection.NumberFormat = "General"
Return (ExcelA)
End Function
End Module

Ich bin so vorgegangen, dass ich eine Funktion erstellt habe, die das bereits geöffnete Excel-Dokument bekommt und aufbereitet.

Vielleicht an dieser Stelle eine Frage...
Der Befehl:

VB.NET-Quellcode

ExcelA.Workbooks.OpenText(System.IO.Path.Combine(fbd.SelectedPath, String.Concat("UmsatzAuswertung_", Today.ToString("d"), "_", rsVertreter.Fields("Vertreter").Value.ToString, "_", rsVertreter.Fields("VertreterName").Value.ToString, ".csv")), Local:=True, Semicolon:=True)

Wird von mir verwendet um die entsprechenden Dateien (CSV) zu öffnen und nach Semikolon getrennt dar zu stellen.
Das klappt bei allen bis auf einer CSV-Datei. Dort scheint er die Angabe "Semicolon:=True einfach zu ignorieren und trennt auf ganz seltsame weise mitten in den Wörtern. Das Phänomen kann ich mir bislang noch garnicht erklären.

[EDIT]
Ich habe die CSV Datei nun manuell einmal in Excel geöffnet und nach Semikolon trennen lassen. Alles kein Problem. Er stellt alles richtig dar.
Ich bin etwas ratlos warum der Aufruf nicht aus VB funktioniert.

RodFromGermany · 20. Juni 2013, 08:36

MemoAnMichSelbst schrieb:

VB.NET-Quellcode

Module mdlStricktOff

Mach da wenigstens eine Class draus und instanziiere die anständig mit New.

MemoAnMichSelbst · 20. Juni 2013, 08:37

Na das ist hier doch echt völlig wurscht. Das soll schließlich net so bleiben und hat auch nichts mit dem Problem zu tun.

ErfinderDesRades · 20. Juni 2013, 09:18

RodFromGermany schrieb:

Mach da wenigstens eine Class draus und instanziiere die anständig mit New.

Wenn er kein Objekt braucht, dann sollte er auch nix mit New instanzieren.
Also braucht er auch keine Class, bzw. es ist schnuppe, ob er eine Class hat mit Shared Functions drin oder ein Modul, wo halt einfach alle Functions Shared sind.

ich hab jetzt auch probiert, das strict Off zu kriegen - aber man ist nur am Casten.
Und einige Sachen gehen bei mir wohl nicht, weil altes Excel.

jdfs. wenn die Doku schon sowas aussagt, verlier ich echt die Lust dran:

ObjectBrowser schrieb:

Function PivotFields(Optional Index As Object = Nothing) As Object
Member von Microsoft.Office.Interop.Excel.PivotTable
Zusammenfassung:
Returns an object that represents either a single PivotTable field (a Microsoft.Office.Interop.Excel.PivotField object) or a collection of both the visible and hidden fields (a Microsoft.Office.Interop.Excel.PivotFields object) in the PivotTable report. Read-only.

ObjectBrowser schrieb:

Function PivotTables(Optional Index As Object = Nothing) As Object
Member von Microsoft.Office.Interop.Excel._Worksheet
Zusammenfassung:
Returns an object that represents either a single PivotTable report (a Microsoft.Office.Interop.Excel.PivotTable object) or a collection of all the PivotTable reports (a Microsoft.Office.Interop.Excel.PivotTables object) on a worksheet. Read-only.

Also die wissen selbst nicht, was sie da returnen - ich find, da wurstelt mans halt Strict Off hin, und fassts später nie wieder an.

MemoAnMichSelbst · 20. Juni 2013, 11:20

Also ich habe das Umprogrammieren jetzt auch erstmal hinten an gestellt.
Aber mich stört trotzdem, dass

VB.NET-Quellcode

ExcelA.Workbooks.OpenText(System.IO.Path.Combine(fbd.SelectedPath, String.Concat("UmsatzAuswertung_", Today.ToString("d"), "_", rsVertreter.Fields("Vertreter").Value.ToString, "_", rsVertreter.Fields("VertreterName").Value.ToString, ".csv")), Local:=True, Semicolon:=True)

nicht sauber läuft.
Bzw. es stört mich dass ich nicht verstehe wieso es bei 9 CSV-Dateien geht und bei der 10ten nicht, wobei es für mich keinen Unterschied der Dateien gibt.

sonne75 · 20. Juni 2013, 11:24

MemoAnMichSelbst schrieb:

wobei es für mich keinen Unterschied der Dateien gibt.

Es muss einen Unterschied geben, und wenn es nur die Reihenfolge ist. Hast du mal die 10.Datei als erste aufgerufen?

MemoAnMichSelbst · 20. Juni 2013, 11:33

Da es leider nur eine Funktion innerhalb einer anderen Funktion ist, geht das leider nicht so einfach.
(Ich schreibe nur die CSV-Dateien, weil es wesentlich schneller funktioniert eine CSV zu erstellen, sie in Excel zu öffnen, die CSV zu verwerfen und die Excel Datei zu formatieren, als die ganzen Daten direkt in Excel zu schreiben.)
Die letzte Excel Datei lässt sich "manuell" (via Doppelklick) problemlos öffnen und lässt sich korrekt formatieren.
Ich frage mich, wieso er da beim Öffnen Probleme macht...

Die CSV-Dateien werden ja alle auf die gleiche Art und Weise erstellt.

sonne75 · 20. Juni 2013, 11:36

Du kannst doch zur Probe die 10.Datei zuerst öffnen lassen (hardcodiert). Auch wenn es eine Funkton innerhalb einer Funktion ist (was hat es damit eigentlich zu tun?). Es geht jetzt nur ums Testen, ob Reihenfolge überhaupt eine Rolle spielt.

EDIT: Du hast mehrere mögliche Faktoren (die Reihenfolge, Dateiname, Dateiinhalt usw), jetzt musst du systematisch jeden untersuchen.

MemoAnMichSelbst · 20. Juni 2013, 11:51

So,

ich habe einfach mal mit:

VB.NET-Quellcode

Dim ExcelA As New Microsoft.Office.Interop.Excel.Application
ExcelA.Workbooks.OpenText("C:\Users\dummy\Desktop\UmsatzAuswertung\UmsatzAuswertung_20.06.2013_10_Deutschland Dummy.csv", Local:=True, Semicolon:=True)
ExcelA.visible = True

geprüft was er tut. Gleiches Ergebnis. Er öffnet die Datei falsch formatiert.
Der Name entspricht dem Schema aller anderen Dateinamen die durchlaufen. Ich wüsste also nicht wo da der Fehler sein könnte.
Die Datei selber... ich erkenne an ihr keine differenz zu den anderen Dateien.
Sie werden mit der gleichen Funktion erstellt und befüllt.

Mir fällt da nichts mehr ein.

sonne75 · 20. Juni 2013, 11:54

Und Nr. 11? Mach eine Nummer 11 zusätzlich und schau, was er mit ihr macht.

MemoAnMichSelbst · 20. Juni 2013, 12:17

Also die Folgenden Dateien bearbeitet er wieder. Es gibt aber noch weitere, bei denen er die Formatierung falsch macht.
Ich erkenne aber keinen Grund dafür...

Ich weiß auch nicht was ihn stören könnte.
Mein erster Gedanke war, dass irgendwo ein Simekolon in nem Namen sitzt, aber ich sehe nichts.
Er scheint in diesen Fällen immer bei einem Leerzeichen getrennt zu haben, anstelle des Semikolons.

[EDIT]
Korrigiere. Er trennt zwei mal nach 8 Zeichen und die dritte Spalte beinhaltet den Rest... -.-

sonne75 · 20. Juni 2013, 12:20

Lade bitte 4 Dateien hoch: 2, die gehen, und 2, die nicht gehen. Dann kann ich versuchen irgendwelche Regelmäßigkeiten zu finden.

MemoAnMichSelbst · 20. Juni 2013, 12:25

Leider nicht möglich

Sind Firmendaten. Ich würde behaupten das ist nen Kündigungsgrund

sonne75 · 20. Juni 2013, 12:42

Ok. Was ist denn nach diesen 8 Zeichen? Ist es bei allen Dateien, die falsch sind, gleich?

EDIT: Ah, und noch eine Idee. Kopiere mal Datei Nr.9 (wenn sie geht) und benenne sie als 10 um. Was passiert dann?

MemoAnMichSelbst · 20. Juni 2013, 13:02

Ich habe jetzt mal die erste Datei genommen und in die 10te umbenannt.
Er konnte dann die Datei mit dem "10"ten Namen öffnen.
Also wird es an irgendwas vom Inhalt liegen. Ist doch zum Mäuse melken.
Die ersten 8 Zeichen wären eine Kundennummer (passt auch genau.... Kundennummern hier sind immer 8-Stellig).
Dann kommt ein Teil vom Kundennamen oder der ganze Name (je nach Länge...) - halt auch wieder 8 Zeichen -

Eigentlich besteht die Datei aus:

Quellcode

Kundennr Kundenname;Teilegruppe Teilegruppenname;Umsatz;Periode

Beispiel:

10001213 RUTH;ABD P-Platte;4234,23;1

Grundlagen Excel-Fernsteuerung - PivotTabelle erstellen

Grundlagen Excel-Fernsteuerung - PivotTabelle erstellen

MemoAnMichSelbst schrieb:

VB.NET-Quellcode

VB.NET-Quellcode

MemoAnMichSelbst schrieb:

VB.NET-Quellcode

RodFromGermany schrieb:

ObjectBrowser schrieb:

ObjectBrowser schrieb:

VB.NET-Quellcode

MemoAnMichSelbst schrieb:

VB.NET-Quellcode

Quellcode

Ähnliche Themen

5 Benutzer haben hier geschrieben