VBA Join - Schritt-für-Schritt-Beispiele für die Excel VBA-Verknüpfungsfunktion

Inhaltsverzeichnis

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

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' Module '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 das Menü 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 As New Scripting.FileSystemObject Dim ts As Scripting.TextStream
  • We will declare more variables. ‘r’ is for holding rows in the range, ‘fs’ is for storing final joined string, ‘cols’ for storing numbers of columns in the range, ‘FolPath’ for storing the path of the folder so that we can save the files in the folder and ‘Items_Sold’ for storing various item names to create a file with these names.

Code:

Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String
  • To count the total number of columns in range, we will define the following statement.

Code:

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

This statement will first select the current region for cell A1 and then will count the total number of columns in the current region.

  • We will write the following statements for assigning the variable ‘FolPath’ a path using VBA ENVIRON function and Concatenation Operator.

Code:

FolPath = Environ("UserProfile") & "DesktopItems_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath

The second statement will create the folder if the folder does not exist in the same location.

  • This code will assign the values of B column one by one to ‘Items_Sold’ We have used ‘OFFSET function’ to get the reference of cell in B column as the currently selected cell is in column A.

Code:

Items_Sold = r.Offset(0, 1).Value
  • The following bordered statement will open the files with names stored in ‘Items_Sold’ variable in one by one in appending mode (the new values will be appended at last).

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") & "DesktopItems_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

Um den Code auszuführen, drücken wir F5. Dann können wir sehen, dass mit Hilfe von VBA-Code auf dem Desktop ein Ordner mit dem Namen 'Items_Sold' erstellt wurde.

In dem Ordner befinden sich 7 eindeutige Dateien, die mit den Namen des Elements erstellt wurden, und wir können Details zu nur diesem bestimmten Element in Dateien herausfinden.

Laptop.xls

Dinge, die Sie bei der VBA JOIN-Funktion beachten sollten

  • Das SourceArray muss ein eindimensionales Array sein. Wir können uns nicht auf eine einzelne Zelle beziehen, da dadurch mehrere mehrdimensionale Arrays erstellt werden.
  • Wenn wir eine Zeichenfolge mit der Länge Null ("") als Trennzeichen angeben, werden alle Elemente im Array ohne Trennzeichen verkettet.

Interessante Beiträge...