У попередніх статтях циклу "Автоматизація заповнення документів" я розповів про те, як сформувати інтерфейс програми, організувати перевірку введених даних і отримати число прописом без використання коду VBA. У цій, заключній статті мова піде про чари - перенесення всіх необхідних значень із робочої книги Excel до документа Word. Давайте я покажу Вам те, що має вийти в результаті:

Опис механізму

Для початку загалом опишу, яким саме чином відбуватиметься перенесення даних у документ Word. Насамперед нам знадобиться шаблон документа Word, Що містить всю розмітку, таблиці та ту частину тексту, яка залишатиметься незмінною. У цьому шаблоні необхідно визначити місця, в які будуть підставлені значення з робочої книги Excel, найзручніше це зробити за допомогою закладок. Після цього необхідно впорядкувати дані Excel таким чином, щоб забезпечити відповідність шаблону Word, та й в останню чергу - написати саму процедуру перенесення на VBA.

Отже, все по порядку.

Створення шаблону документа Word

Тут все гранично просто – створюємо звичайний документ, набираємо та форматуємо текст, загалом, домагаємось того, щоб отримати необхідну форму. У тих місцях, куди необхідно буде підставити значення з Excel, потрібно створити закладки. Це робиться так:

Таким чином, потрібно буде створити всі закладки, тобто відзначити всі місця, куди будуть вставлені дані з Excel. Файл, що вийшов, потрібно зберегти як "Шаблон MS Word" за допомогою пункту меню "Файл" -> "Зберегти як...".

Підготовка даних Excel

Я вирішив для зручності помістити всі дані, які необхідно перенести до документа Word, на окремому робочому аркуші під назвою Bookmarks - закладки. На цьому аркуші два стовпці: у першому містяться назви закладок (точно так, як вони названі в документі Word), а у другому - відповідні значення, що підлягають перенесенню.

Частина цих значень отримана безпосередньо з аркуша введення даних, а частина - допоміжних таблиць, розташованих на аркуші Support . У цій статті я не розбиратиму формули, які розраховують потрібні значення, якщо щось буде незрозуміло - ставте запитання в коментарях.

На цьому етапі важливо правильно вказати всі назви закладок – від цього залежить правильність перенесення даних.

Процедура перенесення

А ось це – найцікавіше. Існує два варіанти виконання коду перенесення даних:

• Код виконується в робочій книзі Excel, дані передаються в Word за одним значенням за раз і відразу розміщуються в документі.
• Код виконується в окремому документі Word, усі дані передаються із Excel одним пакетом.

З точки зору швидкості виконання, особливо при велику кількістьЗакладок, другий варіант виглядає набагато привабливіше, проте вимагає більш складних дій. Саме його я й використав.

Ось, що потрібно зробити:

• Створити шаблон документа Word за допомогою макросів.Цей шаблон містить код, що виконується на VBA.
  
• У створений шаблон необхідно розмістити програму, написану на VBA.Для цього необхідно при редагуванні шаблону натиснути комбінацію клавіш Alt+F11 і ввести у вікні редактора Visual Basic код програми.
• У робочій книзі Excel написати код, що викликає процедуру заповнення із щойно створеного шаблону Word.

Текст процедури я наводити в статті не буду - його можна легко переглянути у файлі FillDocument.dotm, розташованому в папці Template в архіві з прикладом.

Як скористатися всім цим для вирішення саме Вашого завдання?

Розумію, що на словах це все виглядає дуже просто, але що виходить насправді? Я пропоную Вам просто скористатися готовим варіантом. Завантажте архів з прикладом, у робочій книзі Excel натисніть комбінацію клавіш Alt+F11, щоб відкрити редактор Visual Basic і прочитайте всі коментарі до програми. Для того, щоб змінити програму під свої потреби Вам знадобиться лише змінити значення декількох констант, вони винесені на початок програми. Весь текст програми Ви можете вільно копіювати у свій проект.

Структура архіву

У архіві, доданому до цієї статті, міститься кілька файлів.

Основний файл – робоча книга Excel з назвою "Створення підтверджень". У цій робочій книзі 4 робочих аркуша, з яких відображаються лише два: "Input" - аркуш введення даних та "Database" - архів усіх введених документів.

У папці Templates є шаблони документів Word. Один із них - шаблон, що містить програму заповнення закладок, а другий - форма для заповнення. Ви можете використовувати без змін шаблон із програмою, але форму для заповнення, природно, доведеться переробити відповідно до Ваших потреб.

Як переробити приклад "під себе"?

1. Підготувати шаблон документа Word, який потрібно заповнити. Створити у ньому всі необхідні закладки та зберегти як "шаблон MS Word".
2. Скопіювати в папку з підготовленим шаблоном файл FillDocument.dotm з архіву, доданого до цієї статті. Цей файл відповідає за заповнення закладок шаблону, і в ньому нічого не потрібно змінювати.
3. Підготувати робочу книгу Excel для введення даних. Вам вирішувати, чи вона матиме якийсь "просунутий" інтерфейс користувача і здійснювати різні хитрі розрахунки. Головне, щоб у ній містився робочий лист із таблицею відповідності імені закладки у шаблоні Word та значення, яке потрібно підставити.
4. Вставити в підготовлену робочу книгу код програми на VBA із файлу-прикладу. Замінити всі константи відповідно до Вашого проекту.
5. Протестувати правильність роботи.
6. Активно скористатися!

Якось після підбиття підсумків олімпіади, коли учасникам вимагалося розіслати листами з результати з зданих (або не зданих) предметів, я помітив, що дівчина сидить і руками вносить у шаблон листа результати іспиту, ПІБ школяра та іншу інформацію. Перед нею лежало кілька роздрукованих із Excel аркушів із прізвищами та оцінками. Copy-Paste, Ctrl+C - Ctrl+V, переклад ПІБ з називного в родовий відмінок і так вже була виконана робота з половиною першого з трьох аркушів з даними. Скільки на вашу вона могла б ще просидіти копіюючи дані, а потім перевіряючи результат? Думаю досить довго, та й помилок було б чимало. Адже їй потім треба було підписувати поштові конверти... Мені стало шкода її часу і за кілька хвилин я показав, як можна за допомогою простих засобів цей процес автоматизувати. Після невеликого екскурсу її роботу було закінчено за 20 хвилин.

У цьому уроці я покажу Вам якнайшвидше створювати документи (листи, запрошення) витрачаючи на це мінімум часу. У різних версіях мов цей описуваний процес називається по-різному. Так у російській це «Злиття», а в англійській - «Merge».

Сподіваюся, що «листи щастя» від пенсійного фонду, податкові повідомлення створюються у такий самий спосіб:)

Інструменти

Для створення документів нам знадобиться OpenOffice Writer. У ньому ми створюватимемо шаблон листа. Знадобиться також OpenOffice Calc. У ньому ми створимо базу з прізвищами та адресами тих осіб, яких ми, наприклад, хочемо запросити на вечірку. Замість програм із пакета OpenOffice легко можуть використовуватися MS Word та Excel. База даних може легко лежати у MS Access. І так приступаємо.

Створення бази даних

База даних - це ніщо інше як таблиця в MS Excel або OpenOffice Calc, де дані розміщуються в стовпцях, а перший рядок використовується для іменування стовпців. Заведемо, наприклад, стовпці «ПІБ», «місто», «індекс», «адреса». Введемо дані та збережемо файл на диск.

Як бази даних можуть використовуватися й інші джерела, наприклад Адресна книга Outlook або база даних MS Access.

Створення шаблону листа

Зі створенням шаблону листа справа трохи складніша. Оскільки шаблон будуть дані з таблиці (нашої бази даних) підставлятися як є, то й лист треба писати відповідним чином. Якщо у вас у базі даних ПІБ йде в називному відмінку, то навряд чи Вам вдасться використати це поле у ​​зверненні «Дорогий,<ФИО>!» і для конверта у рядку «Кому:<ФИО>». В останньому випадку ПІБ виглядатиме дещо коряво.

Складаючи шаблон листа, я рекомендую почати зі складання листа якомусь реальній людині. Можна самому собі. Потім у ньому виділити кольором ті дані, які Ви братимете з бази даних та замінити їх на відповідні поля. Перед тим, як замінювати виділений текст на поле, рекомендую звернути увагу на те, з якої літери має починатися поле в базі даних (малої або великої). Звичайно, можна складати шаблон з полями, але тоді Ви можете не помітити деяких ляпів, як нестиковка відмінків.

Вже в наведеному шаблоні видно, що нам знадобиться ПІБ у родовому відмінку та полі, що містять тільки ім'я та по батькові. З першим полем ми можемо замінити «Кому» на «Отримувач» і тоді ПІБ у називному відмінку нас цілком влаштує. З другим полем дещо складніше і нам доведеться завести в базі даних ще один стовпець і заповнити його відповідними даними. В одному з наступних уроків я розповім як це зробити автоматично, а поки вважатимемо що таке поле у ​​нас вже є.

Для вставлення поля та прив'язки шаблону до бази даних необхідно виконати таку послідовність дій. У OpenOffice Writer вибрати базу даних

та натиснути "Define" ("Визначити").

Потім потрібно вставити поле з підключеної бази даних. Для цього натиснути Ctrl+F2, або в меню "Вставка" вибрати пункт "Поля" та "Інше". У вікні вибрати вкладку "Бази даних", у типі поля вибрати "Mail Merge fields", з бази даних вибрати відповідне поле і натиснути "Вставити". Власне, базу даних можна також вибрати в цьому вікні.

Після того, як одне поле вставлено, можна не закриваючи поточного вікна, виділити текст для заміни на наступне поле, вибрати поле і знову натиснути «Вставити». І так для кожного із полів.

Для MS Word ситуація в чомусь схожа і, можливо, зручніша. Після створення таблиці в MS Excel запускаємо MS Word і переходимо на вкладку «Розсилки». На цій вкладці можна побачити три необхідні кроки: «Початок злиття», «Складання документа та вставка полів» та «Завершення». Є ще проміжний крок "Перегляд результату", але він є опціональним.

І так робота розпочинається з вибору документа. Це може бути листи, наклейки, звичайний документ Word. Тут же можна запустити майстер злиття, який проведе Вас через усі стадії цього процесу. Наступний крок – вибір одержувача, тобто бази даних. Тут Ви можете вибрати готову базу даних (наприклад, створену на попередньому етапі таблицю MS Excel) або створити новий список. Після того, як список вибраний, стають активними кнопки «Вставити поле злиття», «Змінити список одержувачів» та ін. Через діалог «Змінити список одержувачів», який відкривається відповідною кнопкою, можна вибрати з усього списку лише необхідні для злиття запису.

При вставці полів мені здається зручним підсвітити всі поля сірим кольором, натиснувши кнопку «Виділити поля злиття». В іншому випадку, якщо таких полів багато, їх можна втратити в тексті.

Отже, шаблон готовий.

Генерація листів

Останній етап – генерація листів. В OpenOffice для цього в меню "Інструменти" вибираємо "Mail Merge Wizard..." і проходимо всі пропоновані кроки по об'єднанню нашого шаблону з базою даних.

У діалоговому вікні ми бачимо, що із запропонованих 8 пунктів нам деякі пункти виконувати не доведеться. Тож документ ми вже створили і редагувати його нам не треба, блок адреси ми теж уже вставили. Але давайте по-порядку.

Спочатку вибираємо, що створюємо листи на основі поточного документа і тиснемо "Далі" внизу вікна. Другим пунктом вибираємо, чи буде у нас цей паперовий лист або e-mail. Щоб лист можна було зберегти у файл слід вибрати перше. Тиснемо «Далі». Третім пунктом пропонують створити блок адреси, але оскільки ми його створили руками, то знімаємо всі галочки і переходимо до наступного кроку. Тут нам пропонують вставити звернення, але воно в нас теж уже є, тому тиснемо «Далі». Шостим пунктом можна відредагувати документ, подивитися документ із вставленими з бази даних полями і, якщо треба, якісь поля виключити.

На кроці 7 пропонується відредагувати кожен із створених у результаті з'єднання шаблону з базою даних файлів. І нарешті на останньому, 8 кроці, можна вибрати, що зробити з отриманими документами. Можна зберегти, роздрукувати або надіслати електронною поштою. При цьому можна зберігати всі документи в один файл або кожен документ окремо.

У MS Word процес злиття закінчується натисканням кнопки «Знайти та об'єднати».

З меню, що випадає, очевидно, що всі листи можна надрукувати і надіслати через e-mail, але не очевидно, що всі їх можна зберегти в файл. Для збереження треба вибрати перший пункт - "Змінити окремі документи". При цьому відкриється новий документ, в якому кожен новий лист розташовуватиметься з новій сторінці, а тут цей файл можна і зберегти.

Висновок

І так, у цьому уроці Ви дізналися, як використовуючи інструмент злиття створювати документи на основі шаблону. Давайте коротко запишемо послідовність дій:

1. Необхідно в табличному процесорі створити базу даних, з якої будуть братися дані заповнення полів.
2. Необхідно створити шаблон листа.
3. Зробити злиття і зберегти результат.

A, прізвища у стовпці Bта професії в стовпці C.

2. Створіть word документ (.doc або .docx)

(A), (B)і (C).

(A), (B)і (C) (A)- ім'ям, (B)- прізвищем, (C)- Професією.

Settingsпрограми.

3. Виберіть шляхи для файлів та папок

Select

4. Задайте аркуші та рядки потрібних даних

Excel file data sheets

Excel file data rows Excel file data sheets

1 .

Якщо ви хочете, щоб у формуванні документа брали участь усі аркуші та/або рядки вашого excel-файлу з даними - натисніть праворуч на відповідну кнопку з надписом Numbers(її напис при цьому зміниться на All).

5. Введіть шаблон імен нових word файлів

Введіть шаблон імен нових word-файлів:

New word files names template- це шаблон для імен нових документів, що генеруються програмою (word-файлів). Тут шаблон імен містить імена стовпців excel-файлу, обрамлені фігурними дужками: (A)і (B). При формуванні нового документа програма замінить усі (A)і (B)відповідними значеннями осередків з excel-файлу - це буде ім'ям нового документа (word-файла).

Ви можете задати свої обрамляючі символи на вкладці Settingsпрограми.

6. Натисніть "Generate"

Натисніть кнопку Generateі на екрані з'явиться прогрес виконання. Документів (word-файлів) буде створено рівно стільки, скільки рядків excel-файлу бере участь у формуванні.

7. Все

Усі документи (word-файли) створені та лежать у папці, вказаній у Folder to save the new word files. Всі:)

Exwog - генератор звітів з Excel у Word за шаблоном

Безкоштовний генератор файлів Word за шаблоном (файлом Word) на основі даних Excel файлу

Працює в Mac OS, Windows та Linux

Дозволяє ставити імена нових генерованих word файлів

Дозволяє задавати аркуші та рядки потрібних даних

Дозволяє ставити обрамляючі символи для імен стовпців Excel

Простий у використанні

Зберігайте ваші дані в Excel форматі (.xls та .xlsx) і генеруйте файли формату Word (.doc та .docx) у кілька кліків:)

Як це працює?

Погляньте на ваш файл Excel

У цьому прикладі excel-файл містить інформацію про клієнтів. Кожен рядок відповідає певному клієнту. Імена розташовані в стовпці A, прізвища у стовпці Bта професії в стовпці C.

Клацніть для перегляду

Створіть word документ (.doc або .docx)

Клацніть для перегляду

Створіть "шаблон" (word-файл) для формування нових документів (word-файлів). Тут текст "шаблону" містить імена стовпців excel-файлу, обрамлені фігурними дужками: (A), (B)і (C).

Програма буде генерувати нові документи щодо "шаблону" замінюючи все (A), (B)і (C)відповідними значеннями осередків із excel-файлу: (A)- ім'ям, (B)- прізвищем, (C)- Професією.

Також ви можете задати свої обрамляючі символи на вкладці Settingsпрограми.

Виберіть шляхи для файлів та папок

Виберіть шляхи для файлів та папок (кнопки з написом Select). У програмі ви задаєте такі шляхи:

Excel file with data (*.xls, *.xlsx)- це шлях до вашого Excel-файлу з даними (інформація про клієнтів);

Word template file (*.doc, *.docx)- це шлях до вашого "шаблону" (word-файлу, створеному на попередньому кроці);

Folder to save the new word files- це шлях до папки, в яку програма буде зберігати нові згенеровані документи.

Клацніть для перегляду

Вкажіть аркуші та рядки потрібних даних

Клацніть для перегляду

Задайте номери аркушів та рядків вашого excel-файлу з даними (інформація про клієнтів) за якими потрібно сформувати документи:

Excel file data sheets- номери аркушів вашого excel-файлу, які братимуть участь у формуванні нових документів;

Excel file data rows- номери рядків аркушів (аркушів, зазначених у Excel file data sheets) вашого excel-файлу які братимуть участь у формуванні нових документів. На підставі даних кожного вказаного рядка буде створено окремий документ (word-файл).

Нумерація листів та рядків у програмі починається з 1 .