Передача любой информации между приложениями VBA с использованием буфера обмена
Как раз тогда, когда я подумал, что я закончил с моей последней партией советов и приемов VBA , я обнаружил еще одну по-настоящему классную технику, которую каждый может использовать для простой передачи информации между двумя приложениями, которые используют VBA в качестве внутреннего языка сценариев.
Отличительной особенностью VBA является то, что он предоставляет вам так много функций и инструментов. , которые обычно являются частью приложений VB, хотя и в несколько урезанной версии.
Одна из самых неприятных частей работы с VBA в конкретных приложениях заключается в том, что не всегда легко заставить два приложения «общаться» друг с другом. Это потому, что все переменные, которые вы используете, доступны только в рамках самого приложения, а не где-либо еще на компьютере.
С учетом вышесказанного существуют способы передачи информации. между приложениями на основе VBA, такими как Word или Excel. Во многих случаях, даже в профессиональной среде, программисты прибегают к обмену данными между приложениями с помощью файлов данных. Это может сработать, но оно вводит элемент потенциального вмешательства или ошибки — например, удаление или изменение этих файлов — чтобы испортить работу.
Еще один подход, который вы можете использовать для очень быстрой передачи информации между приложениями, — это передача данных в буфер обмена, а затем чтение этой информации из другого приложения.
Передача информации с помощью буфера обмена
В этом примере я покажу вам, как передать три фрагмента текстовой информации — значения внутри 3 текстовых полей — непосредственно в электронную таблицу Excel, которую запускает макрос Word.
Начальная настройка показана ниже. Это документ Word, в котором я переключился в режим разработки и создал 3 метки и 3 текстовых поля, чтобы пользователь мог вводить информацию. При нажатии на кнопку «Отправить» запускается код передачи данных. 
Дважды щелкните по новой кнопке, которую вы создали в режиме разработки, чтобы попасть в экран VB Editor. Первое, что вам нужно сделать, это добавить ссылку, которая дает вам доступ к системному буферу обмена. Нажмите на Инструменты — Ссылки. 
Прокрутите список литературы и выберите «Библиотека объектов Microsoft Forms 2.0». После того, как вы нажмете Okay, ваш сеанс редактирования VB теперь будет иметь доступ к методам, которые позволяют вам читать или писать из буфера обмена.
Это именно то, что мы собираемся сделать сейчас. В функции для кнопки «Отправить» я ввел следующий код.
Смотри как это просто? Объект данных strClipText — это объект буфера обмена, которым вы можете манипулировать. Во-первых, прочитайте все значения полей в единую строковую переменную, где каждое поле данных будет разделено запятой или любым другим, что вам подходит.
Затем вам нужно будет создать файл Excel, который вы открываете в команде Shell выше. В Excel вы захотите добавить ту же ссылку в «Библиотеку объектов Microsoft Forms 2.0», а затем в функции Workbook.Open () вы можете запустить следующий скрипт.
Опять же, когда вы проходите через это, это простой и очень быстрый скрипт. Он создает объект MSForms, получает самые последние данные из буфера обмена и помещает их в строковую переменную. Затем он разбивает эту единственную длинную строковую переменную по любому разделителю, который вы использовали, на массив отдельных строк, и, наконец, проходит через массив (CountA дает вам длину массива) и выводит каждое значение на лист в столбце 2.
Вот окончательные результаты.
Теперь, проявив немного творческого подхода, вы можете использовать эту технику для запуска любого приложения на основе VBA и вставки в него информации.
Вы всегда можете сделать то же самое, используя справочную библиотеку Excel, но в этом случае вы будете молча открывать файл Excel и манипулировать данными внутри этого файла. В приведенном выше случае вы буквально открываете приложение Excel и отображаете файл, а оставшаяся часть работы позволяет запускать макрос в Excel.
Это наиболее полезно, когда есть другие приложения, такие как многие приложения интерфейса оператора на многих производственных объектах, которые основаны на VBA, но могут не иметь этих библиотек, доступных для тесной связи двух приложений. Использование буфера обмена в автоматическом режиме, как это быстро, легко и выполняет свою работу.
Попробуйте приведенные выше сценарии и дайте нам знать, как они работают в вашей системе. Вы должны были настроить это? Есть ли у вас какие-либо предложения о том, как сделать код еще лучше? Поделитесь своими мыслями в разделе комментариев ниже.
VBA Excel. Буфер обмена (копирование, вставка, очистка)
Копирование содержимого и форматов ячеек (диапазона) в буфер обмена осуществляется методом Range.Copy, а вставка – методом Worksheet.Paste:
При вставке диапазона ячеек из буфера обмена на рабочий лист достаточно указать верхнюю левую ячейку места (диапазона) вставки.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, формулы, примечания и т.д.), а также применения к диапазону транспонирования или вычислений, используется метод Range.PasteSpecial (специальная вставка).
Буфер обмена и переменная
Передача текста между переменной и буфером обмена в VBA Excel осуществляется с помощью объекта DataObject. Стоит иметь в виду, что на некоторых компьютерах DataObject может некорректно работать при открытом окне проводника.
Объект DataObject
Подробнее об элементе DataObject вы можете прочитать на сайте разработчиков.
Методы объекта DataObject:
| Метод | Описание |
|---|---|
| GetFromClipboard | Копирует данные из буфера обмена в DataObject |
| GetText | Извлекает текстовую строку из объекта DataObject в указанном формате |
| PutInClipboard | Перемещает данные из DataObject в буфер обмена |
| SetText | Копирует текстовую строку в DataObject, используя указанный формат |
Копирование текста из переменной в буфер обмена
Копирование текста из буфера обмена в переменную
Очистка буфера обмена
Специального метода для очистки буфера обмена в VBA Excel нет. Для решения этой задачи можно использовать выход из режима вырезания-копирования:
Следующий пример демонстрирует вставку скопированной ячейки «A1» в ячейки «A2» и «A3» и отсутствие вставки в ячейки «A4» и «A5» после строки Application.CutCopyMode = False :
Оператор On Error Resume Next необходим для обработки (пропуска) ошибки, возникающей при вставке из пустого буфера обмена.
Функции для работы с буфером обмена
В некоторых системах, начиная с Windows 8, метод DataObject.PutInClipboard не работает правильно: если открыт хотя бы один экземпляр Проводника (папка), в буфер обмена записываются два квадратика. Следующие функции должны решить эту проблему:
Пример использования функций для работы с буфером обмена:
7 комментариев для “VBA Excel. Буфер обмена (копирование, вставка, очистка)”
Код — не работает ▼
В А1 вставляется какой-то непечатаемый символ и всё. При переходе к другому приложению и попытке вставить содержимое буфера — фиаско.
Добрый день, Михаил!
Такое встречается, начиная с Windows 8, когда открыта хотя бы одна папка в Проводнике. Я добавил в статью функции для работы с буфером обмена, они должны работать.
В Win 8.1 такой проблемы не наблюдаю, уж по всякому погонял, пытаясь вызвать ошибку — всегда правильно отрабатывает ))
А за статью большое спасибо!
Нашёл такую информацию на случай получения ошибки относительно типа данных DataObject.
Чтобы заработал тип данных «DataObject» нужно подключить инструмент «Microsoft Forms Object Libraries». Для этого необходимо выполнить действия в редакторе макросов Эксель:
1. Открыть Tools/References;
2. Через кнопку Browse открыть файл C:\WINDOWS\SYSTEM32\FM20.DLL. Файл «FM20.DLL» может оказаться в другой подпапке Windows, поискать по названию. Может быть в «SysWOW64» или в «System».
Работа с буфером обмена (Windows Clipboard) из VBA
ВНИМАНИЕ: для работы функций требуется установка ссылки на библиотеку Microsoft Forms 2.0 Object Library:
Аналогичная функция ClipboardText (чтение текста из буфера обмена), но не требующая подключения библиотеки:
Копировать сумму выделенных ячеек в Буфер обмена
Иногда на то, чтобы придумать некоторые вещи, уходит очень много времени. Но когда их УЖЕ придумали, то постфактум они кажутся очевидными и даже банальными. Из серии «а что, так можно было?».
С самых первых версий в строке состояния внизу окна Microsoft Excel традиционно отображались итоги по выделенным ячейкам:
При желании, можно было даже щёлкнуть по этим итогам правой кнопкой мыши и выбрать в контекстном меню, какие именно функции мы хотим видеть:
И только совсем недавно в последних обновлениях Excel разработчики Microsoft добавили простую, но гениальную фишку — теперь при щелчке мышью по этим итогам они копируются в буфер!
Но что делать тем, у кого пока (или уже?) нет такой версии Excel? Тут могут помочь несложные макросы.
Копирование суммы выделенных ячеек в Буфер с помощью макроса
Откройте на вкладке Разработчик (Developer) редактор Visual Basic или воспользуйтесь для этого сочетанием клавиш Alt + F11 . Вставьте новый пустой модуль через меню Insert — Module и скопируйте туда следующий код:
Логика его работы проста:
- Сначала идёт «защита от дурака» — мы проверяем что именно выделено. Если выделены не ячейки (а, например, диаграмма), то выходим из макроса.
- Затем при помощи команды GetObject мы создаем новый объект данных, где будет храниться впоследствии наша сумма выделенных ячеек. Длинный и непонятный буквенно-цифровой код — это, на самом деле, ссылка на ветку реестра Windows, где лежит библиотека Microsoft Forms 2.0 Object Library, которая умеет создавать такие объекты. Иногда такой трюк ещё называют неявным поздним связыванием. Если его не использовать, то пришлось бы заранее делать в файле ссылку на эту библиотеку через меню Tools — References.
- Сумма выделенных ячеек считается командой WorksheetFunction.Sum(Selection), а затем полученная сумма помещается в буфер обмена командой PutInClipboard
Для удобства использования можно, конечно же, повесить этот макрос на сочетание клавиш с помощью кнопки Макросы на вкладке Разработчик (Developer — Macros) .
А если хочется видеть, что именно скопировалось после выполнения макроса, то можно включить панель Буфер обмена с помощью маленькой стрелки в правом нижнем углу соответствующей группы на Главной (Home) вкладке:
Не только сумма
Если кроме банальной суммы хочется что-то ещё, то можно воспользоваться любой из функций, которую нам предоставляет объект WorksheetFunction:
Например, там есть:
- Sum — сумма
- Average — среднее арифметическое
- Count — количество ячеек с числами
- CountA — количество заполненных ячеек
- CountBlank — количество пустых ячеек
- Min — минимальное значение
- Max — максимальное значение
- Median — медиана (центральное значение)
- . и т.д.
С учетом фильтров и скрытых строк-столбцов
Что если в выделенном диапазоне окажутся скрытые (вручную или фильтром) строки или столбцы? Чтобы не учитывать их в итогах, нужно будет чуть-чуть модифицировать наш код, добавив к объекту Selection свойство SpecialCells(xlCellTypeVisible):
В этом случае подсчет любой функции итога будет применён только к видимым ячейкам.
Если нужна живая формула
Если пофантазировать, то можно придумать сценарии, когда в буфер лучше скопировать не число (константу), а именно живую формулу, которая подсчитывает нужные нам итоги по выделенным ячейкам. В этом случае придётся склеить формулу из фрагментов, добавив к ней дополнительно удаление знаков доллара и замену запятой (которая используется как разделитель адресов нескольких выделенных диапазонов в VBA) на точку с запятой:
Суммирование с дополнительными условиями
Ну и, наконец, для совсем уже маньяков можно написать макрос, который будет суммировать не все выделенные ячейки, а только те, что удовлетворяют заданным условиям. Так, например, будет выглядеть макрос помещающий в Буфер сумму выделенных ячеек, если их значения больше 5 и при этом они залиты любым цветом:
Как легко сообразить, условия можно задать абсолютно любые — вплоть до форматов ячеек — и в любых количествах (в том числе, связывая их между собой логическими операторами or или and). Простор для фантазии большой.