VBA-Methoden: Mehrere Arbeitsblätter umbenennen

Vielleicht erinnerst du dich. Im Beitrag „VBA-Methoden: Arbeitsblätter zählen, Anzahl festlegen“ zeigte ich am Ende des Beitrags eine einfache Möglichkeit, die Arbeitsblätter einer neuen Arbeitsmappe umzubenennen.
Gleichzeitig versprach ich, dir in einem späteren Beitrag bessere Wege zu zeigen. Jetzt ist es soweit.

Weiterlesen „VBA-Methoden: Mehrere Arbeitsblätter umbenennen“

Advertisements

VBA: Datenfelder und Arrays

Arrays sind Datenfelder, die je nach Dimensionierung eine unterschiedliche Anzahl von Daten aufnehmen können. Ein Array kannst du dir als Tabelle vorstellen, die nicht real sichtbar, aber dennoch vorhanden ist.

Ein Array ist eine Variable, die mehrere Datenfelder und damit auch mehrere Daten enthält. Alle Datenfelder haben den Datentyp, der für das Array deklariert wurde. Wurde kein Datentyp festgelegt, hat das Array automatisch den Datentyp Variant.

Zur Deklarierung von Variablen kannst du auch gern in einem früheren Beitrag nachlesen.

Weiterlesen „VBA: Datenfelder und Arrays“

VBA-Methoden: Arbeitsblätter zählen, Anzahl festlegen

Wie viele Arbeitsblätter hat eine Arbeitsmappe?

Befindest du dich in einer geöffneten Arbeitsmappe, kannst du das sofort sehen, wenn sich die Anzahl auf ein überschaubares Maß beschränkt.

Geht es aber um 95 oder 134 Arbeitsblätter, müsstest du zählen.

Das aber kannst du auch durch ein kleines Makro erledigen lassen.

Sub ArbBlattZählen1()
Dim intZahl As Integer
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

So sieht die Antwort aus. Bestätige sie mit „OK“.

Nichtnumerisch34

Lässt sich die Anzahl der Arbeitsblätter für eine andere, geöffnete, Mappe ermitteln?

Sub ArbBlattZählen2()
Dim intZahl As Integer
Workbooks(„VBA_Datei öffnen.xlsm“).Activate
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

Hier gibt die MsgBox z.B. die Anzahl „3“ zurück.

Nun fragst du dich, ob du diese Abfrage auch an eine geschlossene Datei richten kannst.

Nachfolgendes Makro verwendet die GetObject-Funktion, die ich hier nicht weiter erläutere. Nur soviel, GetObject wird für den Zugriff auf ein ActiveX-Object verwendet. Mit der Set-Anweisung wird dem Objekt die Objektvariable zugewiesen.

Sub ArbBlattZählen3()
Dim intZahl As Integer
Dim wb As Workbook
Set wb=GetObject(„C:\Excel\VBA\VBA_Arbeitsblätter Anzahl.xlsm“)
intZahl=wb.Sheets.Count
MsgBox intZahl
End Sub

Die MsgBox gibt hier die Anzahl „5“ zurück.

Anzahl Arbeitsblätter festlegen

Eine neue Excel-Arbeitsmappe enthält standardmäßig drei Arbeitsblätter. Dies ist in den Excel-Optionen unter „Allgemein“ so eingestellt.

Wenn du aber eine neue Mappe haben willst, die 12 Arbeitsblätter enthält, kannst du das mit einem Makro erledigen lassen.

Du fragst dazu zuerst die hinterlegte Anzahl der Arbeitsblätter für neue Arbeitsmappen ab.

Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook

Dann legst du die Anzahl der Blätter für deine neue Mappe fest, in diesem Fall sollen es 12 sein.

Application.SheetsInNewWorkbook=12

Jetzt legst du eine neue Mappe an und gibst ihr einen Namen. Wie das geht, habe ich in meinem Beitrag „VBA-Methoden: Eine Datei neu erstellen und speichern als“ beschrieben:

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“

Den Ablageort gibst du mit der Pfadangabe gleich mit an.

Du hast jetzt eine neue Arbeitsmappe mit 12 Arbeitsblättern.

Nun solltest du die Einstellung für die Anzahl der Blätter wieder auf den Standard setzen und die Datei speichern und schließen.

Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close

Das Makro gesamt:

Sub ArbBlätter()
Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook=12
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“
Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Natürlich kannst du den 12 Arbeitsblättern in deiner neuen Mappe gleich noch neue Namen geben, z.B.“Januar“ bis „Dezember“.

Füge dazu im einfachsten Weg diese Codezeilen in dein Makro vor den Anweisungen .Save und .Close ein:

ActiveWorkbook.Sheets(1).Name=“Januar“
ActiveWorkbook.Sheets(2).Name=“Februar“

usw. bis „Dezember“.

Einen besseren Weg zeige ich mal in einem künftigen Beitrag.

VBA-Methoden: Arbeitsblätter, Druckvorschau und Drucken

Jeder, der am Computer mit Excel arbeitet, wird hin und wieder Ausdrucke ganzer Arbeitsblätter oder eines definierten Druckbereichs vornehmen.

In der Regel wird dem Druck eine Druckvorschau vorangehen.

Dieser Beitrag zeigt, wie mit einem Makro Druckvorschau und Drucken auf unterschiedliche Weise erledigt werden kann.

Weiterlesen „VBA-Methoden: Arbeitsblätter, Druckvorschau und Drucken“

Wie du Werte mit VBA ersetzen kannst

Der Beitrag in der letzten Woche beschäftigte sich mit dem Ersetzen von Werten in Excel.

Mit dem heutigen Beitrag zeige ich dir das Ersetzen von Werten mit einem Makro in VBA. Dabei verwende ich die gleichen Beispiele wie im vorangegangenen Beitrag.

Im ersten Beispiel sollte „Düssel“ im Wort „Düsseldorf“ durch „Deggen“ ersetzt werden. „Düsseldorf steht z.B. in Zelle B119.

Das Makro dazu ist dieses:

Sub Ersetzen1()
Cells(121,2).Formula=“=REPLACE(B119,1,6,““Deggen““)“
End Sub

In VBA wird für das Ersetzen die Methode REPLACE verwendet. Das Makro nimmt das Wort aus B119 (Düsseldorf), ersetzt die Zeichenfolge ab dem 1. Zeichen, insgesamt 6 Zeichen, durch das Wort „Deggen“ und trägt den neuen Begriff (Deggendorf) in B121 ein.

In einem zweiten Beispiel soll die Jahreszahl 2016 im abgebildeten Bereich durch 2017 ersetzt werden.

Excel_Ersetzen5          Excel_Ersetzen8

Die Umsetzung realisierst du mit diesem Makro:

Sub Ersetzen2()
Dim rngZelle As Range
For Each rngZelle in Range(„B58:B65“)
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub

Das Makro deklariert die Variable rngZelle. Mit einer For-Next-Schleife werden die Zellinhalte des Bereiches durch „2017“ ersetzt.

Das dritte Beispiel sieht ähnlich aus. Du markierst den Bereich B58:B65, Zellinhalt ist die Jahreszahl 2016. Mit diesem Makro wandelst du die Zahl 2016 in 2017 um:

Sub Ersetzen3()
Dim rngZelle As Range
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub

Was ist anders als im zweiten Beispiel? Richtig, du ersetzt nicht die Werte in einem definierten Bereich, sondern in einem selektierten Bereich.

Im vierten Beispiel gibst du die alte und die neue Jahreszahl in separaten Zellen vor.

VBA_Ersetzen1

Und dies ist das Makro dazu:

Sub Ersetzen4()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Range(„E82“)
strNeu=Range(„E83“)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rng>Zelle
End Sub

Beachte bitte, dass auch hier wieder auf den selektierten Bereich abgestellt wurde.

Im fünften Beispiel verwendest du eine Inputbox zum Abfragen der alten und der neuen Jahreszahl.

Sub Erstezen5()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Inputbox(„altes Jahr“,,“2016″)
strNeu=Inputbox(„neues Jahr“,,“2017″)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rngZelle
End Sub

In einem letzten Beispiel geht es darum, die Jahreszahl „2015“ durch „2016“ und die Jahreszahl „2016“ durch „N.N.“ zu ersetzen. Das folgende Bild zeigt links den Zustand vor dem Makro und rechts danach:

VBA_Ersetzen2

Mit diesem Makro setzt du dein Vorhaben um:

Sub Ersetzen6()
Dim rngZelle As Range
For Each rngZelle in Range(„B93:B100“)
If rngZelle.Value=“2016″ Then
rngZelle=REPLACE(rngZelle.Value,“2016″,“N.N.“)
End If
If rngZelle.Value=“2015″ Then
rngZelle=REPLACE(rngZelle.Value,“2015″,“2016″)
End If
Next rngZelle
End Sub

Warum ersetzt du zuerst die Zahl „2016“? Bedenke, wenn du zuerst „2015“ durch „2016“ ersetzt, steht in jeder Zelle des Bereiches „2016“. Diese Zahl ersetzt du dann mit „N.N.“ Das Ergebnis wäre, dass in allen Zellen „N.N.“ steht.

Also ersetzt du zuerst „2016“ durch „N.N.“ und dann „2015“ durch „2016“.

Bei ganzen Spalten oder ganzen Zeilen gehst du analog vor.

Was machst du mit der Funktion ERSETZEN bzw. REPLACE? Lasse es uns in einem Kommentar wissen.

VBA-Methoden: Eine Datei schließen

Wenn eine Datei geöffnet und bearbeitet wurde, soll sie irgendwann auch wíeder geschlossen werden. Das wiederum kann manuell oder mit einem Makro erledigt werden.

Das manuelle Schließen war schon im Beitrag „VBA-Methoden: Eine Datei speichern“ ein Thema. Dabei wurde vor dem Schließen eine Speicherung mit einem Makro vorgenommen.

Wenn du eine Datei mit einem Makro schließen willst, gibt es auch hierbei verschiedene Möglichkeiten.

Einfaches Schließen einer Datei

Für das Schließen der aktiven Datei wird die Methode Close genutzt. Das geht so:

Sub SchließenDatei1()
ActiveWorkbook.Close
End Sub

Wenn du vorher nicht gespeichert hast, bekommst du diese Mitteilung:

Datei_schließen1

Erst wenn du dann „Speichern“ klickst, wird die Datei geschlossen. Das kannst du umgehen, wenn du den Speichern-Befehl in deinen Code mit einbaust. Zwei Möglichkeiten hast du hierfür:

Sub SchließenDatei2()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

oder so:

Sub SchließenDatei3()
ActiveWorkbook.Close SaveChanges:=True
End Sub

Nach dem Close-Befehl wird zusätzlich die Anweisung gegeben, dass die Speicherung von Veränderungen vorgenommen wird.

Schließen einer zusätzlich geöffneten Datei

Wie im Beitrag „VBA_Methoden: Eine Datei öffnen“ erläutert, hast du z.B. eine weitere Datei manuell oder mit einem Makro geöffnet. Du hast deine Bearbeitungen vorgenommen und willst die Datei nun über dein Makro wieder schließen. Eine Speicherung siehst du ebenfalls vor.

Sub SchließenDatei4()
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

oder so:

Sub SchließenDatei5()
Workbooks(„VBA_Datenblatt.xlsx“).Close SaveChanges:=True
End Sub

Öffnen, Bearbeiten, Speichern und Schießen einer zusätzlichen Datei

Jetzt willst du die Vorgänge vom Öffnen bis zum Schließen einer zusätzlichen Datei mit einem Makro ausführen lassen.

Im Arbeitsblatt „Tabelle1“ der Zusatzdatei soll in Zelle B10 der Text „Bearbeitung ist erfolgt“ eingetragen werden. Die Bearbeitung wird mit einer MsgBox bestätigt.

Sub SchließenDatei6()
Workbooks.Open Filename:=“C:\Excel\VBA_Datenblatt.xlsx“
Worksheets(„Tabelle1“).Range(„B10″)=“Bearbeitung ist erfolgt“
MsgBox(„Eintrag erfolgt“)
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

Schließen einer Datei nach dem Speichern

Die Zusatzdatei soll sofort geschlossen werden, wenn gespeichert wurde.

Sub SchließenDatei7()
If Workbooks(„VBA_Datenblatt.xlsx“).Saved=True Then
Workbooks(„VBA_Datenblatt.xlsx“).Close
Else
MsgBox(„Datei ist nicht gespeichert“)
End If
End Sub


Wurde nicht gespeichert, kann in diesem Fall auch nicht geschlossen werden. Du erhälst deshalb die Mitteilung „Datei ist nicht gespeichert“ mittels einer MsgBox.

VBA-Methoden: Eine Datei umbenennen

Wenn du eine Datei „speicherst als“, hast du zwei Dateien. Einmal die ursprüngliche und einmal die mit einem neuen Namen und / oder einer anderen Extension.

Manchmal willst du eine Datei einfach nur umbenennen. Auch dazu bietet VBA eine Lösung.

1. Einfach umbenennen

Die umzubenennende Datei ist geschlossen. Deshalb wird jeweils der gesamte Pfad mit angegeben.

Sub Umbenennen1()
Name „C:\Excel\VBA_Datenblatt.xlsx“ As „C:\Excel\VBA_Datenblatt_2.xlsx“
End Sub

Wenn du das Ganze wieder rückgängig machen willst, tauscht du im zweiten Code die Bezeichnungen der Dateien.

Sub Umbenennen2()
Name „C:\Excel\VBA_Datenblatt_2.xlsx“ As „C:\Excel\VBA_Datenblatt.xlsx“
End Sub

2. Umbenennen mit Variablen

Es gibt dann noch die Möglichkeit, für den alten und neuen Namen jeweils eine Variable zu verwenden. Für das erste Beispiel sieht der Code dann so aus:

Sub Umbenennen3()
Dim strAltname As String
Dim strNeuname As String
strAltname=“C:\Excel\VBA_Datenblatt.xlsx“
strNeuname=“C:\Excel\VBA_Datenblatt_2.xlsx“
Name strAltname As strNeuname
End Sub

3. Umbenennen nach Liste

Vielfach gibt es eine ganze Reihe von Dateien in einem Ordner, die umbenannt werden sollen, beispielsweise wenn die Jahreszahl im Namen enthalten ist und ein neues Jahr begonnen hat.

Auf dem Laufwerk sollen z.B. die Dateien

  • VBA-Umsatz_2016.xlsx
  • VBA-Kosten_2016.xlsx
  • VBA-Mengen_2016.xlsx

liegen. Sie sollen jeweils die Jahreszahl 2017 erhalten.

Da es auch sehr viel mehr als diese drei Dateien sein können, kann eine Auflistung im Arbeitsblatt nützlich sein.

Datei_umbenennen1

Der folgende Code wird die Umbenennung der Dateien in Spalte B in die Dateien in Spalte C. vornehmen.

Das Makro wurde sehr ausführlich aufgeschrieben, Schritt für Schritt, so dass auch VBA-Neulinge die Prozedur verstehen sollten.

Sub Umbenennen4()
Dim intZeile As Integer
Dim strPfad As String
Dim strAltname As String
Dim strNeuname As String
Dim strAlt As String
Dim strNeu As String
strPfad=“C:\Excel\“
For intZeile=3 To 5
strAltname=Cells(intZeile,2).Value
strNeuname=Cells(intZeile,3).Value
strAlt=strPfad & strAltname
strNeu=strPfad & strNeuname
Name strAlt As strNeu
Next intZeile
End Sub

Komfortabler wäre es. die Dateien, die umbenannt werden sollen, per VBA aus dem Ordner auszulesen, sie in einer weiteren Spalte umbenennen und dann die Änderungen im Ordner vorzunehmen.

Das Auslesen von Dateien aus Ordnern ist aber eine andere Geschichte, die ich hier nicht behandle.