Continuing the theme of automating file output by template. Excel

Automation of filling and outputting files according to routine document templates is one of those areas in the construction industry where traditionally software, except accounting, is at the level of licked crafts, in my humble opinion. Therefore, developing the topic , I invite you to discuss the problems and opportunities that I had to face in the implementation process based on MS Excel. Half a year has passed

since the previous article . During this time, using this blank, the text part of the Executive documentation was developed and delivered to the Customer. Based on the results of work and reviews of rare participants, the following changes were made to the file, which I would like to talk about and these are 3 big topics:

1. Aesthetics and usability
2. Code Optimization + Innovations
3. Structure and Relations

So - go ahead !!!

1. Aesthetics and usability

- Tables are primarily tables, faceless cells with signed columns and rows. However, very often we are faced with a situation where additional explanations are needed for the value that will be in the cell, or it is necessary to intensify the user's attention to the importance of the entered value. It is especially important if, as in my case, the lines in the column of a very long table contain diverse information, for example: dates, types of work, materials, signatories, and many others. In such cases, we have 2 tools for solving the problem:

1. Note
2. “Data” tab -> “Data verification” menu item -> “Message for input” tab


There are drawbacks to this solution, in particular, tooltips can be annoying, but in a situation where monitors on laptops with a resolution of 1366 × 768 are on a 15 "object, this is a reasonable compromise, so that the workspace is as large as possible.

If you carefully analyze the data, it will turn out that there will be 3 types of cells in the table:

1. cells into which it is directly necessary to enter new text information;
2. cells whose value can take on a value from a limited range entered in advance, for example: name and position of signatories;
3. cells in which formulas are written, for example, there is a part of the data that will be repeated from act to act and it is enough to enter such information once, for example: name of the object, site, organization, etc .; or formulas designed to implement technical capabilities, for example: line wrapping, pulling up workloads, regalia according to name, etc.

Thus, it turns out that it is logical to highlight the necessary fields with the background color and put protection on the sheet for cases where the violation of the formulas will be critical, for example, in Excel very often formulas fly if you do not copy the values, but cut-paste, which can be limited along with selecting and editing cells containing formulas, for example, writing a macro on a sheet:

Private Sub Worksheet_Activate()
    Worksheets("Ваш Лист").EnableOutlining = True
    Worksheets("Ваш Лист").Protect Password:="111"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Application.CutCopyMode = xlCut Then
    Application.CutCopyMode = False
  End If
End Sub

Here, the first procedure will constantly protect the sheet with the password 111, the second will block the cut-paste functionality. Needless to say, this all works only with the included macros, but on the other hand, without them, the file will not function 100%.
For the cases of claim 2, it is reasonable to create a sheet where the columns will contain changing values, write references to ranges in them, give them names, i.e. on the “Formulas” tab -> “Name Manager” assign names to each range and through the “Data” tab -> “Data Validation” menu item -> Parameters tab -> validation condition - “List” implement a drop-down menu.

And, of course, do not forget to set the formatting conditions in color, for example, for cases when all the necessary rows in the column are filled in via “Conditional formatting”, for example, the conditional formatting formula fills the cell if the following cells below it contain text: = AND (DLSTR (E5) > 0; DLSTR (E6)> 0)

2. Code optimization + innovations

You will have to start from afar, namely, return to the question of the implementation of the template filling mechanism. If you decide to fill out a template in Excel and Word format, then these will be completely 2 different mechanisms. Basically, values ​​are written to specific file cells or ranges of cells in the Excel file and have a binding of the form (y, x) (don’t ask why they have a row in front of the column when addressing - I don’t know), for example: Worksheet.Cells (y, x ) = k. Hence the first thought that you can fill in an Excel template either explicitly, i.e. directly the whole macro will contain what comes from and where it is laid, but what if you have to make changes to the data tables or a new form of the template comes out? Hence the second implementation idea, the code of which is described in the first article, is parsing some characters, which first fill the array, and also in turn contains the template file in the right places. Then, in each row of the template, a match is searched for with the elements of the array in turn, if there is a match, then the serial number of the array is tied to the row of the table where the data comes from, and the column is taken from the sheet in which we indicate which acts we want to display. In total, several nested cycles, which imposes restrictions on the formatting of the Excel template, the simpler the better, because the more cells to parse, the longer the template will fill with data.

By popular demand, I integrated the ability to output Word format to a template, and there are actually 2 ways to output text:



There is a huge gap between the outputs to the Word and Excel file formats, which is as follows:

The Excel template requires you to configure the display for a specific printer before use, because The actual printable area varies from model to model. Also, line wrapping is possible, but only within the cell / merged cells. In the latter case, do not auto-line feed, in case of text wrapping. Those. You will have to manually determine in advance the boundaries of the area that will contain the text, which in turn should be removed. But you have precisely set the boundaries of the print and the displayed text and are sure that the information (but not the content) will not move from one sheet to another.

When setting up a Word template, it automatically transfers the text to the next line if it has not been removed along the width of the cell / line, however, by doing so it causes an unpredictable vertical shift of the text. Given the fact that according to the requirements for Executive documentation in construction it is FORBIDDEN to print one act on 2 or more sheets, this in turn also gives rise to problems.

The second big innovation was the rejection of the implementation of text line wrapping from a VBA macro and replacing it with an Excel function, which accelerated the work with the file.

For the first line:

{= IF ERROR (IF ($ F $ 20 <> "-"; IF (DLSTR ('Project Data'! $ C $ 3) <106; 'Project Data'! $ C $ 3; PSTR ('Project Data'! $ C $ 3; 1; 105-SEARCH ("*"; PRIVSIMV (PSTR ('Project data'! $ C $ 3; 1; 105); STRING ($ 1: $ 10));)))); "-") }

For subsequent:

{= IF ERROR (IF ($ F $ 20 <> "-"; PSTR ('Project Data'! $ C $ 3; SUM (DLSTR (F $ 1: F1)) + 1; 105-SEARCH ("*"; RIGHT ( PSTR ('Project Data'! $ C $ 3; SUM (DLSTR (F $ 1: F1)) + 1; 105); STRING ($ 1: $ 10));))); "-")}

The principle of arrays is used here, i.e. Such text is entered by Ctrl + Shift + Enter, and not the usual Enter. The formulas themselves are located in cells F1 and F2. 'Data for the project'! $ C $ 3 - a link to the names of the object, the text length of which is more than 105 characters. The hyphenation is organized in case of exceeding the text length of 105 characters.

Another innovation was the general registry, as well as control over the write-off of materials on acts of AOSR, but there is nothing new here, just parsing the corresponding lines in the INDEX + SEARCHPOS link, which are described in many manuals.

3. Structure and communications

But my post would have remained an ordinary post with the next game in the invention of the bicycle with tools that are designed for a completely different, if not one BUT (!) Monthly-daily schedule.



The idea that you can hang a lot of things on it, for example, filling out the General Journal of Works in Section 3 - the name of the work by date, the sequence and necessity of the Acts of examination of hidden works and not only - took hold of my thoughts. Excel usually paints dates, depending on the date ranges - beginning and end, but not at the construction site !!! At the construction site, the volumes are written in the calendar schedule, and depending on which date, the volumes are opposite the name of the work and by which the date ranges of the reporting periods are obtained. The screenshot shows the volumes falling into the systematic reporting periods (1 month) in gray. Thus, it turns out that if:

• we detail and draw up the order of work for the section of the project and write them in the order of priority, then we will get the order of work;
• on the calendar chart we designate the reporting periods (grayed out) and organize the summation of volumes for the reporting periods for each line - we will get the volumes of work for AOSD and other acts;
• sub-ranges of work for each reporting period can be selected with a macro or formula.

Thus, it turns out that with the help of MSG it is possible to draw up documentation ... And this simplifies the visual perception and visual control of the scope of work.

I hope you were interested. You can try the program via the link

Thank you for your attention.