Pripojiť sa k VBA - Podrobné príklady funkcie spojenia Excel VBA

Podobné tomu, čo máme v pracovnom hárku ako funkcia Zreťaziť a príkaz &, ktorý sa používa na spojenie dvoch alebo viac ako dvoch reťazcov, vo VBA používame na to príkaz Pripojiť, v spojení vo VBA berieme zdroj údajov v poli a podobne ako v prípade zreťazenia používame oddeľovač, aby sme sa k nim pripojili.

Funkcia Excel VBA JOIN

Ako už samotný názov napovedá, funkcia VBA JOIN sa používa na spojenie radu podreťazcov so zadaným oddeľovačom. Ak nezadáme žiadny oddeľovač, potom bude ako predvolený oddeľovač znaku „medzera“. Robí to isté, čo funkcia Concatenate v programe Excel, okrem toho, že musíme určiť oddeľovač iba raz, zatiaľ čo vo funkcii Concatenate musíme oddeľovač zadať zakaždým medzi dvoma reťazcami.

Syntax funkcie je

Ako vidíme, funkcia vezme dva argumenty a vráti reťazec. Argumenty sú:

  1. SourceArray : Musíme špecifikovať alebo dať odkaz na pole podreťazcov, ktoré sa majú spojiť.
  2. Oddeľovač : Oddeľovač sa používa na oddelenie každej z podreťazcov pri vytváraní výsledného reťazca. Pretože toto je voliteľný argument, ak ho vynecháme, oddeľovač sa nastaví na medzeru. ““.

Funkcia VBA SPLIT je úplne opačnou funkciou ako funkcia VBA JOIN.

Príklady funkcie spojenia VBA

Ďalej uvádzame príklady funkcií spojenia v programe Excel VBA.

Pripojenie VBA - príklad č

Predpokladajme, že sa chceme spojiť s menom (Ramesh), stredom (Kumar) a priezviskom (Mishra).

Kroky by boli:

  • Najskôr musíme otvoriť editor jazyka Visual Basic. To isté môžeme urobiť kliknutím na príkaz „Visual Basic“ v skupine „Kód“ na karte „Vývojár“ programu Excel alebo môžeme použiť klávesovú skratku programu Excel Alt + F11 .
  • Vložte modul kliknutím pravým tlačidlom myši na „hárok 1“ a výberom príkazu „Vložiť“ z kontextového menu. Potom vyberte modul „Vložiť“.
  • Vytvorte podprogram s názvom 'JoiningName'.

Kód:

Sub JoiningName () End Sub
  • Funkciu JOIN použite nasledovne

Kód:

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

Vidíme, že sme na poskytnutie funkcie SourceArray funkcii JOIN použili funkciu ARRAY a preskočili sme, aby sme určili znak oddeľovača, takže predvoleným znakom bude „medzera“. Spracovaná hodnota funkcie JOIN sa zapíše do bunky D2, keď tento kód vykonáme pomocou klávesu F5 alebo ručne.

Pripojenie VBA - príklad č. 2

Predpokladajme, že chceme vytvoriť rôzne súbory programu Excel s názvom položky, ktorý obsahuje predaj iba pre túto položku.

  • Otvorte editor jazyka Visual Basic pomocou klávesovej skratky Alt + F11.
  • Kliknite pravým tlačidlom myši na hárok „Hárok1“ (príklad 2), aby ste otvorili kontextové menu, a kliknite na „vložiť“, aby ste vložili modul „VBA“ do projektu VBA.
  • Definujte podprogram s názvom 'CreateItemSoldFiles'.

Kód:

Sub CreateItemSoldFiles () End Sub
  • Musíme nastaviť odkaz na objektovú knižnicu „Microsoft Scripting Runtime“ pomocou ponuky Nástroje -> Referencie …, pretože použijeme nejaký kód (objekty), ktorý nebude fungovať, ak túto knižnicu objektov nezahrnieme.
  • Teraz deklarujeme všetky premenné.

Kód:

Dim FSO ako nové skriptovanie. FileSystemObject

Vyššie uvedená premenná FSO umožňuje prístup k súboru VBA FileSystemObject. Po väzbe môžeme použiť funkcie ako BuildPath, CopyFile, CreateTextFile atď.

  • Nasledujúci príkaz vytvorí objekt TextStream. Prostredníctvom objektu TextStream môžeme čítať z pôvodného súboru alebo k nemu pripojiť.

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

Teraz na vykonanie kódu stlačíme kláves F5 a vidíme, že pomocou VBA kódu na ploche bol vytvorený priečinok s názvom „Items_Sold“.

V priečinku je 7 jedinečných súborov vytvorených s názvami položiek a v súboroch môžeme zistiť podrobnosti iba o tejto konkrétnej položke.

Laptop.xls

Dôležité informácie o funkcii VBA JOIN

  • Pole SourceArray musí byť jednorozmerné pole. Nemôžeme odkazovať na samostatnú bunku, pretože sa tým vytvorí viacrozmerné pole.
  • Ak zadáme ako oddeľovač reťazec s nulovou dĺžkou („“), všetky položky v poli budú spojené bez oddeľovačov.

Zaujímavé články...