Vba excel метод paste

Обновлено: 07.07.2024

Методы класса Word.Selection – выделение текста в Word

Я приведу по два примера программного кода на языках JScript, VBScript и VBA. Хотя по сути, код под макрос (VBA) и сценарий VBScript особо не отличается. Для тех, кто забыл: для создания макроса в документе Microsoft Office нужно вызвать редактор Visual Basic for Application (комбинация клавиш Alt + F11), далее, добавить в проект новый модуль (макрос).

Методы с префиксом Clear… позволяют очистить выделенный материал от форматирования (абзацы, стили, символы и так далее):

ClearCharacterAllFormatting () - Все форматирование.

ClearCharacterDirectFormatting () - Форматирование знаков.

ClearCharacterStyle () - Форматирование знаков, применяемых через стили.

ClearFormatting () – Все форматирования (параграфы, стили и так далее).

ClearParagraphAllFormatting () - Форматирование абзаца.

ClearParagraphDirectFormatting () - Форматирование абзацев, применяемых вручную.

ClearParagraphStyle () - Форматирование абзацев, применяемых через стили.

Collapse (Direction) – Позволяет убрать выделение текста Word и переместить указатель в начало или конец. VBA Selection. Если параметры отсутствуют, то просто убирается выделение. Параметр Direction содержит значение константы WdCollapseDirection :

wdCollapseEnd - 0 – Указатель вконец
wdCollapseStart - 1 – Указатель вначале

ConvertToTable (Separator, NumRows, NumColumns, InitialColumnWidth, Format, ApplyBorders, ApplyShading, ApplyFont, ApplyColor, ApplyHeadingRows, ApplyLastRow, ApplyFirstColumn, ApplyLastColumn, AutoFit, AutoFitBehavior, DefaultTableBehavior) – Довольно мощный метод, который позволяет преобразовать выделенный Word текст в таблицу.

Separator - Символ-разделитель, может быть знаком или значением константы WdTableFieldSeparator:

wdSeparateByParagraphs - 0 - абзаца.
wdSeparateByTabs - 1 - табуляции.
wdSeparateByCommas - 2 - запятая.
wdSeparateByDefaultListSeparator - 3 - разделитель списка по умолчанию.

NumRows - Количество строк.

NumColumns - Количество столбиков.

InitialColumnWidth - Начальная ширина каждого столбца, в пунктах.

Format – Определяет формат таблицы и содержит значение константы WdTableFormat.

ApplyBorders - TRUE - применять границы.

ApplyShading - TRUE - применить затенение.

ApplyFont - TRUE применять свойства шрифтов.

ApplyColor - TRUE применять цветовые свойства.

ApplyHeadingRows - TRUE - применить свойства заголовок-строка.

ApplyLastRow - TRUE - применить свойства последней строке.

ApplyFirstColumn - TRUE - применить свойства первого столбцов.

ApplyLastColumn - TRUE - применить свойства последнего столбца.

AutoFit - TRUE - уменьшить ширину столбцов таблицы как можно больше.

AutoFitBehavior - Устанавливает правила авто-подбора (если DefaultTableBehavior содержит значение wdWord8TableBehavior, то этот аргумент игнорируется, VBA Selection), содержит значения константы WdAutoFitBehavior:

wdAutoFitContent - 1 - автоматически размер
wdAutoFitFixed - 0 - фиксированный размер
wdAutoFitWindow - 2 - автоматический размер по ширине активного окна

DefaultTableBehavior - Значение, указывающее, следует ли автоматически изменять размер ячеек таблицы по содержимому. Selection VBA. Содержит значение константы WdDefaultTableBehavior:

wdWord8TableBehavior - 0 - отключает Авто-подбор (по умолчанию)
wdWord9TableBehavior - 1 - включить Авто-подбор

Хорошо, с теоретической частью мы закончили, теперь можно приступить к программированию.

Примера на языке VBScript – выделение текста в редакторе Word

В данном примере в цикле For (смотрите статью "Урок 6 по VBScript: Циклы for…next и for each…next"), который выполняется шесть раз, происходит вставка перевода на новую строку, вставка текстовой фразы и перемещаем курсов к конец строки. С помощью оператора with мы можем экономить размер программного кода – не нужно лишний раз писать имя класса. Вконце происходит выделение текста Word и преобразование его в таблицу.

Примера на языке JScript – выделение текста в редакторе Word

Логика работы данного программного кода аналогичны предыдущему примеру, тут мы уже использовали цикл for языка JS, читайте в статье "Урок 9 по JScript - оператор цикла for". Тут тоже использовался оператор with, обратите внимание, что имена переменных изменились, а в конце каждой строки кода прописана точка с запятой.

Примера на языке Visual Basic for Application – выделение текста в редакторе Word

Хорошо, давайте продолжим рассматривать методы класса Selection.

EndOf () – Перемещает курсор в конец выделения.

StartOf () – Перемещает курсор в начало выделения.

SetRange (start, end) – Позволяет задать начальное и конечное значение для выделения текста Word.

TypeText () – Выполняет ту же функцию, что и свойство Text – ввод информации.

TypeParagraph () - Метод вставляет параграф.

Copy (), Cut (), Paste (), Delete () – Копирует, вырезает, вставляет и удаляет выделенный текст в Word.

CopyAsPicture () – Копирует выбранный материал как изображение.

И так, теперь рассмотрим программный код с данными методами.

По сути, данный программный код практически аналогичен предыдущим. Тут в цикле происходит вставка данных несколько раз, а далее следуют операции выделения текста в документе Word, его копирование и вставка.

Ну и в заключение, привожу программный код для макроса:

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

Спасибо за внимание. Автор блога Владимир Баталий

Ничто в мире не идеально, и Excel тоже. Как и любая программа он порой может сильно удивлять разными "непонятками". Вот очередная шутка: казалось бы простой код по копированию и вставке картинки из листа Excel, который отлично работает в 2010, вылетает в 2016 с ошибкой Метод paste из класса worksheet завершен неверно :

Сам код простой и ошибки в общем-то вызывать не должен:

Sub CopyPastePicture() ActiveWorkbook.Sheets("PICS").Shapes("Picture1").Copy ActiveWorkbook.Sheets("MAIN").Paste End Sub

При этом самое печально то, что это даже не на каждом ПК проявляется. А при пошаговой отладке кода и вовсе пропадает. Т.е. для получения ошибки недостаточно одного Excel 2016, здесь влияет несколько факторов: установленные программы, операционная система, метод выполнения и т.д. и т.п. Разбирать каждый частный случай не представляется возможным. Да и даже если найти причину - что, теперь надо удалять все лишнее, что не понравилось Excel-ю? А почему тогда это лишнее не мешает тому же коду в Excel 2010? А если этот код - часть программы на заказ? Заказчик скажет "Тыжпрограммист" и будет прав - это наша проблема, проблема разработчиков. Мы обязаны знать эти подводные камни или как минимум хоть уметь вовремя их устранять. Поэтому приходится искать обходные пути. Судя по ошибке, сам корень зла где-то по пути от буфера к Excel. Возможно, наша скопированная картинка просто не до конца "прогрузилась" в буфер и надо дождаться завершения этой прогрузки. Текст ошибки несколько укрепляет это предположение. Первый порыв - использовать DoEvents, чтобы передать эстафету операционной системе - дать ей завершить свои процессы, в том числе и обработку буфера обмена:

Sub CopyPastePicture() ActiveWorkbook.Sheets("PICS").Shapes("Picture1").Copy DoEvents ActiveWorkbook.Sheets("MAIN").Paste End Sub

Но это не спасает ситуацию. Равно как не спас и цикл с сотней DoEvents:

Sub CopyPastePicture() Dim i As Long ActiveWorkbook.Sheets("PICS").Shapes("Picture1").Copy For i = 1 To 100: DoEvents: Next ActiveWorkbook.Sheets("MAIN").Paste End Sub

С одной стороны все логично и должно работать. И даже работает, но не всегда - ошибка все равно появлялась чуть ли не в половине случаев. Почему? Потому что дело все же в некорректной работе буфера. И DoEvents хоть и передавал управление - проблемы вовсе не решал. Он просто давал небольшую отсрочку, которая позволяла в ряде случаев картинке догрузиться в буфер и избежать ошибки. Но главная проблема в том, что неизвестно для какого ПК сколько таких циклов надо, потому что неизвестно сколько ждать до полной загрузки картинки в буфер. Неизвестно, т.к. на каждом ПК это может быть разное время. В итоге, помучившись еще какое-то время я нашел "костыльное" решение проблемы через такой код:

Sub CopyPastePicture() 'сначала очистим буфер, чтобы там точно ничего лишнего не было Application.CutCopyMode = False 'копируем картинку ActiveWorkbook.Sheets("PICS").Shapes("Picture1").Copy 'а теперь разрешаем пропуск всех ошибок! On Error Resume Next Err.Clear 'очищаем лог ошибок, если они были 'пробуем вставить нашу картинку ActiveWorkbook.Sheets("MAIN").Paste 'если в момент вставки возникла ошибка ' сработает цикл, который будет выполняться до тех пор, ' пока что-то все же не вставится Do While Err.Number <> 0 Err.Clear ActiveWorkbook.Sheets("MAIN").Paste 'передаем управление системе DoEvents Loop 'отключаем пропуск ошибок On Error GoTo 0 'опять очищаем буфер - теперь уже от того, что скопировали сами кодом Application.CutCopyMode = False End Sub

Решение основано на том, что мы сначала копируем картинку, а потом пробуем её вставить. Если возникает ошибка - запускаем цикл, в котором пытаемся вставить картинку из буфера до тех пор, пока она все же не будет вставлена. Т.е. пока ошибка возникает - картинка не прогрузилась и цикл работает. Как только картинка прогрузилась в буфер - она вставляется без ошибки и цикл завершается.
И такой подход работает и по скорости не сильно тормозит процесс. Единственное, что неплохо было бы добавить - так это некий счетчик. Вдруг по какой-то причин буфер вообще очистился(ну бывают системные ошибки) или ошибка возникала совсем по другой причине(например, вставка производится на защищенный лист) - тогда получим бесконечный цикл. В этом случае лучше всего код "обернуть" в функцию:

тогда можно будет не только вставить картинку, но и получить обратную связь - успешно прошла вставка или нет. Если кажется, что 1000 попыток это много, то можно просто в строке If lPasteCnt > 1000 Then вместо 1000 указать нужное число.
А использовать приведенную функцию можно будет так:

Т.е. мы вызываем функцию, которая пробует вставить картинку. Если все 1000 попыток были безуспешными, то функция вернет значение False . Если же хоть одна вставка удалась - функция вернет True .
Кстати, функция поможет сделать вставку не только картинки, но и любой другой фигуры, у которой есть метод Copy: рисунок, фигура, диаграмма.

Если тоже столкнулись с такой проблемой - делитесь в комментариях кто как решал и что помогло. Соберем подборку методов :)

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:

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

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

Пример 2

В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.

Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ' Создаем новый лист

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ' Находим Лист1

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются "Sheet1", "Sheet2" и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств и методов:

Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл -> Параметры страницы.
свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

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