Буфер обмена Excel vba excel

Обновлено: 21.11.2024

В Excel много данных перемещается, возможно, путем копирования и вставки. Если вы оставите много информации в памяти или кеше, вы получите сообщение типа «вы оставили много информации в буфере обмена…». Как правило, в VBA этого можно избежать с помощью других методов, но иногда единственным способом является копирование и вставка. Как очистить буфер обмена в Excel VBA?

Это делается с помощью простой строки кода, которую вы размещаете после операции вставки:

Вы можете увидеть это в коде, записанном макросом, — он создается, когда вы нажимаете «Esc» на клавиатуре, чтобы остановить копирование, или если вы просто вставили. Эта строка кода очищает буфер обмена и кэш памяти.

Умная версия

Вы можете полностью избежать этого, полностью отключив буфер обмена. Это одна из тех вещей, которые вы можете сделать только с помощью VBA. У команды «Копировать» есть молчаливый аргумент «Назначение», который не очевиден, если вы не нажмете пробел сразу после инструкции «Копировать». Вы точно не получите его из макрорекордера.

Вот фрагмент кода, который я только что записал, копируя и вставляя блок с одного листа на другой:

Там обычно много лишнего кода. Строки, оканчивающиеся на «…Активировать», почти всегда бесполезны, указывая, какая ячейка выбрана — зачем это нужно в данном контексте, я не знаю. Вот урезанная версия:

Эта команда не использует буфер обмена, поэтому команда очистки буфера обмена Excel VBA не требуется. И, конечно, у нас одна строка кода вместо семи. Вообще чем меньше кода, тем быстрее выполнение, но есть много исключений.

Если вы хотите узнать об этом подробнее, введите бит .Copy и нажмите пробел. Изучите аргументы, чтобы лучше понять, как вы можете использовать эту команду.

Дополнительные баллы — работайте с любым диапазоном данных из любого места в книге

Если добавить к команде префикс исходного листа (в данном случае «Лист1») и добавить CurrentRegion за начальную ячейку, выбор будет продолжаться до тех пор, пока не будут получены все данные (он остановится на полностью пустых строках и полностью пустых столбцах). ):

Хотите стать экспертом в VBA? Так что это правильное место для вас. Этот блог в основном посвящен обучению применению Visual Basic для Microsoft Excel. Так что улучшите функциональность своих книг Excel с помощью этого блога. Также задавайте любые вопросы, касающиеся MS Excel и применения VBA. Мы будем рады помочь вам.

Страницы

Вставить содержимое буфера обмена на лист Excel с помощью VBA

Вам когда-нибудь приходилось вставлять содержимое буфера обмена на лист Excel? Контент в буфере обмена может быть скопирован с веб-страницы, программного обеспечения, файла Word или текстового файла и т. д. И контент может быть в любой форме. Это может быть текст, таблица, изображение и так далее. Поэтому в этом посте я научу вас, как это сделать с помощью VBA.

В этом методе сначала необходимо добавить ссылку на библиотеку объектов Microsoft Forms 2.0. Для этого перейдите в редактор VBA и выберите меню «Инструменты», а затем нажмите «Ссылки».

Затем поставьте галочку напротив Microsoft Forms 2.0 Object Library. Однако вы заметите, что приложение Excel автоматически добавляет ссылку на эту библиотеку, если вы добавляете форму в свой проект.

Затем добавьте этот код в модуль. В этом коде содержимое буфера обмена назначается переменной SText. Так что в конце вы можете вставить его в то место, где вам нужно. В этом примере содержимое вставляется в ячейку B2 активного листа.

Затемнить DataObj как MSForms.DataObject
Установить DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

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

На самом деле в этом методе мы используем очень простую теорию. Мы знаем, что Ctrl + v — это сочетание клавиш для вставки чего-либо на лист Excel. Поэтому вместо того, чтобы делать это вручную, мы можем дать эту команду через VBA следующим образом.

активный лист. Range("B2"). Выберите
SendKeys "^v"

Сначала мы выбираем ячейку B2 активного листа. Затем мы даем команду вставки, используя метод SendKeys. Используя этот метод, вы даже можете вставлять изображения, фигуры и таблицы из буфера обмена.

В этом учебном пособии по VBA показано, как копировать текст в буфер обмена, вставлять его из буфера обмена и очищать содержимое буфера обмена. Эти макросы работают с 32-разрядными версиями Excel, Outlook, Word и многими другими продуктами Microsoft Office.

Обновление 2018 г.: это руководство было обновлено, чтобы научить вас использовать VBA для копирования текста в буфер обмена в Windows 8 и Windows 10.В этом уроке я представлю два решения. Первое решение использует библиотеку объектов Microsoft Forms 2.0 для копирования текста в буфер обмена в Windows 7 и более ранних версиях. С выпуском Windows 8 и Windows 10 это решение больше не работает надежно, поэтому я представил второе решение, использующее вызовы Windows API для копирования и вставки с помощью VBA.

Давайте рассмотрим первое и самое старое решение:

Первоначальная настройка

Библиотека объектов Microsoft Forms 2.0

Чтобы получить доступ к методам буфера обмена в классе clipboard в старых версиях Windows, вы можете объявить ссылку на библиотеку объектов Microsoft Forms 2.0, выполнив следующие действия:

Откройте редактор VBA.
Нажмите Инструменты > Ссылки.
Установите флажок рядом с "Библиотека объектов Microsoft Forms 2.0".

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

Копировать VBA в буфер обмена

Решение 1. Windows 7 и более ранние версии

Чтобы скопировать в буфер обмена в Windows 7 и более ранних версиях, необходимо сначала объявить переменную типа MSForms.DataObject. DataObject будет мостом между вашими данными и вашим буфером обмена.

Копировать строку в буфер обмена

Метод SetText используется для сохранения строки в DataObject. Строка может быть переменной VBA, как показано выше, или ячейкой на листе, как показано ниже. Метод PutInClipboard — это волшебная штука, которая помещает текст в буфер обмена.

Копировать ячейку в буфер обмена

VBA довольно хорошо преобразует типы данных в строки перед сохранением их в буфере обмена, поэтому вам не нужно беспокоиться об использовании функции CStr для ручного преобразования.

Хотя я сказал, что это решение для Windows 7 и более ранних версий, следующее решение также работает для Windows 7 и более ранних версий. Дополнительным преимуществом является возможность работы и с более новыми версиями Windows.

Решение 2. Windows 8 и Windows 10

В Windows 8 и Windows 10 существует небольшая ошибка, если хотите, которая не позволяет библиотеке объектов Microsoft Forms 2.0 копировать текст в буфер обмена. Обходной путь для этого — использовать вместо этого вызовы Windows API. Хотя этот макрос VBA предназначен для использования в более новых версиях Windows, он будет успешно скопирован в буфер обмена во всех версиях Windows.

Эти макросы намного длиннее, чем предыдущее решение, но их по-прежнему легко использовать, поскольку все, что вам нужно сделать, это скопировать и вставить. Во многих отношениях это решение на самом деле проще в использовании, потому что вам не нужно добавлять ссылку на библиотеку объектов Microsoft Forms 2.0, чтобы добавить текст в буфер обмена! Вам по-прежнему нужно будет добавить ссылку на библиотеку объектов Microsoft Forms 2.0 для вставки из буфера обмена, так что вы можете добавить ее.

Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA

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

Чтобы поместить текст в буфер обмена, вам нужно просто передать макросу ClipBoard_SetData аргумент, содержащий строку, которую вы хотите сохранить в буфере обмена, например так:

Это решение было адаптировано из макроса, который я видел здесь.

Вставить VBA из буфера обмена

Как только вы научитесь копировать, вставка станет проще простого. Синтаксис практически идентичен показанным ранее примерам копирования Windows 7. Взгляните.

Получить содержимое буфера обмена

Метод GetFromClipboard извлекает данные из буфера обмена и сохраняет их в объекте DataObject. Метод GetText берет строку из DataObject и помещает ее в вашу строковую переменную — в данном случае в строковую переменную str1. Вы успешно вставили содержимое буфера обмена в строковую переменную, которой можно управлять в макросе VBA.

Очистить буфер обмена VBA

Вы спросите, как очистить содержимое буфера обмена? Есть несколько способов очистить буфер обмена. Самый простой — установить буфер обмена равным пустой строке.

Установить буфер обмена на пустую строку

Windows 7 и более ранние версии

Windows 8 и более поздние версии

Более тщательный способ очистки буфера обмена с помощью VBA — воспользоваться нашим хорошим другом, библиотекой user32.

Очистить все содержимое буфера обмена

Обратите внимание, что частные объявления должны быть вставлены в самый верх вашего модуля перед любыми процедурами. Если вы уже вставили большой макрос «Решение 2» для копирования и вставки в буфер обмена, вам не нужно будет снова добавлять частные объявления, поскольку они уже там.

Заключительные мысли

Возможность управлять буфером обмена — мощная функция VBA. Вы можете использовать его для автоматизации сложных задач.Например, в своей карьере инженера-ядерщика я регулярно работаю с большими файлами ASCII на машинах UNIX. Я скопирую текст в буфер обмена, запущу макрос VBA в Windows, чтобы выполнить манипуляцию со строками в тексте, а затем сохраню окончательный контент в желаемом формате обратно в буфер обмена. Затем я буду использовать эту новую отформатированную строку, чтобы продолжить свою работу над UNIX-системами. Манипуляции с буфером обмена — прекрасный постпроцессор!

Подпишитесь на нашу серию электронных писем VBA Insiders, чтобы получить дополнительные советы, приемы и тактики VBA, используя форму ниже.

Поделитесь этой статьей в Twitter и Facebook, а затем оставьте комментарий ниже, и давайте обсудим.

Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.

Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.

Я пытаюсь добавить в электронную таблицу кнопку, при нажатии которой определенный URL-адрес копируется в буфер обмена.

У меня были некоторые познания в Excel VBA, но это было давно, и я борюсь.

Добро пожаловать в stackoverflow! Если бы вы могли поделиться тем, что пробовали до сих пор, у вас больше шансов получить помощь в решении вашей проблемы.

8 ответов 8

Я оставляю здесь свой первоначальный ответ только для справки:

отличная помощь, и хотя полагаясь на волшебные строки, аккуратно сохраняет дополнительную ссылку на сторонние dll и т. д. плюс 1

@Jroonk этот метод не работает (по крайней мере, в Windows 10, не уверен в других версиях Windows), если проводник открыт для папки. В зависимости от кодировки места назначения вставки он может вставляться как ?? или \xEF\xBF\xBF\xEF\xBF\xBF .

Чтобы записать текст (или прочитать текст) в буфер обмена Windows, используйте эту функцию VBA:

Это решение, которое НЕ использует MS Forms и Win32 API. Вместо этого он использует библиотеку объектов Microsoft HTML, которая является быстрой и вездесущей и НЕ рекомендуется Microsoft, как MS Forms. И это решение учитывает переводы строк. Это решение также работает в 64-разрядной версии Office. Наконец, это решение позволяет как записывать, так и читать из буфера обмена Windows. Ни одно другое решение на этой странице не обладает такими преимуществами.

Решение проблемы с недопустимым аргументом (работает в 64-разрядной версии Excel 2010). Второй аргумент .setData должен быть Variant (также работает строковый литерал, такой как "test"), а не переменной типа String. Создайте переменную Variant, назначьте ей s и используйте ее вместо s в .setData, и все будет работать нормально.

Самый простой (не Win32) способ — добавить пользовательскую форму в проект VBA (если у вас ее еще нет) или добавить ссылку на библиотеку объектов Microsoft Forms 2, а затем из лист/модуль вы можете просто:

Это метод, который я использую, но я обнаружил, что он не работает, если папка открыта в проводнике в Windows 10. Я не могу комментировать другие версии Windows.

Читайте также: