Vba excel join

VBA JOIN

Similar to what we have in worksheet as Concatenate function and the & command which is used to join two or more than two strings together, in VBA we use Join command to do so, in Join in VBA we take source of the data in an array and similar to the concatenation we use a delimiter to join them.

Excel VBA JOIN Function

As the name itself suggests, the VBA JOIN function is used to join together an array of substrings with the specified delimiter. If we do not specify any delimiter, then it takes ‘space’ as a default delimiter character. It does the same work as Concatenate function does in Excel except we have to specify delimiter character once only whereas, in Concatenate function, we have to specify delimiter character every time in between every two strings.

The syntax of the function is

As we can see, the function takes two arguments and returns a string. Arguments are:

SourceArray: We need to specify or give reference to an array of substrings that are to be joined.
Delimiter: The delimiter is used to separate each of the substrings when creating up the resultant string. As this is an optional argument, if we omit it, the delimiter is set to be a space ” “.

The VBA SPLIT function is an exact opposite function of the VBA JOIN function.

Examples of VBA Join Function

Below are the examples of join function in Excel VBA.

VBA Join – Example #1

Suppose, we want to join the first (Ramesh), middle (Kumar) and last name (Mishra).

First, we need to open the visual basic editor. We can do the same by clicking on ‘Visual Basic’ command in the ‘Code’ group under the ‘Developer’ tab or we can use the excel shortcut keyAlt+F11.

Insert the module by right-clicking on a “sheet 1” and choosing ‘Insert’ command from the contextual menu and then choose ‘Module’ to insert.

Create a subroutine named ‘JoiningName’.

Code:

Use the JOIN function as follows

Code:

We can see that we have used ARRAY function to provide SourceArray to the JOIN function and skipped to specify the delimiter character so ‘space’ would be default character. The processed value of the JOIN function will be written in cell D2 when we execute this code using the F5 key or manually.

VBA Join – Example #2

Suppose, we want to create various excel files with the item name containing sales only for that item.

Open the Visual Basic Editor using shortcut key Alt+F11.
Right click on ‘Sheet1′ (Example 2)’ sheet to open the contextual menu and click on ‘Insert’ to insert a VBA ‘Module’ in the VBA project.

Define a subroutine named ‘CreateItemSoldFiles’.

Code:

We need to set a reference to ‘Microsoft Scripting Runtime’ object library using Tools menu -> References… command, as we will use some code (objects), which will not work if we do not include this object library.

Now we will declare all the variables.

Code:

The above FSO variable gives access to the VBA FileSystemObject. After binding, we can use functions like BuildPath, CopyFile, CreateTextFile, etc.

The next statement creates a TextStream object. Through the TextStream object, we can read from or append to the original file.

Code:

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 file with these names.

Code:

To count the total number of columns in range, we will define the following statement.

Code:

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 ENVIRON function and Concatenation Operator.

Code:

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:

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:

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:

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 350 th row).

Читать еще: Работа с датами в vba excel

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:

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.

Есть ли INNER JOIN в Excel?

3 Moh S. [2018-08-17 18:52:00]

Я хочу сделать внутреннее соединение двух разных таблиц в электронной таблице Excel на основе соответствующих значений столбцов. Вот пример:

Таблица A:

Таблица B:

Внутреннее соединение должно возвращать тикеры и цены из таблицы B, для которых в таблице A указаны тикеры:

Я пробовал VLOOKUP и MATCH но не смог эмулировать функцию INNER JOIN , существующую в SQL . Надеемся, что сообщество StackOverflow может оказать некоторую помощь 🙂

inner-join vba excel-vba excel excel-formula

4 ответа

Для этого вам понадобятся две разные формулы:

Чтобы получить имена, которые вам понадобятся:

Чтобы получить значения:

Это можно сделать с помощью одной формулы:

Поместите это в G2 и скопируйте его сверху вниз.

Но это удваивает количество формул массива, и если набор данных большой, это приведет к значительным замедлениям в времени вычислений.

2 QHarr [2018-08-17 20:06:00]

Как было указано, очень просто с помощью powerquery, где вы можете внутренне объединить обе таблицы, используя функцию запросов слияния, после того, как обе таблицы в памяти.

Обе таблицы настроены как таблицы Excel
Выберите ячейку в таблице, затем вкладку «Загрузка» (2013); Получить и преобразовать вкладку данных Excel 2016> Из таблицы (новый запрос)
Это загружает таблицу и появляется редактор запросов. Перейдите на домашнюю вкладку и выберите «Закрыть» и «Загрузить»

Выберите Только закрыть и загрузить соединение

Повторите шаги 2-4 для другой таблицы так, как в памяти.

Новый запрос> Объединить запросы> Объединить запросы

В появившемся окне выберите обе таблицы, убедитесь, что вы выделили столбец «Тикер» в обеих таблицах и установите тип соединения для внутреннего соединения

Разверните стрелки <> в столбце «Таблица» и снимите флажок в столбце тикера (удаляет повторяющийся столбец)

При необходимости переименуйте столбцы. Закройте и загрузите результат на лист

2 JohnyL [2018-08-17 19:59:00]

Если вам нужен SQL — используйте его! 🙂

Это пример кода VBA, который использует SQL для объединения таблиц. Вот пример книги с кодом. Новый лист будет создан с результатом. Предполагая, что таблица A находится на листе 1 и таблице B на листе2:

1 Rawrplus [2018-08-17 19:34:00]

Ну, это вроде Catch 22. Потому что это может быть и не может быть сделано в одно и то же время

Что я имею в виду под этим, Excel в основном используется для статических данных. Хотя результаты могут меняться в зависимости от формул, а что нет, чтобы проверить значение, оно должно быть указано где-то.

Внутреннее объединение — это операция, которая в основном принимает две сущности и создает из нее новую, например:

Это, как правильно указал @ScottCraner, а не то, что было создано для Excel. Таким образом, у нас есть 3 варианта (или, по крайней мере, я мог бы подумать об этом).

Важно отметить, что мы даже не способны делать правильный INNER JOIN() , но мы можем хотя бы создать иллюзию — в основном эмуляцию того, что она должна делать

Ручной способ — у меня был ответ написан, но @ScottCraner избил меня, и он более элегантный, смею сказать, так что просто обратитесь к нему вместо этого

Путь vba — я отвечу ниже

Через powerquery — что я не так хорошо знаком с собой, так что вам придется делать собственное исследование/возможно, кто-то еще ответит на него.

Способ vba:

Предположим, что мы имеем следующие данные:

Если бы мы описали, как INNER JOIN() работает алгоритмически, это будет выглядеть примерно так.

Возьмите диапазон (массив данных) a
Выбор диапазона (массив данных) b

Сравните данные друг с другом и верните новый результирующий массив r = a ∩ b

3.1. Выражение a ∩ b может быть записано «программно» следующим образом:

For каждого элемента в a :
if он exists в b
push его к r массива esulting
в противном случае перейти к Next элементу

Возврат r массива esulting

Теперь, для простоты (и для практического использования), я не собираюсь возвращать массив. То, что я собираюсь сделать isntead, заключается в том, чтобы распечатать каждый элемент в столбце F если он соответствует критериям (в основном, шаги с 1. по 3. )

Читать еще: Vba excel работа с массивами

Это должно при вызове эмулировать ожидаемый результат операции INNER INNER JOIN() .

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Excel VBA. SQL-запросы в подключениях

UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL

Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).

Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.

Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.

Должно получится что-то вроде этого:

Строчка .CommandText = «SELECT…» — отвечает за SQL запрос. Если хотя бы немного почитать поисковую выдачу google по запросу QueryTable можно упростить код до следующего:

Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.

Заполнение нового столбца одинаковым значением

Переименование столбцов

Фильтрация записей

Сортировка

Агрегация записей

Дату можно впрямую через конструкцию

Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция — CONVERT(type,value)

С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:

Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59

А теперь — немного best practice!

Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:

Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).

Бонусы

Формировать более-менее сложный запрос SQL вручную в VBA мягко говоря неудобно. Поэтому я написал мини-макрос, который берет информацию из буфера обмена, и возвращает туда строчки для вставки в VBE.

Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.

И еще один небольшой бонус. Если у вас есть отчет по менеджерам/руководителям, построенный на запросах, то вам наверняка потребуется получать доступ к строке запроса через VBA. Сделать это можно через замечательную команду .CommandText — работает на чтение и запись. Мне для формирования отчета на 25 человек очень пригодился.

Spreadsheets Made Easy

How to Join Cells In Excel with a Comma (or other delimiter)

There are times where I need to join cells in Excel and separate them with a comma or some other text. I usually need this to make a SQL query or something like this. Whatever the case may be, Excel doesn’t seem to have an easy way to join multiple cells with a delimiter like a comma. So I’ve created a very simple VBA function to help us with this task.

The Goal

If you had cells A1:A3 like so:

And you wanted to join the text with a comma between them, one way you can do that is by using a regular formula where you concatenate the cells with ampersand (&):

However, this can become a lot of typing, especially if you have many cells you need to put together.

We can make this easier by creating a custom function in VBA that we can use in our spreadsheet.

The function will result with the same output, but with less typing:

Let’s create the VBA function Join() so that we can use it in our spreadsheet to make this task a lot easier.

Join Cells in Excel with a VBA Function

Let’s write a quick function you can write to help you join multiple cells with a delimiter. If you’re not very familiar with VBA, please see my Intro to VBA article.

Let’s break this down to understand what’s going on.

The function accepts a Cell Range as its first argument, then a string as its second argument. The function will result in a single String that will be returned to the calling Cell that uses the formula.

Here’s the next part of the function that actually builds the resulting string:

Here we loop through each cell in the rng variable and we start building the final string by using Join = . Notice that we are using the name of the Function here. You can think of this as an implicit variable that Excel will use that gets returned back to the spreadsheet.

Here, we build up the string cell by cell. If we had the cells:

And used this formula:

Here would be the breakdown of how the function works.

Читать еще: Excel две строчки в одной ячейке

At this point, Join = «» , cell.Text = «one» , and delimiter = «,» .

Right now, Join = «one,» , cell.Text = «two» , and delimiter = «,» .

The next time we go through the line in the For Each statement, it turns into this:

And in the third time we go through the loop:

Now the loop has finished, and we have Join = «one,two,three,» . However, we don’t want that last comma there, so we need to get rid of it. That’s when we execute the next line:

Which turns into:

If you’re not familiar with the Left(string, length) function, it’s very simple. It will take a string you give it, and only keep the amount of characters you specify. So, Left(«one,»,3) will take the string “one,” and only keep the first 3 characters (from the Left) and end up with “one”.

Also, the Len() function just figures out the Length a string has. So Len(«one») will return 3, for 3 characters. (Len is short for Length).

So why not just use Left(Join, Len(Join) – 1)?

The reason for this is if you don’t want a single character as a delimiter. Maybe instead of a comma, you want a specific piece of text between each string. For example, you could do:

Here, right before the last line we would end up with:

We would end up with:

Basically, we would only get rid of the last space at the end. This is why we should use the Length of the delimiter to remove from the end of the text. With that in mind, here’s what would happen at the end of the function:

Putting the New Formula to Use

To use the formula, simply type it into a cell like a regular formula:

And you’re not limited to a single character for a delimiter. You can have an entire word if you want:

I hope you found this post helpful. If you have any questions or have found a tweak to this function you’d like to share, I’d love to hear about it in the comments below!

Excel VBA Join функция

Я пытаюсь join элементы вектора в vba.

Сначала я ищу с помощью цикла for конкретные строки.

Затем я использую объект address, чтобы получить соответствующие значения в одном столбце справа.

Поэтому в векторе CE_cod я сохраняю все соответствующие значения (иногда это только одно значение, иногда более одного)

И теперь я хочу объединить все содержащиеся (текстовые) значения с»; » в качестве разделителя:

Предположим, что CE_cod имеет две записи в первом цикле: [«XXX», «YYY»] и только один во втором цикле: [«XXX»]

По какой-то причине результат в ячейке(j,3) в первом случае (несколько значений в CE_cod):»; YYY» (обратите внимание, что добавляется разделитель, но не само выражение). Во втором случае результат: «XXX»

Я проверил, если вектор CE_cod содержит все значения с:

и действительно, когда я делаю это «XXX» и «YYY» содержатся.

Так почему же функция join пропускает первую запись вектора, когда есть несколько записей?

1 Ответ

Я думаю, что ваш ce_cod заполняющий цикл должен быть ближе к этому.

Массив rn заполняется из Операции разделения и, как таковой, является нулевым массивом 1-D.

Кроме того, вы не должны использовать ReDim внутри цикла без параметра Preserve, если хотите сохранить свои значения. В любом случае, нет необходимости ReDim внутри цикла, так как вы заранее знаете целевые границы.

Vba excel join

VBA JOIN

Excel VBA JOIN Function

Examples of VBA Join Function

VBA Join – Example #1

VBA Join – Example #2

Things to Remember About VBA JOIN Function

Recommended Articles

Есть ли INNER JOIN в Excel?

4 ответа

Способ vba:

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Excel VBA. SQL-запросы в подключениях

Spreadsheets Made Easy

How to Join Cells In Excel with a Comma (or other delimiter)

The Goal

Join Cells in Excel with a VBA Function

So why not just use Left(Join, Len(Join) – 1)?

Putting the New Formula to Use

Excel VBA Join функция

1 Ответ

Похожие вопросы: