Připojte se k VBA - Podrobné příklady funkce připojení Excel VBA

Podobně jako to, co máme v listu jako funkce Zřetězit a příkaz &, který se používá ke spojení dvou nebo více než dvou řetězců dohromady, ve VBA k tomu použijeme příkaz Připojit, ve Spojení ve VBA vezmeme zdroj dat v poli a podobně jako v zřetězení používáme oddělovač, abychom se k nim připojili.

Funkce Excel VBA JOIN

Jak název napovídá, funkce VBA JOIN se používá ke spojení řady podřetězců se zadaným oddělovačem. Pokud neurčíme žádný oddělovač, bude jako výchozí oddělovač použit znak „mezera“. Provádí stejnou práci jako funkce Zřetězit v aplikaci Excel, kromě toho, že musíme zadat oddělovací znak pouze jednou, zatímco ve funkci Zřetězit musíme zadat oddělovací znak pokaždé mezi dvěma řetězci.

Syntaxe funkce je

Jak vidíme, funkce trvá dva argumenty a vrací řetězec. Argumenty jsou:

  1. SourceArray : Musíme určit nebo uvést odkaz na pole podřetězců, které se mají spojit.
  2. Oddělovač : Oddělovač se používá k oddělení každého z podřetězců při vytváření výsledného řetězce. Jelikož se jedná o volitelný argument, vynecháme-li jej, bude oddělovač nastaven na mezeru ““.

Funkce VBA SPLIT je přesně opačnou funkcí funkce VBA JOIN.

Příklady funkce VBA join

Níže jsou uvedeny příklady funkce spojení v aplikaci Excel VBA.

Připojení VBA - příklad č. 1

Předpokládejme, že se chceme připojit k křestnímu jménu (Ramesh), prostřednímu (Kumar) a příjmení (Mishra).

Kroky by byly:

  • Nejprve musíme otevřít editor jazyka Visual Basic. To samé můžeme udělat kliknutím na příkaz „Visual Basic“ ve skupině „Kód“ na kartě „Vývojář“ Excel nebo můžeme použít klávesovou zkratku Excel Alt + F11 .
  • Vložte modul kliknutím pravým tlačítkem na „list 1“ a výběrem příkazu „Vložit“ z kontextového menu a poté výběrem „Modul“ pro vložení.
  • Vytvořte podprogram s názvem 'JoiningName'.

Kód:

Sub JoiningName () End Sub
  • Použijte funkci JOIN následujícím způsobem

Kód:

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

Vidíme, že jsme použili funkci ARRAY k poskytnutí funkce SourceArray funkci JOIN a přeskočili jsme, abychom určili znak oddělovače, aby byl mezerou výchozí znak. Zpracovaná hodnota funkce JOIN se zapíše do buňky D2, když tento kód provedeme pomocí klávesy F5 nebo ručně.

Připojení VBA - příklad č. 2

Předpokládejme, že chceme vytvořit různé soubory aplikace Excel s názvem položky obsahujícím prodej pouze pro tuto položku.

  • Otevřete editor jazyka pomocí klávesové zkratky Alt + F11.
  • Kliknutím pravým tlačítkem na list „List1“ (příklad 2) otevřete kontextovou nabídku a kliknutím na „Vložit“ vložíte modul „VBA“ do projektu VBA.
  • Definujte podprogram s názvem 'CreateItemSoldFiles'.

Kód:

Sub CreateItemSoldFiles () End Sub
  • Musíme nastavit odkaz na knihovnu objektů „Microsoft Scripting Runtime“ pomocí nabídky Nástroje -> Reference…, protože použijeme nějaký kód (objekty), který nebude fungovat, pokud tuto knihovnu objektů nezahrneme.
  • Nyní deklarujeme všechny proměnné.

Kód:

Dim FSO jako nové skriptování. FileSystemObject

Výše uvedená proměnná FSO umožňuje přístup k VBA FileSystemObject. Po navázání můžeme použít funkce jako BuildPath, CopyFile, CreateTextFile atd.

  • Další příkaz vytvoří objekt TextStream. Prostřednictvím objektu TextStream můžeme číst nebo připojit k původnímu souboru.

Kód:

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

Nyní k provedení kódu stiskneme klávesu F5 a uvidíme, že byla vytvořena složka s názvem „Items_Sold“ pomocí kódu VBA na ploše.

Ve složce je vytvořeno 7 jedinečných souborů s názvy položek a v souborech můžeme zjistit podrobnosti pouze o této konkrétní položce.

Laptop.xls

Důležité informace o funkci VBA JOIN

  • SourceArray musí být jednorozměrné pole. Nemůžeme odkazovat na jednotlivou buňku, protože to vytvoří několik vícerozměrných polí.
  • Pokud zadáme řetězec s nulovou délkou („“) jako oddělovač, všechny položky v poli budou zřetězeny bez oddělovačů.

Zajímavé články...