Excel, VBA, Arbeitsmappen, Tabellen, Spalten, Zellen

[jobo]

Hallo,

aus einem Verzeichnis würde ich gerne die enthaltenen Dateien auslesen, aus den jeweiligen Dateien die darin enthaltenen Tabellen und aus jeder Tabelle zB. erstmal die Summe der Spalte C berechnen, wenn darin eine Zahl stünde.

In PHP wüsste ich wies geht, in VBA fehlen mir die spezifischen Begriffe/Syntax um eben a) das Verzeichnis auszulesen, b) eine Liste der Tabellen in einer Arbeitsmappe zu erstellen, über diese zu iterieren und c) innerhalb einer Tabelle alle Werte einer Spalte zu durchlaufen.

Kann jemand (vielleicht Vinzenz?) ein paar syntaktische Hinweise geben? Oder kennt jemand eine Quelle für Beispielskripte, die o.g. zu teilen enthalten?

Gruß

jobo

[Vinzenz Mai]

Hallo,

aus einem Verzeichnis würde ich gerne die enthaltenen Dateien auslesen, aus den jeweiligen Dateien die darin enthaltenen Tabellen und aus jeder Tabelle zB. erstmal die Summe der Spalte C berechnen, wenn darin eine Zahl stünde.

In PHP wüsste ich wies geht, in VBA fehlen mir die spezifischen Begriffe/Syntax um eben a) das Verzeichnis auszulesen,

dazu kannst Du

Dir[(Pfadname[, Attribute])]

nutzen.

b) eine Liste der Tabellen in einer Arbeitsmappe zu erstellen,

ist nicht nötig. Diese gibt es bereits, das Worksheets-Objekt, die eine Auflistung aller Worksheet-Objekte in der angegebenen oder aktiven Arbeitsmappe darstellt.

über diese zu iterieren und

Mit For Each selbstverständlich.

Dim ws as Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ' Tu was mit dem Arbeitsblatt
Next

c) innerhalb einer Tabelle alle Werte einer Spalte zu durchlaufen.

Nutze die Range.Column-Eigenschaft in Verbindung mit der Range.Cells-Eigenschaft und durchlaufe sie natürlich mit For Each.

Kann jemand (vielleicht Vinzenz?)

*g*

ein paar syntaktische Hinweise geben? Oder kennt jemand eine Quelle für Beispielskripte, die o.g. zu teilen enthalten?

die Excel-VBA-Referenz und insbesondere das Excel-Objektmodell sind sehr hilfreich. Beides ist Bestandteil der Online-Hilfe.

Freundliche Grüße

Vinzenz

[jobo]

Hallo Vinzenz,

Danke erstmal, bin am Probieren...;

Zwei Fragen parallel:

Gibt es eine Alternative zur MsgBox in Bezug auf "debugging" bzw. Testen, ob man das richtige erwischt hat?

Dim ws As Worksheet
Dim counter As Integer
counter = 1
For Each ws In ActiveWorkbook.Worksheets
    MsgBox ws.Name
    Worksheets("Tabelle3").Range("A" & counter).Value = ws.Name
    counter = counter + 1
Next

"funzt" auf jeden Fall schonmal.

die Excel-VBA-Referenz und insbesondere das Excel-Objektmodell sind sehr hilfreich. Beides ist Bestandteil der Online-Hilfe.

Du meinst "online" oder meinst Du die Hilfe die mit Excel mitkommt?

Gruß

jobo

[Vinzenz Mai]

Hallo,

Zwei Fragen parallel:

Gibt es eine Alternative zur MsgBox in Bezug auf "debugging" bzw. Testen, ob man das richtige erwischt hat?

selbstverständlich. Nutze den Debugger, den finde ich sehr angenehm und komfortabel. Setze Haltepunkte (einfach links in den Rand vor dem Code klicken) und steige anschließend im Einzelschrittmodus durch. Nutze Direktausgaben über

Debug.Print

» die Excel-VBA-Referenz und insbesondere das Excel-Objektmodell sind sehr hilfreich. Beides ist Bestandteil der Online-Hilfe.

Du meinst "online" oder meinst Du die Hilfe die mit Excel mitkommt?

Die in Excel integrierte Online-Hilfe (ich bevorzuge die Offline-Variante der Online-Hilfe :-)), die über F1 (Cursor hinter das Schlüsselwort setzen) erreichbar ist. Online hat in diesem Fall *nichts* mit dem Internet zu tun.

Freundliche Grüße

Vinzenz

[jobo]

Hallo Vinzenz,

merci für die Hilfe!

For Each mycell In ActiveWorkbook.Worksheets("Produkt1").range("B1:B10")
    Worksheets("Ausgabe").range("A" & counter).Value = mycell.Value & "neu"
    counter = counter + 1
Next

"funzt".

Jetzt habe ich aber:

Datum | Anzahl | Kategorie

und würder gerne Anzahl pro Kategorie haben. Bei PHP würde ich machen:

foreach ($rows as $row) {
  if (isset $categoryCounts[$row[2]] {
    $categoryCounts[$row[2]] = 0;
  }
    $categoryCounts[$row[2]] += $row[1];
}

Dann hätte ich nachher

$categoryCounts array(
["Kategorie 1"] => 75

etc.pp.

Gibt es sowas wie "assoziative Arrays"?

Am Ende will ich in meiner Ausgabe schreiben können:

Kategoriename | Gesamtzahl

Datumsfilter <> findet sich dann sicher schon. Allein schon stellt sich mir die Frage, wie ich beim For Eachen durch Spalte B den RowCount finde, um die dazugehörige Spalte C-Zelle zu finden. Abgesehen von o.g. Problem, wie ich Variablen anlege so, dass ich, wenn die Kategorie schon einen Eintrag in der Summe hat, ich den dazuzähle, wenn nicht, die Variable neu anlege.

Dank und Gruß

Robert aka jobo fka frankx ffffka frankxberlin

[Vinzenz Mai]

Hallo,

Gibt es sowas wie "assoziative Arrays"?

nennt sich (wie zum Beispiel in Python) Dictionary. Dazu musst Du einen Verweis auf die Microsoft Scripting Runtime (scrrun.dll) einbinden.

Beispiel:

Sub test()

Dim myDict As New Dictionary

' hier zwei Möglichkeiten, um Werte hinzuzufügen:

' a) Add-Methode des Dictionary-Objektes
    myDict.Add "Schlüssel", "Wert"

' b) einfache Zuweisung eines Wertes
    myDict("Hello") = "world"

Dim myKey As Variant    ' Variant erforderlich für For Each
    ' Durchlaufe das Dictionary
    For Each myKey In myDict.Keys
        Debug.Print myKey & ": " & myDict(myKey)
        ' Gibt folgendes aus:
        ' Schlüssel: Wert
        ' Hello: world
    Next

End Sub

Freundliche Grüße

Vinzenz

[jobo]

Hallo,

nennt sich (wie zum Beispiel in Python) Dictionary. Dazu musst Du einen Verweis auf die Microsoft Scripting Runtime (scrrun.dll) einbinden.

Blöde Frage: Wie binde ich das ein??? Ins Projekt, habe ich gefunden, aber wo und wie?

Gruß

jobo

[Vinzenz Mai]

Hallo,

» nennt sich (wie zum Beispiel in Python) Dictionary. Dazu musst Du einen Verweis auf die Microsoft Scripting Runtime (scrrun.dll) einbinden.

Blöde Frage: Wie binde ich das ein??? Ins Projekt, habe ich gefunden, aber wo und wie?

im VBA-Editor: Extras -> Verweise ... die entsprechende Checkbox setzen.

Freundliche Grüße

Vinzenz

[jobo]

Hallo Vinzenz,

eine Liste des Ordnerinhaltes erstellen klappt:

Pfad1 = FileSystem.CurDir & ""   ' Pfad setzen.
Name1 = Dir(Pfad1)     ' erster Eintrag
Do While Name1 <> ""    ' Schleife beginnen.
    ' Aktuelles und übergeordnetes Verzeichnis ignorieren.
    If Name1 <> "." And Name1 <> ".." Then
       Debug.Print Name1
    End If
    Name1 = Dir    ' Nächsten Eintrag abrufen.
Loop

Wenn ich aber mit Workbooks.Open jetzt eine Datei öffnen würde, wäre die ja das ActiveWorkbook, nicht wahr? Ich würde aber gerne aus der geöffneten Datei aus deren Tabellen was auslesen und das dann in mein Ausgabe.xls-Workbook reinschreiben.

Momentan habe ich das ohne das Dictonary gemacht, aber alles innehalb eines Workbooks, dort dann ein Worksheet namens "Ausgabe". Ich durchlaufe dort Worksheets, die nicht "Ausgabe" heißen, durchlauf dort die Spalte B, durchlaufe für jeden Wert im Worksheet "Ausgabe"-Spalte C und schaue dort, ob es schon einen Eintrag für die fragliche Kategorie (Spalte C der aktuellen Produkttabelle) gibt. Wenn ja, addiere ich den Wert aus der Produkttabelle dazu, wenn nicht, mache ich einen neuen Eintrag in Spalte C und B des "Ausgabe"-Worksheets:

For Each actualWs In Worksheets 'durchlaufe Worksheets
    RowCount = actualWs.UsedRange.Rows.Count
    If actualWs.Name <> "Ausgabe" Then ' in Ausgabe steht ja die Ausgabe
    For Each mycell In actualWs.range("B1:B" & RowCount) 'Spalte B durchlaufen
        CatFound = "notfound" 'Kategorie auf "nicht gefunden setzen"
        RowCountAusgabe = Ausgabe.UsedRange.Rows.Count 'Zeilenzahl der Ausgabetabelle
        For Each myAusgabeCell In Ausgabe.range("C3:C" & RowCountAusgabe)
            'wenn Spalte C der Kategorie entspricht und Spalte B dem Tabellennamen
            If myAusgabeCell.Value = actualWs.range("C" & mycell.Row).Value _
            And Ausgabe.range("B" & myAusgabeCell.Row).Value = actualWs.Name Then
                Ausgabe.range("D" & myAusgabeCell.Row).Value = mycell.Value + Ausgabe.range("D" & myAusgabeCell.Row).Value
                CatFound = "found"
            End If
        Next
        'Falls Tabellenname-Katgorie-Pärchen in Ausgabe-Spalte B-C nicht gefunden
        If CatFound = "notfound" Then
            Ausgabe.range("B" & RowCountAusgabe + 1).Value = actualWs.Name
            Ausgabe.range("C" & RowCountAusgabe + 1).Value = actualWs.range("C" & mycell.Row).Value
            Ausgabe.range("D" & RowCountAusgabe + 1).Value = mycell.Value
            Debug.Print RowCountAusgabe
        End If
        counter = counter + 1
    Next
    End If
Next

O.g. spielt sich ja aber eben alles innheralb des ActiveWorkbook ab. Wie kriege ich denn ein zweites Workbook auf (Referenz darau, mit Set ???), so dass ich also eine Ausgabe.xls mit einer Tabelle (Worksheet) "Ausgabe" habe, in die ich die zusammengefassten Ergebnisse der anderen Workbooks und deren Worksheets eintrage.

Dank und Gruß

Robert

[jobo]

Hallo Vinzenz,

mit Workbooks.open("myWorkbook.xls") kann ich dann wohl via Workbook("myWorkbook.xls") darauf zugreifen. Jetzt fand ich folgenden Code in einem PDF der Fernunin Hagen:

Sub Auto_open()
Worksheets("Hilfe").Visible = False
Sheets("Auswertung").Select
Range("A2").Select
End Sub

Heißt das, es gibt Kürzel, "Sheets" statt "Worksheets", und "Range" bezieht sich auf das zuletzt genannte Worksheet?

Werte setzte ich mit Workbooks("Ausgabe.xls").Worksheet("Ausgabe").Range("C5").Value oder gibt es dafür eine galantere Methode, zB.

Set WsAusgabe = Workbooks("Ausgabe.xls").Worksheet("Ausgabe")
WsAusgabe.Range("C5").Value = "was neues"

?

Dank und Gruß

Robert

[jobo]

Hallo,

der Vollständigkeit halber mal der aktuelle Code, der alle Workbooks im aktuellen Verzeichnis durchläuft und die in Spalte C notierten Kategorien mit in Spalte B notierter Anzahl zusammenzählt, pro Datei und Tabelle:

  
' Fasse Daten aus allen Workbooks dieses Ordners zusammen  
Sub DatenZusammenfassen()  
' setzt aktuellen Pfad in diesem Ordner (inkl. Backslash am Ende)  
Pfad = ActiveWorkbook.Path & "\" 'FileSystem.CurDir & "\"  
' Lese ersten Dateinamen/Ordnernamen  
Dateiname = Dir(Pfad)  
' Wir starten die Ausgabe in Zeile 3  
OutputRow = 3  
' Lösche vorhandene Daten in Ausgabetabelle ab 1,4 2,3 und alles ab Zeile 3  
range(Cells(1, 4), Cells(1, UsedRange.Columns.Count)).Value = ""  
range(Cells(2, 3), Cells(2, UsedRange.Columns.Count)).Value = ""  
range(Cells(3, 1), Cells(UsedRange.Rows.Count, UsedRange.Columns.Count)).Value = ""  
' Schreib mal Datum und Uhrzeit in Zeile 1  
range("B1").Value = "fasse Daten zusammen"  
range("C1").Value = Date  
range("D1").Value = Time  
' Durchlauf Dateinamen  
While Dateiname <> ""  
    ' Filterkriterium (Exceldatei, nicht die aktuelle Datei)  
    If DateinameCheck(Dateiname) = "ok" Then  
    ' öffne Workbook  
    Workbooks.Open (Pfad & Dateiname)  
    Debug.Print "opened " & Dateiname  
    ' Dateiname kommt in erste Spalte  
    range("A" & OutputRow).Value = Dateiname  
    ' Setze Variable auf Worksheetsliste dieses Workbooks  
    Set actualWorksheets = Workbooks(Dateiname).worksheets  
    ' Durchlaufe Worksheets  
    For Each ws In actualWorksheets  
        ' Schreibe Tabellennamen in B-Spalte  
        range("B" & OutputRow).Value = ws.Name  
        ' Lasse die Spalten durchlaufen  
         SpaltenDurchlaufen ws, OutputRow  
        ' nächste Zeile für neuen Eintrag  
        OutputRow = OutputRow + 1  
    Next  
    ' Workbook wieder zumachen  
    Workbooks(Dateiname).Close  
    Debug.Print "closed " & Dateiname  
    End If  
    ' nächsten Dateinamen einlesen  
    Dateiname = Dir  
Wend  
End Sub  
Function DateinameCheck(Dateiname)  
    ' Dateiname soll nicht diese Datei sein und muss auf .xls enden  
    If Dateiname <> ThisWorkbook.Name _  
    And Right(Dateiname, 4) = ".xls" Then  
    DateinameCheck = "ok"  
    Else  
    ' das macht man wohl besser mit boolean  
    DateinameCheck = "notok"  
    End If  
End Function  
Function SpaltenDurchlaufen(ws, OutputRow)  
    Debug.Print "--->" & ws.Name  
    ' Durchlaufe die Spalte B im aktuellen Tabellenblatt  
    For Each mycell In ws.range("B1:B" & ws.UsedRange.Rows.Count)  
        ' Flag setzen, Kategorie noch nicht vorhanden in Ausgabetabelle  
        CategoryFound = "notfound"  
        'in 0 umwandeln, wenn da Text drinne steht  
        myvalue = mycell.Value  
        If VarType(myvalue) = 8 Then  
            myvalue = 0  
        End If  
        'nur wenn was in Spalte C steht  
        If ws.range("C" & mycell.Row).Value <> "" _  
        And ws.range("C" & mycell.Row).Value <> " " _  
        And myvalue <> 0 Then  
            ' schaue in Ausgabetabelle Zeile 2 ab Spalte 5 ob Kategorie schon gelistet  
            For Each myAusgabeCell In range(Cells(2, 5), Cells(2, UsedRange.Columns.Count))  
                'natürlich nur, wenn nicht schon gefunden  
                If Trim(myAusgabeCell.Value) = Trim(ws.range("C" & mycell.Row).Value) Then  
                    ' wenn also Kategorie stimmt, dann addiere den Wert mit dem Zellenwert  
                    Cells(OutputRow, myAusgabeCell.Column).Value = _  
                    Val(Cells(OutputRow, myAusgabeCell.Column).Value) + myvalue  
                    ' Flag gefunden setzen  
                    CategoryFound = "found"  
                    Exit For  
                End If  
            Next  
            ' falls Kategorie noch nicht gelistet und der Wert nicht 0/String (s.o.) ist  
            If CategoryFound = "notfound" And myvalue <> 0 Then  
                ' schreibe Spaltenzahl in erste Zeile  
                Cells(1, UsedRange.Columns.Count + 1).Value = UsedRange.Columns.Count + 1  
                ' schreibe neue Kategorie in zweite Zeile  
                Cells(2, UsedRange.Columns.Count).Value = Trim(ws.range("C" & mycell.Row).Value)  
                ' setze Wert in aktuelle Zeile  
                Cells(OutputRow, UsedRange.Columns.Count).Value = myvalue  
            End If  
        End If  
    Next  
End Function  
Private Sub losjetzt_Click()  
    DatenZusammenfassen  
End Sub  
  

Gruß

jobo