For those who were afraid, but still ready to try. (Excel)

Often, when people talk about working in office applications, it is intended to use a PC as a typewriter and, frankly, this is a rather expensive substitute for physical mechanisms, if not using the capabilities that the PC gives us. Let's talk about it.

Introduction

In my opinion, the PC appeared as a tool for solving the principle of automating routine processes, and in this he is damn good. In addition, the development of functional programs has a history of creating tools for the implementation of the translation of emergency tasks into routine actions, as a consequence of the deepening division of labor. However, there are areas, including and workflow, where today the PC is used exactly as a typewriter including. and because serious development was not conducted due to the fact that the salaries of people are sitting in overhead costs and, as a result, are not large, which cannot be said about the increasing volumes of workflow.

So, let's talk about construction, specifically about the preparation of as-built documentation (hereinafter ID).


Most of the work is closed in the form of the Inspection Certificate for Hidden Works (approved by order of the Federal Service for Environmental, Technological and Nuclear Supervision dated December 26, 2006 N 1128 (As amended, effective March 6, 2016, Order No. 42 of Rostechnadzor) Further AOSR).
')

Baseline data for automation.

Therefore, we take the form AOSR as a basis. So, we have a document template in which the following information is entered:

- postfix number of the act;
- name of the capital construction object;
- legal data on construction participants (Developer or Customer; Construction Person; Person who prepares project documentation;
The person carrying out the construction, completed the work to be surveyed; other faces.)
- a list of persons with the name of organizations, positions and orders confirming their authority;
- name of work performed;
- turnaround time;
- list of works included in the work performed;
- references to reference documents and sections of the project / technical documentation;
- references to executive schemes, test reports (if required);
- list of materials used with references to documents confirming their compliance (passports, certificates, etc.)

Thoughts on the fundamental methods of solving the problem

So, in the first approximation, you can simply create a visual table in which by assigning the corresponding fields of the same type to each act, we will get a descriptive shingles on the scheme of work on the object. And this is nothing new. So, we need to associate the form with cells in the data tables, and here there are 2 options:

1. Merge with Word file
2. Filling using Excel-based template macros.

Each of these methods has its pros and cons, but since the merge makes a replacement in real time, then I decided to choose the second item, which does not provide a real time merge and it will be necessary to deduce acts every time, in case of data correction, again. This is due to the fact that I often need a history of my actions.

So, now we run into 2 tasks:

1. Filling the template based on tabular data
2. Which fields are sufficient to enter once, which will change from time to time and which fields will differ in each act.

Solving the problem number 2, we will put on a separate sheet the data that will be the same within the object / section of the construction project - this is:


On the current sheet, fill in only once, and for the rest of the acts we just put down references to these values:


And the fields that will change in each act:


Now about convenience, if you write down the names of the responsible persons, their organization, the order of appointment with the date, then using the “data check” tool you can put their names in the spoiler , and pull up their regalia with a formula.

=((("'  '!";(((E30;'  '!$G$15:$G$34;0))+14;6)));"-") 

Those. on the 'Data for the project' sheet in the $ G $ 15 range: $ G $ 34, in the 6th column we look for the value in cell E30, and as soon as we find it using the simple method we will convert it into an address that will be converted into a link by formulas.

The new problem already rests on the length of the line, if you use the Times New Roman font number 10, the length of the text to be printed does not exceed 105 characters. Those. we resist the need to create a crutch for transfers. So the function code on 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) 

Those. we first take away the text, then cut out 105 characters, look for the first character from the end of the space, as we find it, then we drop the text from the first character into the first line of the array, by the number of the space found. Then we continue the operation until the text ends or the output array is clogged. At this stage, it is limited to 10 lines of memory. Then we derive from the link the contents of the desired line from 1-10. From the minuses of the solution, memory is clogged up, and for each new request a recount is performed again. But the crutch works.

Now output to the standard AOSP template. There are again 2 options, either manually assigning a column mapping (/ rows in the case of horizontally positioned data for each act), this will be long and resource-intensive to adjust each new template or changes to the data tables. Therefore we do optimization. The data for each one act will be placed vertically, and the comparison of the controlling combination of characters (in Latin, because the acts themselves are entirely in Cyrillic) will be lines with information in these acts, so in a double nested loop, we will compare the necessary text in the text of control characters value from column.

 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 

Well, the last point at this stage - we run into system performance and with a large number of acts, their output will take hours. In order to speed up the process, I use the following option: I copy the contents of a sheet with data into a new sheet with a macro, it gets a numeral (2) in its name, then another macro is launched, which accelerates Excel, but disables some functionality:

 ' 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 

And after outputting all the data from the forms, I run a similar macro where I assign the value true to the same variables and delete the duplicate sheet so that it does not interfere.