Vba excel метод pastespecial завершен неверно

Обновлено: 07.07.2024

Может кто подскажет, как посредством Delphi скопировать данные из одной книги (Excel) и вставить их в другую(Excel).

Использую такой код, но программа аварийно завершается.

.
var
XL_1: Variant;
XL_2: Variant;
.
XL_2.Range[ 'c6' , 'c12' ].Copy(EmptyParam);
XL_1.Range[ 'd9' , 'd15' ].PasteSpecial(,,False,False);
.

выводит ошибку: "Метод PasteSpecial из класса Range завершен неверно"

Есть предположение, что необходимо активировать первую книгу для того, чтобы вставить данные из буфера обмена, но не получается (делал через XL_1.WorkBooks[1].Activate;).

Отслеживать ответы на этот вопрос по RSS

17-05-2012 06:16
Задача: скопировать из Word'a текст и таблицу через буфер в Excel. Как известно, Excel меняет формат данных при ставки, например, двадцатизначный счёт 40702810000400002588 преобразует в 4,07028E+19 .
Решение: в Word выделяем текст и копируем его в буфер. С этим проблем нет, стандартный vba справляется.
в Excel вставляем следующим кодом:

.
var Excel, WorkBook, Sheet:Variant;
myRange: OleVariant;
begin
Excel := CreateOleObject( 'Excel.Application' );
WorkBook:=Excel.WorkBooks.add;
Sheet := Workbook.Sheets.Item[ 1 ];
Excel.Application.Cells.Select;
Excel.Application.Selection.NumberFormat:= '@' ;

Excel.Application.Selection.Range[ 'A1' ,EmptyParam].PasteSpecial(xlPasteValues. );
Excel.visible:=true;
. //Дальше сохраняем, преобразуем и т.д.
end ;

Спасибо огромное kilobait80 , Работает только так: PasteSpecial(xlPasteValues. );

Sheet1.Range[ 'A1:C10' , EmptyParam].Copy(EmptyParam);
Sheet2.Range[ 'B5' , EmptyParam].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, EmptyParam, EmptyParam);
XL.CutCopyMode[ 0 ] := 0 ;

Смотрите описание PasteSpecial и констант - может пригодиться:

function ExcelRange.PasteSpecial(Paste: XlPasteType; Operation: XlPasteSpecialOperation;
SkipBlanks: OleVariant; Transpose: OleVariant): OleVariant;

type
XlPasteType = TOleEnum;
const
xlPasteAll = $FFFFEFF8 ;
xlPasteAllExceptBorders = $00000007 ;
xlPasteFormats = $FFFFEFE6 ;
xlPasteFormulas = $FFFFEFE5 ;
xlPasteComments = $FFFFEFD0 ;
xlPasteValues = $FFFFEFBD ;
xlPasteColumnWidths = $00000008 ;
xlPasteValidation = $00000006 ;
xlPasteFormulasAndNumberFormats = $0000000B ;
xlPasteValuesAndNumberFormats = $0000000C ;

type
XlPasteSpecialOperation = TOleEnum;
const
xlPasteSpecialOperationAdd = $00000002 ;
xlPasteSpecialOperationDivide = $00000005 ;
xlPasteSpecialOperationMultiply = $00000004 ;
xlPasteSpecialOperationNone = $FFFFEFD2 ;
xlPasteSpecialOperationSubtract = $00000003 ;

Ну и огласим весь список Microsoft Excel Constants

Но сработала вот такая конструкция .PasteSpecial(xlPasteValues. );

где
const
xlPasteValues = -4163;

Причем метод PasteSpecial с параметрами по умолчанию не работает.

uses ExcelXP;
.
procedure TForm1.ButtonRangeCopyExampleClick(Sender: TObject);
var
XL: TExcelApplication;
Sheet1, Sheet2: ExcelWorksheet;
begin
XL := TExcelApplication.Create( nil );
try
XL.ConnectKind := ckNewInstance;
XL.AutoQuit := False;
XL.Visible[ 0 ] := True;
// открывем первую книгу и назначаем получаем ссылку на активеый лист
Sheet1 := XL.Workbooks.Open( 'Книга1.xls' ,
False, // UpdateLinks: OleVariant;
False, // ReadOnly: OleVariant;
EmptyParam, // Format: OleVariant;
EmptyParam, // Password: OleVariant;
EmptyParam, // WriteResPassword: OleVariant;
EmptyParam, // IgnoreReadOnlyRecommended: OleVariant;
EmptyParam, // Origin: OleVariant;
EmptyParam, // Delimiter: OleVariant;
EmptyParam, // Editable: OleVariant;
EmptyParam, // Notify: OleVariant;
EmptyParam, // Converter: OleVariant;
False, // AddToMru: OleVariant;
EmptyParam, // Local: OleVariant;
EmptyParam, // CorruptLoad: OleVariant;
0 // lcid: Integer
).ActiveSheet as ExcelWorksheet;
// открывем вторую книгу и назначаем получаем ссылку на активеый лист
// последняя открытая книга становится активной
Sheet2 := XL.Workbooks.Open( 'Книга2.xls' ,
False, // UpdateLinks: OleVariant;
False, // ReadOnly: OleVariant;
EmptyParam, // Format: OleVariant;
EmptyParam, // Password: OleVariant;
EmptyParam, // WriteResPassword: OleVariant;
EmptyParam, // IgnoreReadOnlyRecommended: OleVariant;
EmptyParam, // Origin: OleVariant;
EmptyParam, // Delimiter: OleVariant;
EmptyParam, // Editable: OleVariant;
EmptyParam, // Notify: OleVariant;
EmptyParam, // Converter: OleVariant;
False, // AddToMru: OleVariant;
EmptyParam, // Local: OleVariant;
EmptyParam, // CorruptLoad: OleVariant;
0 // lcid: Integer
).ActiveSheet as ExcelWorksheet;
// Копируем данные из области A1:C10 листа Книги 1 в область B5 Книги 2
// Нам не нужно делать активной книгу 1 для чтения/запси данных из Sheet1
Sheet1.Range[ 'A1:C10' , EmptyParam].Copy(Sheet2.Range[ 'B5' , EmptyParam]);
finally
FreeAndNil(XL);
end ;
end ;

var
XL_1: Variant;
XL_2: Variant;
Mas: Variant;
.
Mas := XL_2.Range[ 'c6' , 'c12' ].Value[xlRangeValueDefault];
XL_1.Range[ 'd9' , 'd15' ].Value[xlRangeValueDefault] := Mas;

Или обойтись даже без его объявления:

XL_1.Range[ 'd9' , 'd15' ].Value[xlRangeValueDefault] :=
XL_2.Range[ 'c6' , 'c12' ].Value[xlRangeValueDefault];

Если вы заметили орфографическую ошибку на этой странице, просто выделите ошибку мышью и нажмите Ctrl+Enter.
Функция может не работать в некоторых версиях броузеров.

Здравствуйте!
Есть кусок кода vba, который прекрасно работал с 2003 по 2010 Excel
А вот для Excel2016 (windows 10) на тех же файлах выдает ошибку:
Метод pasteSpecial из класса worksheet завершен неверно

Что тут может быть неверным?

Приложить пример затруднительно - все файлы большие и разные, все прекрасно работали на предыдущих версиях Excel
Тут скорей вопрос про Excel 2016

Что тут может быть неверным?

Приложить пример затруднительно - все файлы большие и разные, все прекрасно работали на предыдущих версиях Excel
Тут скорей вопрос про Excel 2016 Shurakm

Что тут может быть неверным?

Приложить пример затруднительно - все файлы большие и разные, все прекрасно работали на предыдущих версиях Excel
Тут скорей вопрос про Excel 2016 Автор - Shurakm
Дата добавления - 04.10.2017 в 15:46

Чепуха какая-то. Вы с листа на лист копируете ВСЕ ячейки каким-то инвалидным способом:
[vba] Чепуха какая-то. Вы с листа на лист копируете ВСЕ ячейки каким-то инвалидным способом:
[vba] [/vba]Зачем? Откуда и что Вы таким кодом копируете? Автор - KuklP
Дата добавления - 04.10.2017 в 18:14

Сорри за сумбур.
Я в vba даже не любитель. Код не мой и очень древний.

Берется шаблон из БД (Excel 2007), копируются листы из него в новую книгу (Excel 2016).
[vba]

А потом вот эта странная функция (CopySheetFormules) для каждого листа должна по идее отдельно скопировать формулы (присутствует коммент типа - "а то при копировании есть ограничение 255 символов")
Может старое ограничение, может еще что
Вот вроде параметр Format:=7 - это xlAllExceptBorders, а совсем не формулы как указано

Сорри за сумбур.
Я в vba даже не любитель. Код не мой и очень древний.

Берется шаблон из БД (Excel 2007), копируются листы из него в новую книгу (Excel 2016).
[vba]

А вот нифига!
xlAllExceptBorders это не параметр Format, это параметр paste:
Range.PasteSpecial Method
Pastes a Range from the Clipboard into the specified range.
Syntax
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
expression A variable that represents a Range object.
Parameters
Name Required/Optional Data Type Description
Paste Optional XlPasteType . The part of the range to be pasted.
А вот нифига!
xlAllExceptBorders это не параметр Format, это параметр paste:
Range.PasteSpecial Method
Pastes a Range from the Clipboard into the specified range.
Syntax
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
expression A variable that represents a Range object.
Parameters
Name Required/Optional Data Type Description
Paste Optional XlPasteType . The part of the range to be pasted.
Range.PasteSpecial Method
Pastes a Range from the Clipboard into the specified range.
Syntax
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
expression A variable that represents a Range object.
Parameters
Name Required/Optional Data Type Description
Paste Optional XlPasteType . The part of the range to be pasted.
[/vba] Автор - KuklP
Дата добавления - 04.10.2017 в 18:48 Выкинул в паре шаблонов указанный блок с дополнительным копирование как бы длинных формул - все заработало
Но.. этих шаблонов много и возможно в каком-то из них есть эти пресловутые длинные формулы
Т.е. теоретически задачу можно свести к переделыванию всех шаблонов (выкинуть этот блок) и тщательной проверке результата
Однако это немаленькая задача Выкинул в паре шаблонов указанный блок с дополнительным копирование как бы длинных формул - все заработало
Но.. этих шаблонов много и возможно в каком-то из них есть эти пресловутые длинные формулы
Т.е. теоретически задачу можно свести к переделыванию всех шаблонов (выкинуть этот блок) и тщательной проверке результата
Однако это немаленькая задача Shurakm

Поэтому вместо Вашей помойки:

Попробовал. Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно"

Поэтому вместо Вашей помойки:

Попробовал. Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно" Shurakm

Поэтому вместо Вашей помойки:

Попробовал. Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно" Автор - Shurakm
Дата добавления - 04.10.2017 в 18:56

на тех же файлах выдает ошибку:
Метод pasteSpecial из класса worksheet завершен неверно Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно" Как минимум ничего не потеряли. Тогда надо видеть файлы. только не
а пара НЕБОЛЬШИХ для примера, где длина формулы >255.
И еще - объединенные ячейки тоже могут вызывать такую ошибку. на тех же файлах выдает ошибку:
Метод pasteSpecial из класса worksheet завершен неверно Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно" Как минимум ничего не потеряли. Тогда надо видеть файлы. только не
а пара НЕБОЛЬШИХ для примера, где длина формулы >255.
И еще - объединенные ячейки тоже могут вызывать такую ошибку. KuklP на тех же файлах выдает ошибку:
Метод pasteSpecial из класса worksheet завершен неверно Теперь ошибка "Метод pasteSpecial из класса Range завершен неверно" Как минимум ничего не потеряли. Тогда надо видеть файлы. только не
а пара НЕБОЛЬШИХ для примера, где длина формулы >255.
И еще - объединенные ячейки тоже могут вызывать такую ошибку. Автор - KuklP
Дата добавления - 04.10.2017 в 19:03 Но вообще-то чепуха. Как правило не делаю примеров за ТС, но.. В F1 длина формулы 1542 знака, что по мне так запредельный изврат. Но все копируется и как формулы и как просто копирование:
[vba] Но вообще-то чепуха. Как правило не делаю примеров за ТС, но.. В F1 длина формулы 1542 знака, что по мне так запредельный изврат. Но все копируется и как формулы и как просто копирование:
[vba] [/vba] Автор - KuklP
Дата добавления - 04.10.2017 в 19:35

Спасибо за помощь
На самом деле что-то непонятное и главное не могу выявить смысл этого куска кода (отчеты монструозные)

За неимением времени попробую отрубить этот кусок кода и подождать где вылезет

За неимением времени попробую отрубить этот кусок кода и подождать где вылезет Shurakm

За неимением времени попробую отрубить этот кусок кода и подождать где вылезет Автор - Shurakm
Дата добавления - 05.10.2017 в 11:18

Специальная вставка работает только с данными ячеек, скопированными в буфер обмена методом 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, который был скопирован в указанный диапазон.

Синтаксис

выражения. PasteSpecial (вастер, операция, SkipBlanks, Transpose)

выражение: переменная, представляющая объект Range.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Paste	Необязательный	XlPasteType	Часть диапазона, который необходимо вклеить, например xlPasteAll или xlPasteValues.
Операция	Необязательный	XlPasteSpecialOperation	Операция вклейки, например xlPasteSpecialOperationAdd.
SkipBlanks	Необязательный	Variant	Правда, чтобы пустые ячейки в диапазоне на буфере обмена не вклеились в диапазон назначения. Значение по умолчанию — False.
Transpose	Необязательный	Variant	True для перезаповки строк и столбцов при вклеивании диапазона. Значение по умолчанию — False.

Возвращаемое значение

Пример

В этом примере данные в ячейках D1:D5 на листе1 заменяют сумму существующего содержимого и ячеек C1:C5 на sheet1.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

У меня есть макрос, чтобы открыть документ Word в серверной части и перетащить все таблицы данных в шаблон Excel.

Я проверил ссылки и добавил, что в машинах моих коллег мои машины.

3 ответа

ИЗМЕНИТЬ

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

Я думаю, что вы используете ActiveSheet.PasteSpecial , вероятно, хорошо, основываясь на тестировании, которое вы провели, и это Документация MSDN. Однако ваша проблема может заключаться в том, что вы получаете данные, несовместимые с форматом text , как описано ниже.

Что касается метода, метод Worksheet.PasteSpecial полностью отличается от Range.PasteSpecial . Я нахожу странным, что полученная ошибка говорит о том, что метод диапазона потерпел неудачу, если вы вызываете метод листа. Если это точно, я подозреваю, что метод Worksheet в какой-то момент вызывает метод range.

Я могу воспроизвести конкретную ошибку

Ошибка метода PasteSpecial класса Range

Для Range.PasteSpecial при следующих условиях:

буфер обмена пуст
данные не были скопированы из приложения Office.

Оказывается, есть офисный буфер обмена и буфер обмена Windows. Например, Application.CutCopyMode контролирует только офисный буфер обмена. Range.PasteSpecial работает в буфере обмена Office, а Workbook.PasteSpecial работает в буфере обмена системы или Windows. Таким образом, он не выдает ошибку, если офисный буфер обмена пуст, на самом деле, он выдает ошибку для противоположного условия, когда диапазон Excel копируется, если он вынужден использовать Text в качестве своего формата.

Ошибка метода PasteSpecial класса Worksheet

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

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

Как я уже сказал в своих комментариях, PasteSpecial может быть привередливым. Поэтому я рекомендовал удалить это как фактор и проверить, можете ли вы напрямую обращаться к содержимому буфера обмена, как показано ниже (скопировано отсюда) .

Смотрите этот документированный случай той же ошибки это было вызвано тем, что буфер обмена пуст, и насколько легко это может произойти с макрокомандами Office. Вы копируете в свой макрос, поэтому я не ожидаю, что это будет вашей проблемой. Кроме того, этот фрагмент кода будет защищать от нулевого значения для метода Range , но не для метода Worksheet , поскольку он проверяет только буфер обмена приложения, а не систему.

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