jobo: Excel, VBA, Arbeitsmappen, Tabellen, Spalten, Zellen

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

  1. 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

    1. 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

      1. 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

        1. 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

          1. 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

            1. 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

              1. 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

                1. 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

                  1. 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

                    1. 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