✔ Excel - Schleife mit Tabellennamen

[jerry0110]

Hallo zusammen,

ich habe in meinem Projekt eine Mail-Funktion, wo ich per Makro ein Tabellenblatt anspreche und dann eine Email verschicke.
Die Werte sind natürlich statisch und das möchte ich ändern, weil ich 30 Tabellenblätter habe die ich ansprechen möchte.

Eine Schleife die die Anzahl der Tabellenblätter zählt bekomme ich ohne Probleme hin. Dann weiß ich zwar wie viele Tabellenblätter es gibt, aber nicht die Namen.
Wie kann ich mir jetzt die Namen anzeigen lassen?

Dim i

For i = 0 To Worksheets.Count

      MsgBox i

Next i

End Sub

Ziel soll sein, in dir For Schleife die Email Funktion reinzupacken, die sich dann je Tabellenblatt ändert mit Ansprechpartner und Emailadresse.

 

[Yaslaw]

For i = 0 To Worksheets.Count - 1
    sheetName = Worksheets(i).Name
Next i

oder noch schöner

Dim ws As Worksheet
for each ws in Worksheets
    sheetName = ws.name
Next ws

 

[jerry0110]

So jetzt noch mal mit Erklärung im Code, was ich überhaupt machen möchte. Ich bekomme immer Typ Unverträglichkeit bei source.

Sub Daten_speichern()

Dim ws As Worksheet
Dim SheetName As String

For Each ws In Worksheets
       
    SheetName = ws.Name
   
    Dim source As Workbooks
    Dim sOrdner As String
    Dim sblattname As String
    Dim sFilename As String
    Dim myDate As Date
    Dim i As Integer
    myDate = datum
   
    'Werte aus source für Schleife (FEHLER!! Typ unverträglich)
    Set source = Workbooks("xxx.xlsm").Worksheets("Makros")
   
    'Schleife für Ordner Erstellung & Speichern der Datei im richtigem Ordner
    For i = lastRowNr(source) To 7 Step -1
       
        'Wenn der Wert in source der gleiche ist wie vom SheetName dann Exit
        If source.Range("A" & i).Value = SheetName Then Exit Sub
       
        'Ordner wo der Ordner sein soll
        sOrdner = "L:\Global\xxx\" & Format(datum, "YYYY") & "\" & SheetName
        sblattname = Format(datum, "YYYYMMDD") & "_" & SheetName & ".xls"
       
        'Wenn Ordner nicht vorhanden, dann anlegen
        If Dir(sOrdner, vbDirectory) = "" Then
            MkDir (sOrdner)
        End If
       
        sFilename = Application.GetSaveAsFilename(sOrdner & sblattname, "Micrsoft Excel-Dateien (*.xls),*.xls")
       
        'Fehler wird hier auch angezeigt bei ws.Activate'
        ws.Activate
        ActiveSheet.Copy
        ActiveWorkbook.SaveAs sFilename, FileFormat:=xlNormal
        ActiveWorkbook.Close False
   Next i
Next ws

End Sub

 

[jerry0110]

Habe den Code jetzt angepasst, das die Fehler nicht mehr kommen.
Aber er erstellt keinen Ordner und will die Datei auch nicht in dem vorgegebenen Ordner speichern.
Und er will immer das gleiche Sheet speichern.

Sub Daten_speichern()

Dim ws As Worksheet
Dim SheetName As String

For Each ws In Worksheets
     
    SheetName = ws.Name
 
    Dim source As Worksheet
    Dim sOrdner As String
    Dim sblattname As String
    Dim sFilename As String
    Dim myDate As Date
    Dim i As Integer
    myDate = datum
 
    'Werte aus source für Schleife
    Set source = Workbooks("xxx.xlsm").Worksheets("Makros")
 
    'Schleife für Ordner Erstellung & Speichern der Datei im richtigem Ordner
    For i = lastRowNr(source) To 7 Step -1
     
        'Ordner wo der Ordner sein soll
            sOrdner = "L:\Global\xxxx\" & Format(datum, "YYYY") & "\" & SheetName & "\"
            sblattname = Format(datum, "YYYYMMDD") & "_" & SheetName & ".xls"
     
        'Wenn Ordner nicht vorhanden, dann anlegen
            If Dir(sOrdner, vbDirectory) <> "" Then
                MkDir sOrdner
            End If
     
            sFilename = Application.GetSaveAsFilename(sOrdner & sblattname, "Micrsoft Excel-Dateien (*.xls),*.xls")
         
            ws.Activate
            ActiveSheet.Copy
            ActiveWorkbook.SaveAs sFilename, FileFormat:=xlNormal
            ActiveWorkbook.Close False

   Next i
Next ws

End Sub

 

[Yaslaw]

Du setzt deine Source zwar jedes mal neu, aber es ist immer dasselbe Sheet
Set source = Workbooks("Automatisierung NOC.xlsm").Worksheets("Makros")
Dann iterierst du über jede Zeile. Pro Zeile speicherst du. Und zwar in den Ordenr, denn du auswählst und nicht in den Ordner den du erstellst.

Was willst du denn machen? Ich verstehe das ab dem Code nicht.

 

[jerry0110]

Ich habe in der xxx.xlsm im Sheet Makros eine Liste von Namen.
Ich möchte, dass er diese Liste durch geht und dann prüft, gibt es passend zum Sheet den passenden Ordner. Wenn nicht dann erstell diesen Ordner. Am Ende soll er dann den Sheet in den passenden Ordner speichern.

 

[Yaslaw]

Warum fragst du jeweils nach dem Speicherort?
sFilename = Application.GetSaveAsFilename(sOrdner & sblattname, "Micrsoft Excel-Dateien (*.xls),*.xls")

 

[jerry0110]

Muss ich den nicht angeben, wenn ich am Schluss einfüge? Damit Excel weiß wohin er das speichern soll?

ActiveWorkbook.SaveAs sFilename, FileFormat:=xlNormal

 

[Yaslaw]

Schon. Aber das sollte sich doch aus deinem Automatisierung NOC.xlsm ergeben.

 

[jerry0110]

Vielleicht muss ich vorher noch was schreiben.

Also die Automatisierung NOC.xlsm ist nur eine Datei wo das Makro gespeichert ist.
Der Rest wird in einer neu erstellten Datei gemacht.

Das Makro macht aus einer CSV eine Excel und ordnet Inhalte anhand einer Spalte im Sheet und kopiert diese dann in das richtige Sheet. Das klappt. Da die neu erstellte Excel Datei ja aktiv ist und ich dann aber aus der Automatisierung NOC.xlsm die Liste abrufen möchte muss ja dann auf die andere Excel verweisen.

Dann soll er das oben angegebene Script ausführen.

Ich hoffe das habe ich jetzt verständlich erklärt.