Найчастіше, коли говорять про роботу в офісних додатках, мається на увазі використання ПК в якості друкарської машинки і, якщо чесно, то це досить дорога заміна фізичним механізмам, якщо не користуватися можливостями який дає нам ПК. Давайте про це і поговоримо. 
Вступ.
На мій погляд ПК з'явився як інструмент для вирішення принципу автоматизації рутинних процесів і в цьому він скажено гарний. Крім того розвиток функціональних програм є історія створення інструментів для реалізації перекладу авральних завдань в рутинні дії, як наслідок поглиблення поділу праці. Однак є області, в т.ч. і документообігу, де на сьогоднішній день ПК використовується саме як друкарська машинка в т.ч. і тому, що серйозних розробок не велося в силу того, що оклади людей сидять в накладних витратах і, як наслідок, не великі, що не скажеш про зростаючих обсягах документообігу.
Отже, поговоримо про будівництво, конкретно про складання виконавчої документації (Далі ВД).
про Виконавчу документації
Точніше про її текстову складову. Якщо коротко, то ВД є набір актів, журналів та інших документів, креслень, схем які складаються на кожен етап і навіть операцію (групи операцій) в будівництві, для підтвердження / відхилення виконуваних робіт проекту. Такі документи часто-шаблонні, їх перелік, в залежності від виду робіт, регламентований, і вони ведуться згідно фактичного графіка виконуваних робіт, формалізуючи затвердженої комісією правильність / відступ робіт проекту.
Велика частина робіт закривається за формою Акта огляду прихованих робіт (Затвердженого наказом Федеральної служби з екологічного, технологічного і атомного нагляду від 26 грудня 2006 року N 1128 (В редакції, введеної в дію з 6 березня 2016 року наказом Ростехнагляду від 26 жовтня 2015 року №42. Далі АОСР).
Вихідні дані для автоматизації.
По-цьому візьмемо форму АОСР за основу. Отже, у нас є шаблон документа, в який заноситься наступна інформація:
- постфікси номера акта;
- найменування об'єкта капітального будівництва;
- юридичні дані про учасників будівництва (Забудовник або Замовник; Особа, яка здійснює будівництво; Особа, яка здійснює підготовку проектної документації;
Особа, яка здійснює будівництво, яка виконала роботи, що підлягають огляду; інші особи.)
- перелік осіб з найменуванням організацій, посад і наказів підтверджують їх повноваження;
- найменування виконаних робіт;
- терміни виконання робіт;
- перелік робіт, що входять в виконані роботи;
- посилання на НТД і розділи проекту / тех.документацию;
- посилання на виконавчі схеми, акти випробувань (якщо такі потрібні);
- перелік використовуваних матеріалів з посиланнями на документи що підтверджують їх відповідність (паспорта, сертифікати тощо)
Думки про принципові методи вирішення завдання
Отже, в першому наближенні, можна просто створити наочну таблицю, в якій призначивши відповідні однотипні поля кожним актом ми отримаємо наочну онучу схеми виконання робіт на об'єкті. І в цьому немає нічого нового. Отже, нам необхідно пов'язати форму з осередками в таблицях даних і тут є 2 варіанти:
1. Злиття з файлом Word
2. Заповнення за допомогою макросів шаблону на базі Excel.
Кожен з цих способів має свої плюси і мінуси, але тому що злиття робить заміну в реальному часі, то мною було прийнято рішення вибрати другий пункт, який в реальному часі злиття не забезпечує і кожен раз необхідно буде здійснювати висновок актів, в разі коригування даних, заново. Це пов'язано з тим, що мені часто необхідна історія моїх дій.
Отже, тепер ми впираємося в 2 завдання:
1. Заповнення шаблону на основі табличних даних
2. Які поля досить ввести один раз, які буде змінюватися час від часу і які поля будуть відрізнятися в кожному акті.
Вирішуючи задачу №2 ми винесемо на окремий лист ті дані, які будуть єдині в рамках об'єкта / розділу проекту будівництва - це:
спойлер
- найменування об'єкта капітального будівництва;
- юридичні дані про учасників будівництва (Забудовник або Замовник; Особа, яка здійснює будівництво; Особа, яка здійснює підготовку проектної документації;
Особа, яка здійснює будівництво, яка виконала роботи, що підлягають огляду; інші особи.)
- перелік осіб з найменуванням організацій, посад і наказів підтверджують їх повноваження;
На поточному аркуші заповнимо лише один раз, а для інших актів просто проставимо посилання на ці значення:
спойлер
- посилання на НТД і розділи проекту / тех.документацию;
- Постфікс до номера акта;
І поля, які будуть змінюватися в кожному акті:
спойлер
- найменування виконаних робіт;
- терміни виконання робіт;
- перелік робіт, що входять в виконані роботи;
- посилання на виконавчі схеми, акти випробувань (якщо такі потрібні);
- перелік використовуваних матеріалів з посиланнями на документи що підтверджують їх відповідність (паспорта, сертифікати тощо)
Тепер про зручності, якщо записати ПІБ відповідальних осіб, їх організацію, наказ про призначення з датою, то за допомогою інструменту «перевірка даних» можна занести їх прізвища в спойлер , А формулою підтягувати їх регалії.
= ЕСЛІОШІБКА (ДВССИЛ (СЦЕПИТЬ ( " 'Дані для проекту'!"; АДРЕСА ((ПОИСКПОЗ (E30; 'Дані для проекту'! $ G $ 15: $ G $ 34; 0)) + 14; 6))); "- ")
Тобто на аркуші 'Дані для проекту' в діапазоні $ G $ 15: $ G $ 34, в 6м стовпці шукаємо значення в осередку Е30, і як тільки знаходимо нехитрим методом перетворимо на адресу, який буде перетворений на заслання формулами.
Нова проблема вже впирається в довжину рядка, якщо використовувати шрифт Times New Roman №10, то довжина тексту, яка буде виводиться на друк, не перевищує 105 символів. Тобто ми впираємося в необхідність створювати милицю для переносів. Отже код функції на VBA:
Function PatrOfString (StringOfTable As String, Nnumber As Byte) As String Dim МассівБлоков (1 To 10) As String Dim i As Integer 'Dim j As Integer' Dim k As Integer 'Dim p As Integer' For i = 1 To 10 Let МассівБлоков (i) = "" Next i Let k = 1 Let p = Len (StringOfTable) Let p1 = Len (StringOfTable) For i = 1 To Round (Len (StringOfTable) / 105) + 1 Step 1 If p> 0 And p <105 Then If k <= p1 Then Let МассівБлоков (i) = Mid $ (StringOfTable, k, p) Else If Mid (StringOfTable, k, 1) = "" Then If k <= p1 Then Let МассівБлоков (i) = Mid $ (StringOfTable, k, 105) Let p = p - 105 k = k + 105 Else j = 105 * i If j - k> = 105 Then j = k + 105 End If Do j = j - 1 Loop While Mid $ (StringOfTable, j, 1) <> "" Let МассівБлоков (i) = Mid $ (StringOfTable, k, j - k + 1) Let p = p - (j - k + 1) Let k = j + 1 End If End If Next i If Nnumber - 1> 0 Then If МассівБлоков (Nnumber) = МассівБлоков (Nnumber - 1) Then МассівБлок в (Nnumber) = "" End If PatrOfString = МассівБлоков (Nnumber)
Тобто ми спершу забираємо текст, потім вирізаємо 105 символів, шукаємо з кінця перший символ пробіл, як його знаходимо, то закидаємо в перший рядок масиву текст довжиною з першого символу, по номер знайденого пробілу. Потім продовжуємо операцію до тих пір поки текст не закінчиться або не заб'є вивідний масив. На даному етапі він обмежений пам'яттю в 10 рядків. Потім ми виводимо за посиланням зміст потрібного рядка з 1-10. З мінусів рішення - забивається пам'ять, і для кожного нового запиту здійснюється перерахунок заново. Але милицю працює.
Тепер висновок в типовий шаблон АОСР. Є знову 2 варіанти, або вручну прописувати порівнянні колонки (/ рядки в разі горизонтально розташованих даних під кожен акт), то це буде довго і ресурсомісткість підганяти кожен новий шаблон або зміни під таблиці даних. Тому робимо оптимізацію. Дані на кожен один акт будуть розташовуватися вертикально, а зіставлення керуючої комбінації символів (на латиниці, тому що самі акти суцільно на кирилиці) будуть рядки з інформацією в цих актах, таким чином в подвійному вкладеному циклі пошуком в тексті керуючих символів ми зіставимо потрібне значення з колонки.
Do wb.Worksheets ( "Приклад акту вхідного контролю"). Copy after: = Worksheets (Worksheets.Count) Set новийЛіст = wb.Worksheets (Worksheets.Count) For x = 1 To 15 Step 1 'Перебираємо стовпці в аркуші "Приклад акту вхідного контролю "For y = 1 To 71 Step 1 'Перебираємо рядки в листі" Приклад акту вхідного контролю "If Sheets (новийЛіст.Name) .Cells (y, 20) = 1 Then Let k = CStr (Sheets (новийЛіст.Name) .Cells (y, x)) 'Шукаємо тільки якщо в осередку щось є If k <> "" Then For i = 1 To Кол_воЕл_овМассіваДанних Step 1 Let k = Replace (k, arrСсилкіДанних (i), Worksheets ( "БД для вхідного контролю (2) "). Cells (i, НомерСтолбца)) Next i новийЛіст.Cells (y, x) = k End If End If Next y Next x 'Якщо Ви захочете додати нові дані для автозаповнення, продовжуйте цей список. 'Осередки мають координати формату Cells (3, 2), де 3 - приклад номера рядка, 2 - приклад номера стовпця' Щоб легко дізнаватися номер стовпчика, можна включити стиль посилань R1C1 '(Файл -> Параметри -> Формули -> галочка на поле "Стиль посилань R1C1") 'або задавати координати в форматі Cells (1, "A"), де 1 - приклад номера рядка, "А" - приклад літерного позначення стовпчика Rem - = Уточнюємо ім'я файлу і поточний шлях до папки звідки запущений макрос = - Let имяфайла = имяфайла + CStr (Worksheets ( "БД для вхідного контролю (2)"). Cells ( "1", НомерСтолбца)) + "-" Let имяфайла = имяфайла + CStr (Worksheets ( "БД для вхідного кін троля (2) "). Cells (" 2 ", НомерСтолбца)) +" .xlsx "НовийПуть = Replace (ThisWorkbook.FullName, ThisWorkbook.Name, имяфайла) Application.DisplayAlerts = False 'вимикаємо висновок попереджень Sheets (новийЛіст.Name) .Copy 'Копіюємо поточний лист в нову книгу ActiveWorkbook.SaveAs Filename: = НовийПуть, _ FileFormat: = 51 ActiveWindow.Close Sheets (новийЛіст.Name) .Delete' Видаляємо створений лист Application.DisplayAlerts = True 'Включаємо висновок попереджень назад Let НомерСтолбца = НомерСтолбца + 1 Loop While НомерСтолбца <= КонечнийНомерСтолбца End Sub
Ну і останній момент на даному етапі - ми впираємося в продуктивність системи і при великій кількості актів їх висновок буде займати години. Щоб прискорити процес я використовую наступний варіант: Копіюю вміст листа з даними в новий лист макросом, він отримує в своєму імені циферку (2), потім запускається ще один макрос, що прискорює Excel, але відключає ряд функціоналу:
'Прискорюємо Excel шляхом відключення всього "гальмуючого" Public Sub AccelerateExcel ()' Більше не оновлюємо сторінки після кожної дії Application.ScreenUpdating = False 'Розрахунки переводимо в ручний режим Application.Calculation = xlCalculationManual' Відключаємо події Application.EnableEvents = False 'Чи не відображаємо кордону осередків If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If 'Відключаємо статусну рядок Application.DisplayStatusBar = False' Відключаємо повідомлення Excel Application.DisplayAlerts = False End Sub
А після виведення всіх даних з форм я виганяю аналогічний макрос де тим же самим змінним привласнюю значення true і видаляю дублюючий лист, що б не заважали.