VBA JOIN

Ähnlich wie im Arbeitsblatt als Verkettungsfunktion und mit dem Befehl &, mit dem zwei oder mehr als zwei Zeichenfolgen miteinander verbunden werden, verwenden wir in VBA den Befehl Join, um dies zu tun. In VBA verbinden verwenden wir die Datenquelle in einem Array und ähnlich wie bei der Verkettung verwenden wir ein Trennzeichen, um sie zu verbinden.

Excel VBA JOIN-Funktion

Wie der Name selbst schon sagt, wird die VBA JOIN-Funktion verwendet, um ein Array von Teilzeichenfolgen mit dem angegebenen Trennzeichen zusammenzufügen. Wenn wir kein Trennzeichen angeben, wird 'Leerzeichen' als Standardtrennzeichen verwendet. Es funktioniert genauso wie die Verkettungsfunktion in Excel, außer dass wir das Trennzeichen nur einmal angeben müssen, während wir in der Verkettungsfunktion jedes Mal zwischen zwei Zeichenfolgen ein Trennzeichen angeben müssen.

Die Syntax der Funktion lautet

Wie wir sehen können, akzeptiert die Funktion zwei Argumente und gibt eine Zeichenfolge zurück. Argumente sind:

  1. SourceArray : Wir müssen ein Array von Teilzeichenfolgen angeben oder referenzieren, die verbunden werden sollen.
  2. Trennzeichen : Das Trennzeichen wird verwendet, um die einzelnen Teilzeichenfolgen beim Erstellen der resultierenden Zeichenfolge zu trennen. Da dies ein optionales Argument ist, wird das Trennzeichen als Leerzeichen festgelegt, wenn wir es weglassen.

Die VBA SPLIT-Funktion ist eine genau entgegengesetzte Funktion der VBA JOIN-Funktion.

Beispiele für die VBA-Verknüpfungsfunktion

Nachfolgend finden Sie Beispiele für Join-Funktionen in Excel VBA.

Sie können diese VBA-Join-Funktions-Excel-Vorlage hier herunterladen - VBA-Join-Funktions-Excel-Vorlage

VBA Join - Beispiel 1

Angenommen, wir möchten den Vor- (Ramesh), Mittel- (Kumar) und Nachnamen (Mishra) verbinden.

Schritte wären:

  • Zuerst müssen wir den Visual Basic-Editor öffnen. Sie können das Gleiche tun, indem Sie auf den Befehl 'Visual Basic' in der Gruppe 'Code' unter der Registerkarte 'Entwickler' Excel klicken oder die Excel-Tastenkombination Alt + F11 verwenden .

  • Fügen Sie das Modul ein, indem Sie mit der rechten Maustaste auf ein "Blatt 1" klicken und im Kontextmenü den Befehl "Einfügen" auswählen und dann "Modul" zum Einfügen auswählen.

  • Erstellen Sie eine Unterroutine mit dem Namen 'JoiningName'.

Code:

 Sub JoiningName () End Sub 

  • Verwenden Sie die JOIN-Funktion wie folgt

Code:

 Sub JoiningName () Range ("D2"). Value = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub 

Wir können sehen, dass wir die ARRAY-Funktion verwendet haben, um SourceArray für die JOIN-Funktion bereitzustellen, und übersprungen haben, um das Trennzeichen anzugeben, sodass 'Leerzeichen' das Standardzeichen ist. Der verarbeitete Wert der JOIN-Funktion wird in Zelle D2 geschrieben, wenn wir diesen Code mit der Taste F5 oder manuell ausführen.

VBA Join - Beispiel 2

Angenommen, wir möchten verschiedene Excel-Dateien erstellen, deren Artikelname nur Verkäufe für diesen Artikel enthält.

  • Öffnen Sie den Visual Basic-Editor mit der Tastenkombination Alt + F11.
  • Klicken Sie mit der rechten Maustaste auf das Blatt 'Sheet1' (Beispiel 2) ', um das Kontextmenü zu öffnen, und klicken Sie auf' Insert ', um ein VBA-Modul in das VBA-Projekt einzufügen.

  • Definieren Sie eine Unterroutine mit dem Namen 'CreateItemSoldFiles'.

Code:

 Sub CreateItemSoldFiles () End Sub 

  • Wir müssen einen Verweis auf die Objektbibliothek 'Microsoft Scripting Runtime' über den Befehl Extras -> Verweise… festlegen, da wir Code (Objekte) verwenden, der nicht funktioniert, wenn wir diese Objektbibliothek nicht einschließen.

  • Jetzt werden wir alle Variablen deklarieren.

Code:

 FSO als neues Scripting.FileSystemObject dimmen

Die obige FSO-Variable ermöglicht den Zugriff auf das VBA FileSystemObject. Nach dem Binden können wir Funktionen wie BuildPath, CopyFile, CreateTextFile usw. verwenden.

  • Die nächste Anweisung erstellt ein TextStream-Objekt. Über das TextStream-Objekt können wir aus der Originaldatei lesen oder an diese anhängen.

Code:

 Dim FSO als neues Scripting.FileSystemObject Dim ts als Scripting.TextStream

  • Wir werden weitere Variablen deklarieren. 'r' dient zum Speichern von Zeilen im Bereich, 'fs' zum Speichern der endgültig verbundenen Zeichenfolge, 'cols' zum Speichern der Anzahl der Spalten im Bereich, 'FolPath' zum Speichern des Pfads des Ordners, damit die Dateien gespeichert werden können im Ordner und 'Items_Sold' zum Speichern verschiedener Elementnamen, um eine Datei mit diesen Namen zu erstellen.

Code:

 Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String 

  • Um die Gesamtzahl der Spalten im Bereich zu zählen, definieren wir die folgende Anweisung.

Code:

cols = Range ("A1"). CurrentRegion.Columns.Count

Diese Anweisung wählt zuerst die aktuelle Region für Zelle A1 aus und zählt dann die Gesamtzahl der Spalten in der aktuellen Region.

  • Wir werden die folgenden Anweisungen schreiben, um der Variablen 'FolPath' einen Pfad mit der VBA ENVIRON-Funktion und dem Verkettungsoperator zuzuweisen.

Code:

FolPath = Environ ("UserProfile") & "\ Desktop \ Items_Sold" Wenn nicht FSO.FolderExists (FolPath) Dann FSO.CreateFolder FolPath

Die zweite Anweisung erstellt den Ordner, wenn der Ordner nicht am selben Speicherort vorhanden ist.

  • Dieser Code weist die Werte der Spalte B nacheinander 'Items_Sold' zu. Wir haben die Funktion 'OFFSET' verwendet, um die Referenz der Zelle in der Spalte B abzurufen, da sich die aktuell ausgewählte Zelle in Spalte A befindet.

Code:

Items_Sold = r.Offset (0, 1) .Value

  • Die folgende umrandete Anweisung öffnet die Dateien mit Namen, die in der Variablen 'Items_Sold' gespeichert sind, nacheinander im Anhänge-Modus (die neuen Werte werden zuletzt angehängt).

Code:

 Set ts = FSO.OpenTextFile(FolPath & "\" & Items_Sold & ".xls", ForAppending, True )

We have used Concatenate operator with variables ‘FolPath’ and ‘Items_Sold’ and static values (“\” and”.xls”) to create file names for excel files.

  • We need to keep in mind that VBA JOIN function takes an only one-dimensional array as SourceArray To convert the rows into a one-dimensional array, we need to use Application.Transpose method two times.

Code:

fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab)

We have used the Resize method of range object to resize the range to the width of a number of columns in the range.

As delimiter, we have used ‘vbTab’ keyword so that values would be filled in different cells.

  • As we have stored the processed value of JOIN function into ‘fs’ variable, we will write the fs’s values into new lines of VBA created excel files for every row in our original file from row number 2 to the last row (in our case it is 350th row).

  • Before ending the loop, we will close the file opened. The code would be as shown in the screenshot.

We have written the full code now.

Code:

 Sub CreateItemSoldFiles() Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String cols = Range("A1").CurrentRegion.Columns.Count FolPath = Environ("UserProfile") & "\Desktop\Items_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath For Each r In Range("A2", Range("A1").End(xlDown)) Items_Sold = r.Offset(0, 1).Value Set ts = FSO.OpenTextFile(FolPath & "\" & Items_Sold & ".xls", ForAppending, True ) fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab) ts.WriteLine fs ts.Close Next r End Sub 

Now to execute the code, we will press F5 then, we can see that a folder named ‘Items_Sold’ has been created with the help of VBA code on the desktop.

In the folder, there are 7 unique files created with the names of the item and we can find out details about only that particular item in files.

Laptop.xls

Things to Remember About VBA JOIN Function

  • The SourceArray must be a one-dimensional array. We cannot refer to an individual cell, as this will create multiple multi-dimensional arrays.
  • If we specify a zero-length string (“”) as a delimiter, all items in the array are concatenated with no delimiters.