Скопировать переменную в буфер обмена vba excel
Привет, ребята! Мне требуется помощь: нужно в коде макроса (Excel) задать процедуру копирования переменной в буфер обмена. Коротко: задаю значение переменной, а затем нужно каким-то образом заставить это значение перейти в буфер обмена. Как это сделать? Если кто в курсе, подскажите, пожалуйста.
нажимаешь "Начать запись макроса"
выполняешь все то, что тебе надо
Нажимаешь "остановить запись макроса"
Переходишь в VBA-окошко и получаешь вагон счастья
Для начала, нужно понять, что такое буфер обмена и какие форматы он поддерживает.
Потом, нужно осознать, что значение переменной должно быть представлено в одном из этих форматов, так как другого способа что-то записать в буфер обмена нет.
Ну а дальше, нужно найти объект, который управляет буфером обмена.
P.S. есть подозрение, что преобразование значения в текст и копирование текста в буфер обмена - самое оно.
Нет, ребята, похоже, вы меня ещё не поняли. Вручную мне неинтересно - данных много, времени мало. Надо автоматически из кода копировать в буфер обмена. То, что вы предлагаете - попробуйте, получится ли? Ведь то, что я копирую в буфер обмена, мне из него же надо вставлять в другой программе, например, в 1С
Да, конечно, я не против преобразования значения в текст и его копирования в буфер обмена. Вопрос в том, какой рагмент кода VBA может заставить комп это сделать?
Азат, я тут уже два часа просветляюсь именно тем способом, какой ты мне советуешь. Попробуй сам: получится у тебя?
Dim d as DataObject
d.SetText("MyTextForBuffer")
d.PutInClipboard
d.GetFromClipboard
MsgBox d.GetText,0,"Clipboard однако"
(11) Он копирует значение переменной, но он не сказал, откуда берётся это значение (подозреваю, что где-то оно уже есть, но не в переменной).
Torquader, мой код до определённого места присваивает переменной некоторое значение из некоторой ячейки. А затем это значение мне нужно с помощью кода скопировать в буфер обмена. Вы, похоже, меня правильно понимаете.
Я попробовал использовать Ваш фрагмент кода, сделав модуль:
Sub Name()
Dim d As DataObject
d.SetText ("MyTextForBuffer")
d.PutInClipboard
d.GetFromClipboard
MsgBox d.GetText, 0, "Clipboard . "
End Sub
"User-defined type not defined"
Что бы это значило?
А то, что я, как всегда, поспешил - объект-то создать надо.
Dim d as New DataObject
или как там в VBA пишется - целый год уже туда не заглядывал ^_^
Range.PasteSpecial (специальная вставка) – это метод, который вставляет диапазон ячеек, скопированный в буфер обмена, из буфера обмена в указанное место на рабочем листе с учетом заданных параметров специальной вставки.
Синтаксис
Специальная вставка работает только с данными ячеек, скопированными в буфер обмена методом Range.Copy. При попытке применить метод Range.PasteSpecial к ячейкам, вырезанным в буфер обмена методом Range.Cut, возникает ошибка.
Параметры специальной вставки
Список параметров метода Range.PasteSpecial:
Параметры | Описание |
---|---|
Paste | Необязательный параметр. Константа из коллекции XlPasteType, указывающая на часть данных вставляемого диапазона, которую следует вставить. По умолчанию вставляются все данные. |
Operation | Необязательный параметр. Константа из коллекции XlPasteSpecialOperation, указывающая на математические операции, которые следует провести со скопированными данными и данными в ячейках назначения. По умолчанию вычисления не производятся. |
SkipBlanks | Необязательный параметр. Булево значение, которое указывает, вставлять ли в конечный диапазон пустые ячейки: True – не вставлять, False – вставлять (значение по умолчанию). |
Transpose | Необязательный параметр. Булево значение, которое указывает, следует ли транспонировать строки и столбцы при вставке диапазона: True – транспонировать, False – не транспонировать (значение по умолчанию). |
Смотрите другой способ транспонировать диапазоны ячеек и двумерные массивы.
Константы XlPasteType
Список констант из коллекции XlPasteType, которые могут быть использованы в качестве аргумента параметра Paste:
Константа | Значение | Описание |
---|---|---|
xlPasteAll | -4104 | Вставка всех данных (по умолчанию). |
xlPasteAllExceptBorders | 7 | Вставка всех данных, кроме границ. |
xlPasteAllMergingConditionalFormats | 14 | Вставка всех данных со слиянием условных форматов исходного и нового диапазонов. |
xlPasteAllUsingSourceTheme | 13 | Вставка всех данных с использованием исходной темы. |
xlPasteColumnWidths | 8 | Вставка ширины столбцов. |
xlPasteComments | -4144 | Вставка комментариев. |
xlPasteFormats | -4122 | Вставка форматов исходного диапазона. |
xlPasteFormulas | -4123 | Вставка формул. |
xlPasteFormulasAndNumberFormats | 11 | Вставка формул и форматов чисел. |
xlPasteValidation | 6 | Вставка правил проверки данных из ячеек исходного диапазона в новый диапазон. |
xlPasteValues | -4163 | Вставка значений. |
xlPasteValuesAndNumberFormats | 12 | Вставка значений и форматов чисел. |
Константы XlPasteSpecialOperation
Список констант из коллекции XlPasteSpecialOperation, которые могут быть использованы в качестве аргумента параметра Operation:
Константа | Значение | Описание |
---|---|---|
xlPasteSpecialOperationAdd | 2 | Скопированные данные будут добавлены к значениям в ячейках назначения. |
xlPasteSpecialOperationDivide | 5 | Скопированные данные разделят значения в ячейках назначения. |
xlPasteSpecialOperationMultiply | 4 | Скопированные данные будут перемножены со значениями в ячейках назначения. |
xlPasteSpecialOperationNone | -4142 | Вычисления не выполняются при вставке данных (по умолчанию). |
xlPasteSpecialOperationSubtract | 3 | Скопированные данные будут вычтены из значений в ячейках назначения. |
Примеры
Примеры копирования и специальной вставки актуальны для диапазона "A1:B8" активного листа, ячейки которого заполнены числами:
Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена. |
Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.
Метод Range.Copy
Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена. |
Метод Worksheet.Paste
Синтаксис
Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект. |
Link | Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию). |
В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).
Примеры
Вырезание и вставка диапазона одной строкой (перемещение):
Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование и вставка диапазона одной строкой:
Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:
18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”
Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2
а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:
Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )
Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?
Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :
Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.
(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)
Чтение из буфера обмена
Этот пример кода также доступен в качестве фрагмента кода IntelliSense. в средстве выбора фрагмента кода он находится в папке Windows Forms приложения в буфер обмена. Дополнительные сведения см. в статье Фрагменты кода.
Используйте метод GetImage для извлечения изображения из буфера обмена. Код в этом примере проверяет наличие изображения в буфере обмена, прежде чем извлекать его и назначать PictureBox1 .
Этот пример кода также доступен в качестве фрагмента кода IntelliSense. в средстве выбора фрагмента кода он находится в папке Windows Forms приложения в буфер обмена. Дополнительные сведения см. в разделе фрагменты кода.
Элементы, добавленные в буфер обмена, сохраняются даже после того, как работа приложения завершается.
Определение типа файла, хранящегося в буфере обмена
В буфере обмена могут храниться данные в различных форматах, включая тексты, звуковые файлы и изображения. Чтобы определить, файл какого типа содержится в буфере обмена, можно использовать такие методы как ContainsAudio, ContainsFileDropList, ContainsImage и ContainsText. Если вы используете для проверки пользовательский формат файла, можно использовать метод ContainsData.
Функция ContainsImage позволяет определить, являются ли данные в буфере обмена изображением. Следующий код проверяет, являются ли данные изображением, и выдает соответствующий отчет.
Очистка буфера обмена
Метод Clear очищает буфер обмена. Поскольку буфер обмена используют и другие процессы, очищение буфера может плохо на них повлиять.
В следующем примере кода демонстрируется применение метода Clear .
Запись в буфер обмена
Используйте метод SetText для записи текста в буфер обмена. Следующий код записывает в буфер обмена строку "This is a test string" (Это — тестовая строка).
Метод SetText может принимать параметр формата, который содержит тип TextDataFormat. Следующий код записывает строку "This is a test string" (Это — тестовая строка) в буфер обмена в формате RTF.
Используйте метод SetData для записи данных в буфер обмена. Код в этом примере записывает DataObject dataChunk в буфер обмена в пользовательском формате specialFormat .
Используйте метод SetAudio для записи звуковых данных в буфер обмена. Код в этом примере создает массив байтов musicReader , считывает в него файл cool.wav и записывает его в буфер обмена.
Поскольку к буферу обмена могут обращаться другие пользователи, не храните в нем конфиденциальные данные, включая пароли.
Иногда на то, чтобы придумать некоторые вещи, уходит очень много времени. Но когда их УЖЕ придумали, то постфактум они кажутся очевидными и даже банальными. Из серии "а что, так можно было?".
С самых первых версий в строке состояния внизу окна 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). Простор для фантазии большой.
Читайте также: