Написать функцию, которая копирует все содержимое из одного листа Excel в другой

1. RailMen 823 06.06.16 20:43 Сейчас в теме
Написать функцию, которая копирует все содержимое из одного листа Excel (.xltm) в другой.
Excel 2010.
Дано: файл .xltm внутри >100 листов с формулами и макросами.
Требуется:
1. написать обработку (можно с толстой формой) или по крайней мере просто функцию, которая бы копировала ВСЕ содержимое ячеек с УКАЗАННОГО листа на НОВЫЙ лист (лист желательно создать новый программно). Копироваться должно все в т.ч. и формулы.
2. создать функцию, которая бы осуществляла поиск и замену значений внутри ВСЕХ формул выбранного листа по типу как работает СтрЗаменить(...).
Первый, кто напишет, получит или $m или рубли, кому как удобно (пишите договоримся).
По теме из базы знаний
Вознаграждение за ответ
Показать полностью
Ответы
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
5. tolyan_ekb 104 06.06.16 21:39 Сейчас в теме
(1) RailMen, пример файла будет?
20. CaptainMorgan 10.06.16 07:13 Сейчас в теме
(1) Функции получились рабочие.
Только вот остаётся один вопрос: Какой практический смысл вы вкладываете в них?

Ещё надо учитывать, что Если на компьютере не установлен Офис, то функция работать не будет.

А вообще.
Если надо сделать копию книги Excel, то с этой задачей можно легко справиться с помощью возможностей операционной системы.
24. sardar2c 15.06.16 13:52 Сейчас в теме
(20) CaptainMorgan,
А вообще.
Если надо сделать копию книги Excel, то с этой задачей можно легко справиться с помощью возможностей операционной системы.


Пока читал думал имеено об этом, работать с Excel из 1С то еще колдунство, если можно обойтись лучше так и сделать.
25. CaptainMorgan 16.06.16 05:44 Сейчас в теме
(24)sardar4ik,
если можно обойтись лучше так и сделать

В общем-то если программист поставил себе задачу, то это не с проста.
Возможно требуется не точная копия и при копировании необходимо внести какие-то изменения.
Мне всегда интересно практическое применение.
Можно сделать отчет, который формирует результат в виде файла Excel.
Так вот, у отчета может быть макет в виде внешнего файла, на основании которого формируется новый.
Но у такого отчета будет "тонкое место"
Если на компьютере не установлен Офис, то функция работать не будет
23. AnryMc 849 15.06.16 09:31 Сейчас в теме
Написать функцию, которая копирует все содержимое из одного листа Excel (.xltm) в другой.


Проще не копировать содержимое а скопировать сам лист...

Sheets("Лист1").Select
Sheets("Лист1").Copy Before:=Sheets(1)
26. tarassov 111 16.06.16 11:33 Сейчас в теме
(1) RailMen, предлагаю идею - читать и копировать Excel средствами работы с XML
Т.к. файл xlsx представляет собой архив со вложенными в него файлами xml
то можно выдернуть нужный файл листа, скопировать его, изменить текстовые вхождения и т.д. и т.п
к примеру, файлы листов можно найти внутри xlsx по пути \xl\worksheets\sheet1.xml и далее по номерам
на сайте был даже некий подобный пример http://infostart.ru/public/205359/
27. sergpogo 2 17.06.16 10:25 Сейчас в теме
Если на компьютере не установлен Офис, то работать не будет

	
Эксель = Новый COMОбъект("Excel.Application");   
	
Книга = Эксель.WorkBooks.Open(ПутьКФайлуНаКлиенте);
Лист  = Книга.WorkSheets(1); //лист который будем копировать
	
Лист.Copy(, Лист); //копирование листа, вставка после текущего
Лист2 = Книга.WorkSheets(2); //полученная копия листа
	
Книга.Save();
Эксель.Application.Quit();
Эксель = Неопределено;
Показать
Stansave; pvb2003; Xershi; v3rter; +4 Ответить
38. ZhokhovM 724 23.06.16 14:15 Сейчас в теме
(1) RailMen,
ФайлЧтение = Новый ТабличныйДокумент;
ФайлЧтение.Прочитать(СокрЛП(ПолноеИмяФайла_ЧтениеXLTM), СпособЧтенияЗначенийТабличногоДокумента.Значение);
ФайлЧтение.Записать(ПолноеИмяФайла_ЗаписьXLS, ТипФайлаТабличногоДокумента.XLS);
2. RailMen 823 06.06.16 20:45 Сейчас в теме
Если будут вопросы по задаче - пишите.
3. starik-2005 3033 06.06.16 20:49 Сейчас в теме
(2) RailMen, а что мешает Вам в экселе начать запись макроса, потом выделить всю книгу, нажать "Копировать", перейти в следующий лист и нажать "Вставить", а потом посмотреть, какие команды эксель запишет?
4. RailMen 823 06.06.16 21:22 Сейчас в теме
Вот собственно, что записал макрос, когда я выделил все в листе "20" и скопировал содержимое в листы "21", "22", "23":
Sub КопированиеЛиста()
'
' КопированиеЛиста Макрос

    Cells.Select
    Selection.Copy

    Sheets("21").Select
    Cells.Select
    ActiveSheet.Paste

    Sheets("22").Select
    Cells.Select
    ActiveSheet.Paste

    Sheets("23").Select
    Cells.Select
    ActiveSheet.Paste

End Sub
Показать


Сейчас сам тогда напишу в 1С, потестирую и отпишусь.
6. RailMen 823 06.06.16 21:50 Сейчас в теме
Файла excel? Боюсь всех напугать, мне саму страшно на него смотреть.
7. RailMen 823 06.06.16 22:11 Сейчас в теме
Получился код для авто копирования содержимого ИсходногоЛиста в другие листы, чей список имен задан строкой через запятую:
	Для Каждого ТекСтр Из ПравилаЗаполненияЛистов Цикл 
				
		ЛистОбразцовый = КнигаExcel.Sheets(ТекСтр.ИмяИсходного);
		ЛистОбразцовый.Select();
		ЛистОбразцовый.Cells.Select();
    	        ЛистОбразцовый.Selection.Copy(); //ОШИБКА: Метод объекта не обнаружен (Selection)
		
		МассивИменЗаполняемыхЛистов = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЗаполняемых);	
		Для Каждого ИмяЛиста Из МассивИменЗаполняемыхЛистов Цикл
			ЛистЗаполняемый = КнигаExcel.Sheets(ИмяЛиста);
			ЛистЗаполняемый.Select();
			
			//1. все очищаем
			ЛистЗаполняемый.Cells.Clear();
			
			//2. копируем из памяти
			ЛистЗаполняемый.Select();
		    ЛистЗаполняемый.Cells.Select();
		    ЛистЗаполняемый.ActiveSheet.Paste();
			
		КонецЦикла;
Показать


Пишет ошибку: Метод объекта не обнаружен (Selection)
Чего делать?
8. user_2010 871 06.06.16 22:38 Сейчас в теме
если вы копируете только один лист - разве нужно заменять формулы? формулы должны быть точно такими же, если есть ссылка на другие книги, листы, они ведь тоже должны остаться прежними? так?
9. RailMen 823 06.06.16 23:03 Сейчас в теме
(8) user_2010, там очень много ссылок и на другие листы и на все, что только может приснится в самом страшном сне 1Снега.

Я решил п.п. 1 смоими силами. Excel из под 1С шайтанство какое-то )))) Сейчас выложу обработину
10. RailMen 823 06.06.16 23:05 Сейчас в теме
Решение п.п.1:
Процедура ВыполнитьЗаполнениеЛистов() Экспорт

	Для Каждого ТекСтр Из ПравилаЗаполненияЛистов Цикл 

		ЛистОбразцовый = КнигаExcel.Sheets(ТекСтр.ИмяИсходного);
		ЛистОбразцовый.Select();
		ЛистОбразцовый.Cells.Select();
    	Excel.Application.Selection.Copy();
		
		МассивИменЗаполняемыхЛистов = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЗаполняемых);	
		Для Каждого ИмяЛиста Из МассивИменЗаполняемыхЛистов Цикл

			ЛистЗаполняемый = КнигаExcel.Sheets(ИмяЛиста);
			ЛистЗаполняемый.Select();
			
			//копируем из памяти (все форматирование заполняемого листа и его содержание удаляются автоматом)
		    ЛистЗаполняемый.Cells.Select();
		    ЛистЗаполняемый.Paste();
			
		КонецЦикла;
		
	КонецЦикла;
	
	Excel.ActiveWorkbook.Save();
	
КонецПроцедуры
Показать
11. RailMen 823 07.06.16 01:27 Сейчас в теме
Важное дополнение: написанный выше код не работает в файле *.xltm (с макросами) и приходится его сначала сохранять в обычный *.xlsx, только потом обрабатывать кодом. Почему то код не отрабатывает в файле *.xltm (с макросами) - просто висит как будто что-то делает, но ничего не копирует в итоге.

Короче под *.xltm (с макросами) не работает. Почему?
Может из-за политики безопасности? Хмхмх
Нет, даже включенным автозапуском макросов формат *.xltm (с макросами) не поддается коду 1С.
А вот обычный *.xlsx работает.
12. RailMen 823 07.06.16 06:13 Сейчас в теме
Решение 2 задачи:

ПравилаЗаменыВЛисте таблица с колонками:
ИмяЛиста
ЧтоМеняем - возвожны варианты "Value", "FormulaLocal"
СтрокаПоиска
СтрокаЗамены
ИменаЯчеекДляЗамены - Имена ячеек для замены через запятую "1,A; 2,B" (если не указано, то менять во всем листе)

СписокЛистовExcel - список имен листов книги Excel, задается после определения книги

	Процедура АвтоЗаменаФормул() Экспорт
	
	Для Каждого ТекСтр Из ПравилаЗаменыВЛисте Цикл 
		
		Если ПустаяСтрока(ТекСтр.СтрокаЗамены) или ПустаяСтрока(ТекСтр.СтрокаПоиска) Тогда Продолжить; КонецЕсли;
		Если СписокЛистовExcel.НайтиПоЗначению(ТекСтр.ИмяЛиста)=Неопределено Тогда Продолжить; КонецЕсли;	
		
		Лист = КнигаExcel.Sheets(ТекСтр.ИмяЛиста);
		Лист.Select();		
		
		//Просто меняем значение в ячейке
		Если НЕ ПустаяСтрока(ТекСтр.ИменаЯчеекДляЗамены) Тогда
			
			МассивИменЯчеек = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЯчеекДляЗамены, ";");	
			Для Каждого ИмяЯчейки Из МассивИменЯчеек Цикл
				
				МассивАдреса = РазложитьСтрокуВМассивПодстрок(ИмяЯчейки);
				Ячейка = Лист.Cells(МассивАдреса[0],МассивАдреса[1]);
				
				Если ТекСтр.ЧтоМеняем = ЗаменяемыеТипыЯчеек[0].Значение Тогда
					Ячейка.Value = СтрЗаменить(Ячейка.Value,ТекСтр.СтрокаПоиска,ТекСтр.СтрокаЗамены);
					
				ИначеЕсли ТекСтр.ЧтоМеняем = ЗаменяемыеТипыЯчеек[1].Значение Тогда
					Ячейка.FormulaLocal = СтрЗаменить(Ячейка.FormulaLocal,ТекСтр.СтрокаПоиска,ТекСтр.СтрокаЗамены);
					
				КонецЕсли;
				
			КонецЦикла;
			
		//Поиск и замена значений через Replace
		Иначе
			
			Лист.Cells.Replace(ТекСтр.СтрокаПоиска, ТекСтр.СтрокаЗамены);
			
		КонецЕсли;
		
		Состояние("Строка №"+ТекСтр.НомерСтроки);
		ОбработкаПрерыванияПользователя();
	КонецЦикла;
	
	//Сохранимся
	Excel.ActiveWorkbook.Save();	
	
КонецПроцедуры
Показать
14. user_2010 871 07.06.16 08:54 Сейчас в теме
(12) RailMen, Не пойму зачем менять формулы? при копировании листа формулы остаются теми же. Возможно изменение только ссылок на листы или книги - если вы копируете несколько листов или книг. Я не права?
28. cool.vlad4 2 17.06.16 12:05 Сейчас в теме
(12) RailMen, по моему код будет выполняться относительно долго. 1 хинт. если менять значения, то гораздо быстрее сформировать массив или ТЗ значений (загрузить через ADO, обработать) , выгрузить через Табличный документ в excel , затем прочитать через ADO в RecordSet и загрузить в исходную колонку или диапазон через CopyFromRecordset . второй хинт. по формулам. вряд ли формулы все разные, есть некая единая формула по колонке или диапазону. ну тогда надо задать ее Cells[граница1, граница2]).Formula = строковоепредставлениеформулы. (по формулам можно также отключить ScreenUpdating , потом включить ,но я думаю это всем известно)
29. RailMen 823 19.06.16 21:18 Сейчас в теме
(28) cool.vlad4, копировал лист 100 раз как написал выше: выходит не более минуты это не критично.
На каждом листе МНОГО таблиц, каждая имеет свою логику вычисления ячеек. Приходится упорно вычленять области, где можно отследить зависимости и их менять программно в созданных листах. Адская работа.

Теперь стоит другая задача: по заданной области (к примеру, столбец А1:А99) определить зависимость находящихся внутри формул и автоматически сгенерировать новые формулы на новом диапазоне А100:А199. Пример формул, которые надо продолжить вниз (пробелы вставил для облегчения понимания) :
=I2 * '1'!B289 * '1'!$G$1
=I3 * '1'!B290 * '1'!$G$1
=I4 * '1'!B291 * '1'!$G$1
=I5 * '2'!B289 * '2'!$G$1
=I6 * '2'!B290 * '2'!$G$1
=I7 * '2'!B291 * '2'!$G$1
=I8 * '3'!B289 * '3'!$G$1
=I9 * '3'!B290 * '3'!$G$1
=I10 * '3'!B291 *'3'!$G$1
...

Нужно автоматически продолжить формулы в колонке.
Понятно, что нужно сначала как то распарсить формулу на составляющие. И т.д.
30. sergpogo 2 20.06.16 09:22 Сейчас в теме
(29) RailMen, За основную то задачу кому вознаграждение будет?
32. RailMen 823 20.06.16 23:59 Сейчас в теме
(30) spogo, тому, кто первый ее решил. Все согласны?
34. sergpogo 2 21.06.16 15:55 Сейчас в теме
35. Мегумин 22.06.16 15:48 Сейчас в теме
40. v3rter 30.06.16 16:56 Сейчас в теме
(29) RailMen, Вы так всё вознаграждение "сольёте" инфостарту ;)
sergpogo; +1 Ответить
13. necropunk 9 07.06.16 08:48 Сейчас в теме
1С-ник эгоист :) Сам поставил задачу, назначил вознаграждение, решил, выдал себе вознаграждение. И это пока все спали :)))
pt_olga; dj_serega; awk; RailMen; BuhBuhov; PowerBoy; FilatovRA; +7 Ответить
19. RailMen 823 09.06.16 13:10 Сейчас в теме
15. v3rter 07.06.16 09:53 Сейчас в теме
ЛистОбразцовый.Copy() или ЛистОбразцовый.Cells.Copy() пробовали?

Если я не ошибаюсь, в экселе есть отладчик, пробовали сделать точку останова (breakpoint) и в его аналоге "табло" посмотреть доступные свойства ЛистОбразцовый?

Слишком давно такое делал, примеров не сохранилось. Общая идея была копировать бланк отчета со скрытого листа.
16. DimasBarabas 08.06.16 11:19 Сейчас в теме
Если содержимое одно и тоже то можно так:КопироватьФайл (FileCopy)
Синтаксис:

КопироватьФайл(<ИмяФайлаИсточника>, <ИмяФайлаПриемника>)
Параметры:

<ИмяФайлаИсточника> (обязательный)

Тип: Строка.
Полное имя файла-источника.
<ИмяФайлаПриемника> (обязательный)

Тип: Строка.
Полное имя файла-приемника.
Описание:

Копирует файл-источник в файл-приемник. Допускается перезапись файла-приемника.

Доступность:

Тонкий клиент, веб-клиент, сервер, толстый клиент, внешнее соединение, мобильное приложение(клиент), мобильное приложение(сервер).
Примечание:

Файл-приемник при создании будет иметь права доступа, аналогичные назначаемым при создании новых файлов (т.е. права доступа к файлу-источнику не копируются).
Допускается использование схем http, https и ftp для адресации файлов. При использовании этих схем в адресах необходимо указывать прямые слеши '/', а не обратные '\'.
Для работы метода на веб-клиенте необходимо предварительно подключить расширение работы с файлами.
На веб-клиенте метод работает только с локальными файлами.
Пример:

КопироватьФайл("C:\Temp\Order.htm", "C:\My Documents\Order.htm");
17. Isperator 08.06.16 17:53 Сейчас в теме
Норм тема.Я даже и не знал что так можно.
18. RailMen 823 09.06.16 13:06 Сейчас в теме
Всем спасибо. За психологическую помощь!
21. Xershi 1474 10.06.16 09:22 Сейчас в теме
Так бы неплохо выдать вознаграждение, чтобы глаз тема не мазолила))
31. v3rter 20.06.16 11:34 Сейчас в теме
https://msdn.microsoft.com/en-us/library/office/ff195345(v=office.15).aspx
https://msdn.microsoft.com/en-us/library/office/ff838605.aspx
SourceRange = Worksheets("Sheet1").Range("А1:А99");
fillRange = Worksheets("Sheet1").Range("А100:А199");
sourceRange.AutoFill(fillRange);

возможно
Лист.sourceRange.AutoFill(fillRange,0);

возможно
Лист.Range("А1:А99").AutoFill(fillRange,0);

возможно
Лист.Range("А1:А99").AutoFill(Лист.Range("А100:А199"),0);
33. v3rter 21.06.16 10:43 Сейчас в теме
Согласен.

До кучи, чтобы не пропадал полезный опыт:

Общий принцип - записывается макрос, изучается хелп к командам, код переписывается без использования Selection. Бланки и шаблоны удобно прятать на скрытые листы, через Range удобно ссылаться на именованные ячейки - Range("Курс"), параметры удобно выносить либо на отдельный лист, либо фиксировать область шапки и размещать в ней. Параметры печати "по ширине листа" в экселе - 1 лист в ширину, 999 в высоту.
36. CaptainMorgan 22.06.16 20:33 Сейчас в теме
(33) Очень верное замечание.
А ещё При составлении таблиц Excel нужно руководствоваться следующими принципами:

1. Нельзя пропускать пустые столбцы, то есть, если столбец «Наименование» имеет адрес «B», то столбец «Количество» должен иметь адрес «C». Если название столбца длиннее чем ширина столбца, то его можно растянуть или воспользоваться вкладкой выравнивание в диалоговом окне «Формат ячейки» и установить флажок «Переносить по словам».

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

3. Заголовок столбца нужно писать в одной ячейке.

При выполнении данного задания использовать значок «Объединить и поместить в центре» для центрирования названий таблиц. Значения «Итого» в таблицах должны рассчитываться с помощью значка суммы , а значения в столбце «Сумма» во второй таблицы должны рассчитываться с помощью формул.

Для расположения текста внутри ячейки в несколько строк можно воспользоваться вкладкой «Выравнивание» в диалоговом окне «Формат» ячейки и отметить там пункт «Переносить по словам» или нажать в ячейке комбинацию клавиш «Alt + Enter».
37. v3rter 23.06.16 09:50 Сейчас в теме
4. Не объединяйте ячейки в над, под и внутри табличных данных!
Формулы с участием объединенных ячеек не гарантируют правильности результата.
Ячейки, объединенные в шапке будут мешать сортировке таблицы.
5. В функциях ВПР и ГПР в качестве диапазона поиска удобнее использовать столбцы/строки целиком.
39. CaptainMorgan 23.06.16 17:33 Сейчас в теме
(37) Очень верно!
Но главное - надо помнить, что если зажать на клавиатуре клавишу «Ctrl» и,не отпуская её, нажать клавишу «S», а далее,выбрать папку «Папка:», например «D:\», указать «Имя файла:», нажать «Сохранить», то файл будет защищён от исчезания при отключении питания у компьютера.

И ещё.
Разница между «Сохранить» или «Сохранить как» следующая:
«Сохранить» - перезаписывает уже сохранённый 1 или 2 способом файл.
«Сохранить как» - предлагает сохранить данные в новый файл.
41. adapter 417 07.07.16 17:09 Сейчас в теме
зачем вы тгавите? Это чем то отличается от ctrl+A \ ctr+V?
или правый клик на ярлыке листа, скопировать\вставить, в новую книгу.
Можно выделить все ярлыки с шифтом и скопировать разом

https://drive.google.com/open?id=0BxWfydX7Umi8djlYUm1SUE4zUTA

Стандартная функция поиска замены в Excel тоже работает.
43. RailMen 823 20.07.16 13:32 Сейчас в теме
(41) adapter, суть не в простом копировании листа. Нужно скопировать лист с формулами и "на лету" все формулы заменить по смыслу и по аналогии.
44. Xershi 1474 20.07.16 14:07 Сейчас в теме
(43) RailMen, ну так копирование листа разве что-то меняет в логике?
42. Xershi 1474 19.07.16 08:06 Сейчас в теме
Походу кина не будет...
45. v3rter 21.07.16 17:45 Сейчас в теме
Лет 10 назад я писал эксельные макросы, которые делали запрос к корпоративной базе SQL, копировали скрытый лист с шаблоном печатной формы, на котором в области "табличной части" была всего одна строка с формулалми, затем макрос набивал строки и "размножал" формулы командой, аналогичной двойному щелчку на маркер заполнения, затем копировал ячейки с формулами и вставлял их сами в себя как значения - что экономило время на переписывании кода.

Посмотрите "Перевод кода макроса в код для 1С" http://infostart.ru/public/64421/ , и там же как запустить макрос, вшитый в документ, может это то, что Вам нужно.
46. CaptainMorgan 21.07.16 21:27 Сейчас в теме
(45)v3rter, тема находится в разделе "Программирование 1С 8.3"
А вы предлагаете обработку для 7.7:
Посмотрите "Перевод кода макроса в код для 1С" http://infostart.ru/public/64421/

У этого же автора есть то же самое но для 8.1:
http://infostart.ru/public/64841/

Хотя, RailMen уже потерял интерес к вопросу и всё решил самостоятельно в (7)
47. RailMen 823 15.08.16 18:03 Сейчас в теме
(46) CaptainMorgan, интерес не потерян. Он перерос в кое-что другое.
48. CaptainMorgan 15.08.16 21:05 Сейчас в теме
(47) RailMen
интерес не потерян. Он перерос в кое-что другое.

Это нормальный процесс.
Оценки состояния заинтересованности очень субъективны.
Вполне возможно, что результаты данной ветки воплотятся в некий программный продукт или пополнят личную библиотеку.
Всё может быть, но за пределами данной страницы.
И если смотреть с точки зрения оценки потребности данной ветки, то интерес автора к ней был утрачен.
По этой причине и был мой разъясняющий пост.

Но если у вас возникли новые интересные темы для обсуждения, буду рад поучаствовать.
Оставьте свое сообщение
Вакансии
Программист 1С
Москва
зарплата от 180 000 руб. до 220 000 руб.
Полный день

Аналитик 1С / Бизнес-аналитик
Нижний Новгород
зарплата от 100 000 руб. до 250 000 руб.
Временный (на проект)

Программист 1С
Москва
зарплата от 250 000 руб.
Полный день

Программист 1C
Волгоград
зарплата от 200 000 руб.
Полный день

Аналитик
Санкт-Петербург
зарплата от 200 000 руб. до 250 000 руб.
Полный день