Excel автоматическое заполнение шаблонов
В этом разделе сайта описан процесс настройки программы заполнения документов и рассылки почты под различные задачи.
Выбирая интересующие пункты меню в столбце слева, вы можете найти интересующую вас информацию.
Разделы справки:
Комментарии
Добрый день! Когда будет реализована функция вертикальной таблицы?
Когда будет реализована возможность выбора варианта вертикальной таблицы (очень много данных для заполнения, удобнее расположить их вертикально).
В настройка программы, на вкладке «Дополнительно», найдите и включите пункт «Отображать подменю Дополнительно на панели инструментов».
Из этого меню будет доступно открытие инструкции.
Здравствуйте! Как снова открыть инструкцию которая в начале появляется? Закрыл и не могу снова открыть((
Здравствуйте, Алекс.
Либо маска имени файла неверно задана (файл получает имя открытого файла в Excel), либо шаблон не закрыли перед запуском формирования документов
Здравствуйте, у меня шаблон в excel, хочу чтобы в excel и сохранялся документ, все проходит, написано, что сохранено, но по факту в папке нет документа. Как быть?
Подскажите как запустить макрос на MS Exele Mac
Игорь, добрый день! Уточните, пожалуйста, по Unisender, через него теперь нельзя отправлять, используя ваш скрипт? Или можно, но есть какие-то ограничения?
Вывод на печать только по некоторым шаблонам, - возможен (это описано в справке про коды полей)
Вывод в один файл, - только для шаблонов формата эксель (для word такого нет)
Здравствуйте.
Возможна ли настройка сохранения не в отдельные файлы, в один общий?
Возможна ли настройка "автоматического вывода на печать" только на определенные шаблоны, а не на все сразу??
Добрый день, у меня Эксель не активирован, не открывается ничего. Это от того, что он не активирован?
Здравствуйте, Александр.
1. Если отправляете через Юнисендер, там время зависит от объема вложений.
Если не использовать Юнисендер, тормозов быть не должно.
2. по Unisender поддержки нет (функционал этот не поддерживается более, оставлен только в целях совместимости)
Отправляйте через Outlook, и проблем не будет
Здравствуйте. два вопроса
1) в последнее время скрипт стал очень медленно работать, одно письмо с формированием pdf из word и вкладыванием в письмо занимает 3-5 минут. это норм?
2)без вложения письма отправляются очень быстро, unisender принимает только 50 писем в минуту. подскажите каким образом сделать паузу? чтобы отправлять максимум 50 писем в минуту
Алексей, напишите мне в Скайп, решим проблему.
здравствуйте, с некоторых пор (может обновления системы или самого офиса - у меня лицензия) перестали подставляться данные в файлы *.xls (происходит зависаний excel и ничего не формируется), ранее проблем не возникало. *.doc обрабатываются без проблем
Надстройка FillDocuments обновлена до версии 3.3.6
Можно ли распечатать сразу несколько копий документов. Для договоров было бы очень неплохо
Шаблон - это обычный файл эксель или Ворд. Моя программа не имеет никакого отношения к процессу вставки каких-либо данных в ваш файл шаблона (можете вставлять что угодно)
Добрый день, есть ли ограничение по количеству или содержанию (формату) для вставки в шаблон. Столкнулся с проблемой, что буфер обмена не может вставить объект (таблицу. Пишет, что не поддерживаемый буфером обмена формат. А там для вставки табличка в три строки. Подскажите, пожалуйста.
Здравствуйте, Павел.
Возможно, перемудрили с настройками программы, - сбросьте их (для этого есть кнопка снизу на форме настроек), и попробуйте снова
Добрый день!
При создании документов выдает ошибку:
Создание документов для строки 2 таблицы Excel
========================================
Создание документа 1 из 2
Шаблон: . \Справка.xlsx
Сохранение созданного файла: . \Справка
Результат: ОШИБКА
========================================
Создание документа 2 из 2
Шаблон: . \Счёт.xls
Сохранение созданного файла: . \Счёт
Результат: ОШИБКА
========================================
Результаты работы программы:
Использовано шаблонов: 2
Обработано записей в таблице Excel: 1
Создано файлов: 0
Не удалось создать файлов: 2
Программу использую в тестовом режиме
Елена, не могу ответить, - ваш вопрос не понятен
Добрый день! Подскажите пожалуйста. Есть проблема: из файла - "Документы" хочу перенести текст в шаблон, так чтобы текст был с подчеркиванием каждой строки.Как это сделать?
Владимир, да, возможна.
Здравствуйте, возможна ли отправка каждой строки таблицы как отдельного письма по шаблону на один единственный адрес электронной почты? Очень нужно для работы с сервисом Контур.Закупки.
Нет, макросы в Office могут использовать только одно ядро процессора.
Есть ли возможность использовать все ядра процессора при генерации word файлов?
Мария, ответил вам на почту.
Уважаемый Игорь!
Помогите пожалуйста (ранее написала - но на сайте не вижу свой комментарий)
Купила лицензию на 2 компьютера
На 1м все отлично работает, на 2м не хочет
1) Не формирует письма. При выделении формирует только письмо первое в файле
2) Функции вставляются через окно формул. Так я не могу смешать формулы и сделать ФамилияИО(дательный)
3) В инициалах ошибки при использовании формулу Фамилия ИО (Только одна буква высвечиввается например)
Уважаемый Игорь! Добрый день!
Помогите пожалуйста!
Ссылка на докупку активаций есть в личном кабинете, и в уведомлении с сайта о покупке программы.
Ну или просто можно нажать кнопку Купить на странице программы, и выбрать опцию Приобрести дополнительные активации
Здравствуйте
По ссылке https://excelvba.ru/programmes/FillDocuments указано, что "Если нужны будут дополнительные активации, их можно будет в любой момент приобрести по 500 рублей за каждый дополнительный компьютер."
Нужно приобрести лицензию на дополнительный компьютер
Покупали на 2 компьютера лицензию
1 комп = в ремонте
2-й компьютер = личный ноутбук специалиста, который уже с нами не работает
Как докупить 3-й компьютер лицензию?
Обновите программу до последней версии, — только что залил обновление, решающее эту проблему.
Добрый день!
После работы в тестовом режиме, сбилась функция копирования адреса заголовка по двойному клику. Вставляется символ, вместо адреса ячейки. Прошу подсказать, как решить проблемку?
Функция мгновенного заполнения автоматически подставляет данные, когда обнаруживает закономерность. Например, с помощью мгновенного заполнения можно разделять имена и фамилии из одного столбца или объединять их из двух разных столбцов.
Примечание: Функция мгновенного заполнения доступна только в Excel 2013 и более поздних версий.
Предположим, что столбец A содержит имена, столбец B — фамилии, а вы хотите заполнить столбец C сочетаниями имен и фамилий. Если ввести полное имя в столбец C, функция мгновенного заполнения заполнит остальные ячейки соответствующим образом.
Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.
Начните вводить следующее полное имя в ячейке C3. Excel определит закономерность и отобразит предварительное изображение остальной части столбца, заполненной объединенным текстом.
Для подтверждения предварительного просмотра нажмите клавишу ВВОД.
Если вариант заполнения не выводится, вероятно, эта функция не включена. Вы можете выбрать Данные > Мгновенное заполнение, чтобы применить заполнение вручную или нажать клавиши CTRL+E. Чтобы включить мгновенное заполнение, выберите Сервис > Параметры > Дополнительно > Параметры правки и установите флажок Автоматически выполнять мгновенное заполнение.
Предположим, что столбец A содержит имена, столбец B — фамилии, а вы хотите заполнить столбец C сочетаниями имен и фамилий. Если ввести полное имя в столбец C, функция мгновенного заполнения заполнит остальные ячейки соответствующим образом.
Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.
Выберите Данные > Мгновенное заполнение или нажмите клавиши CTRL+E.
Excel определит закономерность в ячейке C2 и заполнит ячейки ниже.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Если вы еще не знаете про такой прием в Excel, как автозаполнение ячеек путем протягивания мышью крестика - то самое время это про него узнать. Эта возможность очень полезная. Что дает автозаполнение: допустим вы хотите заполнить строку или столбец днями недели(Понедельник, Вторник и т.д.). Человек, который про автозаполнение не знает последовательно вводит в каждую ячейку вручную все эти дни. Но в Excel для выполнения подобной операции вам потребуется заполнить лишь первую ячейку. Запишем в неё Понедельник. Теперь выделяем эту ячейку и ведем курсор мыши к нижнему правому углу ячейки. Курсор приобретет вид черного крестика:
Как только курсор стал крестиком, жмем левую кнопку мыши и удерживая её тянем вниз(если надо заполнить строки) или вправо(если надо заполнить столбцы) на необходимое количество ячеек. Теперь все захваченные нами ячейки заполнены днями недели. И не одним Понедельником, а по порядку следования:
для заполнения вниз подобным методом большого количества строк можно не тянуть за крестик, а быстро дважды нажать левую кнопку мыши на ячейке, как только курсор приобретет вид крестика
Напрашивается вопрос: так можно сделать только с днями недели или есть еще какие-то возможности? Ответ - есть, и немалые.
Если вместо левой кнопки мыши, зажать правую и протянуть, то по завершении Excel выдаст меню, в котором будет предложено выбрать метод заполнения: Копировать ячейки, Заполнить, Заполнить только форматы, Заполнить только значения, Заполнить по дням, Заполнить по рабочим дням, Заполнить по месяцам, Заполнить по годам, Линейное приближение, Экспоненциальное приближение, Прогрессия:
Выбираете необходимый пункт и вуаля!
Серым шрифтом выделены неактивные пункты меню - те, которые нельзя применить к данным в выделенных ячейках
Подобное автозаполнение доступно для числовых данных, для дат и некоторых распространенных данных - дней недели и месяцев.
Однако помимо использования встроенных в Excel списков автозаполнения, можно создать и свои списки. Например, Вы часто заполняете шапку таблицы словами: Дата, Артикул, Цена, Сумма. Можно их вписывать каждый раз или копировать откуда-то, но можно сделать и по-другому. Если Вы используете:
Выбираете пункт НОВЫЙ СПИСОК - ставите курсор в поле Элементы списка и заносите туда через запятую наименования столбцов, как показано на рисунке выше. Нажимаем Добавить.
Так же можно воспользоваться полем Импорт списка из ячеек. Активируем поле выбора, щелкнув в нем мышкой. Выбираем диапазон ячеек со значениями, из которых хотим создать список. Жмем Импорт. В поле Списки появиться новый список из значений указанных ячеек.
Теперь остается проверить в действии. Пишем в любую ячейку слово Дата и протягиваем, как описано выше. Excel заполнил нам остальные столбцы значениями из того списка, который мы сами только что создали. Вы можете изменять и удалять, созданные Вами списки, добавлять новые.
Так же эти списки можно использовать и для сортировки значений. Для этого выделяем нужные для сортировки ячейки -переходим на вкладку Данные -Сортировка. Раскрываем поле порядок -Настраиваемый список. Выбираем созданный список.
Созданные пользователем списки доступны из любой книги на том ПК, на котором эти списки были созданы.
Предположим, у нас в папке ШАБЛОНЫ находятся 3 файла:
По умолчанию, программа будет использовать каждый из шаблонов для каждой из строк исходной таблицы:
Что же делать, если нам требуется по каждой строке формировать различные документы?
(шаблонов может быть много, а документов надо намного меньше, для каждой из строк - по своим шаблонам)
Чтобы включить режим выбора шаблонов для каждой конкретной строки, в настройках программы на вкладке «Исходная таблица») включаем опцию «Использовать только те шаблоны, имена которых перечислены в столбце», выбирая номер столбца с названиями шаблонов:
Теперь, программа будет использовать не все шаблоны из папки, а только те, имя файла которых задано в соответствующем столбце.
Например, мы задали в настройках программы поиск имен шаблонов во 2 столбце, и ввели туда следующие данные:
ФИО | шаблон | примечание |
---|---|---|
Иванов Иван Иванович | акт.doc | только один шаблон акт.doc |
Достоевский Эргешали Бактиярович | test.xls | только один шаблон test.xls |
Купитман Иван Натанович | договор.doc/акт.doc | два шаблона: договор.doc и акт.doc |
Кисегач Анастасия Константиновна | * | все шаблоны |
Черноус Варвара Николаевна | *.doc | только шаблоны формата Word |
Быков Андрей Евгеньевич | ни одного шаблона (документы созданы не будут) | |
Романенко Глеб Викторович | *.xls/акт* | все шаблоны Excel и все шаблоны, имя файла которых начинается со слова акт |
Выделяем строки, запускаем формирование документов, — и на выходе получаем только нужные файлы:
2 (Иванов Иван Иванович) - акт.doc
3 (Достоевский Эргешали Бактиярович) - test.xls
4 (Купитман Иван Натанович) - акт.doc
4 (Купитман Иван Натанович) - договор.doc
5 (Кисегач Анастасия Константиновна) - test.xls
5 (Кисегач Анастасия Константиновна) - акт.doc
5 (Кисегач Анастасия Константиновна) - договор.doc
6 (Черноус Варвара Николаевна) - акт.doc
6 (Черноус Варвара Николаевна) - договор.doc
8 (Романенко Глеб Викторович) - test.xls
8 (Романенко Глеб Викторович) - акт.doc
Как вы видите, можно указать один или несколько шаблонов (разделив через слеш), а также использовать маску для имен шаблонов (символ * обозначает любое количество любых символов) Если шаблоны расположены в подпапках, - названия подпапок указывать не надо (только имена файлов)
Автоматизация заполнения и вывода файлов по шаблонам рутинных документов это одна из та областей в отрасли строительства по которой традиционно софт, кроме бухгалтерского, находится на уровне вылизанных поделок, на мой скромный взгляд. Поэтому, развивая тему, приглашаю обсудить те проблемы и возможности, с которыми пришлось столкнуться в процессе реализации на базе MS Excel.
Со времени предыдущей статьи прошло уже пол года. За это время при помощи этой заготовки была разработана текстовая часть Исполнительной документации и сдана Заказчику. По итогам работы и отзывам редких участников в файл были внесены следующие правки, о которых я бы хотел поговорить и это 3 большие темы:
- Эстетика и юзабилити
- Оптимизация кода + нововведения
- Структура и связи
1. Эстетика и юзабилити
— Таблицы это в первую очередь таблицы, безликие ячейки с подписанными колонками и строками. Однако очень часто мы сталкиваемся с ситуацией, когда необходимы дополнительные пояснения к значению, которое будет находится в ячейке, или требуется дополнительно активизировать внимание пользователя на важности вводимого значения. Особенно важно, если у Вас, как в моем случае, строки в колонке очень длинной таблицы содержат разноплановую информацию, например: даты, виды работ, материалы, подписанты и многие др. В таких случаях у нас есть 2 инструмента для решения задачи:
Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 15" мониторы на ноутбуках с разрешением 1366×768 это разумный компромисс, что бы рабочая область была как можно больше.
Если внимательно проанализировать данные, то окажется что в таблице будут ячейки 3х типов:
- ячейки в которые непосредственно необходимо вводить новую текстовую информацию;
- ячейки, значение которых может принимать значение из ограниченного диапазона, введенного заранее, например: ФИО и должность подписантов;
- ячейки в которых прописаны формулы, например есть часть данных которая будет повторяться из акта в акт и такую информацию достаточно ввести один раз, например: наименование объекта, участок, организация и т.п.; либо формулы призванные реализовать технические возможности, например: переноса строки, подтягивание объемов работ, регалий по ФИО и т.п.
Здесь первая процедура постоянно будет защищать лист при помощи пароля 111, вторая будет блокировать функционал вырезать-вставить. Надо ли говорить, что это все работает только при включенных макросах, но с другой стороны без них и файл на 100% функционировать не будет.
Для случаев же п.2 разумно завести лист где столбцы будут содержать меняющиеся значения, прописать в них ссылки на диапазоны, присвоить им имена, т.е. на вкладке «Формулы» -> «Диспетчер имен» каждому диапазону присвоить имена и через вкладку «Данные» -> пункт меню «Проверка данных» -> вкладка «Параметры» -> условие проверки — «Список» реализовать выпадающее меню.
И, конечно, не забывайте ставить условия форматирования цветом, например для случаев, когда заполнены все необходимые строки в столбце через «Условное форматирование», например формула условного форматирования закрашивает ячейку, если следующие ячейки под ней содержат текст: =И(ДЛСТР(E5)>0; ДЛСТР(E6)>0)
2. Оптимизация кода + нововведения
Начать придется издалека, а именно вернуться к вопросу о реализации механизма заполнения шаблона. Если Вы решите заполнить шаблон в формате Excel и в формате Word, то это будут совершенно 2 разных механизма. В основе своей в файл Excel пишутся значения в конкретные ячейки файла или диапазоны ячеек и имеют привязку вида (у, х) (не спрашивайте почему у них строка идет впереди столбца при адресации — не знаю), например: Worksheet.Cells(y, x) = k. Отсюда же и первая мысль, что заполнять Excel-шаблон можно либо явным образом, т.е. непосредственно весь макрос будет содержать что откуда берется и куда закладывается, но что если придется вносить изменения в таблицы данных или выйдет новая форма шаблона? Отсюда вторая идея реализации, код которой описан в первой статье — это парсинг некоторых символов, которыми сперва заполняется массив, а так же в свою очередь содержит файл шаблона в нужных местах. Затем в каждой строке шаблона ищется совпадение с элементами массива поочередно, если совпадение есть, то порядковый номер массива привязан к строке таблицы откуда берутся данные, а столбец берется с листа в котором мы указываем какие именно акты мы хотим вывести. Итого несколько вложенных циклов, что накладывает ограничения на форматирование шаблона Excel, чем проще — тем лучше, потому что чем больше ячеек парсить — тем дольше будет происходить заполнение шаблона данными.
По многочисленным просьбам мною была интегрирована возможность вывода в шаблон формата Word, и здесь на самом деле есть 2 способа вывода текста:
когда мы так же считываем массив управляющих кодов, вручную прописываем их в шаблоне через «Вставка» -> «Закладки» и дальше просто прогоняем макросом присваивая закладке данные из соответствующей ей ячейке в файле Excel.
Здесь вынесена в отдельную процедуру обращение к закладке и arrСсылкиДанных(i) — это массив который содержит управляющие символы. Издержки метода, если Вам потребуется сослаться на значение Закладки в другом месте, например дату нужно использовать в заголовке и напротив фамилии каждого подписанта, то необходимо использовать в шаблоне Меню «Вставка» -> пункт меню «Перекрестная ссылка» -> Тип ссылки: «Закладка», Вставить ссылку на: «Текст закладки» и снять галочку «Вставить как гиперссылку». Что бы это отобрадзилось корректно не забудте обновить в конце макроса перед выводом поля Wd.Fields.Update
Здесь нужно обратить внимание, что у каждой таблицы в Word есть свой внутренний номер, методом нехитрого перебора Вы найдете нужный, а дальше принцип тот же, что и в Excel.
Между выводами в файлы форматов Word и Excel есть огромная пропасть, которая заключается в следующем:
Шаблон Excel требует перед использованием настроить отображение под конкретный принтер, т.к. фактическая область печати разнится от модели к модели. Так же перенос строки текста возможен, но только в пределах ячейки/объединенных ячеек. В последнем случае не будте автораздвигания строки, в случае переноса текста. Т.е. Вам вручную придется заранее определит границы области, которые будут содержать текст, который в свою очередь в них еще должен убраться. Зато Вы точно задали границы печати и выводимого текста и уверены, что не съедет информация (но не содержание) с одного листа на другой.
Шаблон Word при настройке автоматически переносит текст на последующую строку, если он не убрался по ширине ячейки/строки, однако этим самым он вызывает непрогнозируемый сдвиг текста по вертикали. Учитывая тот факт, что по требованиям к Исполнительной документации в строительстве ЗАПРЕЩЕНО один акт печатать на 2х и более листах, то это в свою очередь так же рождает проблемы.
Вторым большим нововведением стал отказ от реализации переноса текстовых строк с макроса VBA и заменой на функцию Excel, благодаря чему ускорилась работа с файлом.
Для первой строки:
<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105); СТРОКА($1:$10));)));"-")>
Здесь используется принцип массивов, т.е. вводится такой текст по Ctrl + Shift + Enter, а не обычному Enter. Сами формулы располагаются в ячейках F1 и F2. 'Данные для проекта'!$C$3 — ссылка на наименования объекта, длина текста которого более 105 символов. Перенос организуется в случае превышения длины текста в 105 символов.
Еще одним нововведением стал общий реестр, а так же контроль списания материалов по актам АОСР, но здесь ничего нового, просто парсинг соответствующих строк в свяске ИНДЕКС + ПОИСКПОЗ, которые расписаны во многих мануалах.
3. Структура и связи
Но мой пост так бы и остался рядовым постом с очередной игрой в изобретание велосипеда инструментами, которые рассчитаны на совершенно другое, если бы ни одно НО(!) Месячно-суточный график.
Идея о том, что можно именно на него много чего повесить, например заполнение Общего журнала работ в части Раздела 3 — наименование работ по датам, очередность и необходимость Актов освидетельствования скрытых работ и не только — завладела моими мыслями. Обычно в Excel закрашивают даты, в зависимости от диапазонов дат — начало и конец, но не на стройке. На стройке в календарном графике пишут объемы, а в зависимости от того с какой даты напротив наименования работ стоят объемы и по которую — получаются диапазоны дат отчетных периодов. На скриншоте серым помечены объемы попадающие в систематизированные отчетные периоды (1мес). Таким образом получается, что если:
Читайте также: