Automation of the preparation of as-built documentation for construction in Excel. Finance in Excel Automate work in Microsoft excel documents

To automate repetitive tasks in Microsoft Excel, you can quickly record a macro. Let's say you have dates in different formats and you want to apply the same format to all of them. This can be done with a macro. You can record a macro that applies the desired format and then run it as needed.

When you record a macro, all the actions that are described in Visual Basic for Applications (VBA) code are recorded. These actions can include entering text or numbers, clicking cells or commands on the ribbon or menu, formatting cells, rows, or columns, or importing data from an external source such as Microsoft Access. A Visual Basic Application (VBA) is a subset of the powerful Visual Basic programming language that is included with most Office applications. Although VBA provides the ability to automate processes between Office applications, you do not need to know VBA code or programmatic programming if you need to.

It is important to know that when you record a macro, almost everything you do is recorded. So if you make a mistake, such as pressing the wrong button, the macro recorder will log that action. In such a case, you can rewrite the entire sequence or modify the VBA code. Therefore, before recording the process, you should work it out well. The more precisely you write the sequence, the more effectively the macro will work.

Developer, which is hidden by default, so you need to enable it first. For more information, see Show the Developer tab.

Macro Recording

On the tab Developer click Macros to view the macros associated with the workbook. You can also press the keys ALT+F8. This will open a dialog box. Macro.

Attention:

		Learn about macro security settings and their meaning.
	Macros can be run in a variety of ways, such as a keyboard shortcut, a graphic object, the Quick Access toolbar, a button, or even when a workbook is opened.
	You can use the Visual Basic Editor to edit macros that are attached to a workbook.

	assign a macro. In field Assign macro


	Learn how to enable and disable macros in Office files.
	Press the keys ALT+F11.

Working with written code in the Visual Basic Editor (VBE)

With the Visual Basic Editor (VBE), you can add your own variables, control structures, and other elements to the recorded code that the macro recorder does not support. Since the macro recorder captures almost every step that occurs during recording, you may also need to remove unnecessary code. Viewing recorded code is a great way to learn VBA programming or hone your skills.

An example of modifying the recorded code can be found in the Get started with VBA in Excel article.

Macro Recording

Before recording macros, it is useful to know the following:

A macro written to operate on an Excel range will only run on cells in that range. Therefore, if you add a new line to the range, the macro will not apply to it.

If you need to record a long sequence of tasks, we recommend using several smaller macros instead.

A macro can also contain non-Excel tasks. The macro process can span other Office applications and other programs that support Visual Basic for Applications (VBA). For example, you can record a macro that first updates a spreadsheet in Excel and then opens Outlook to email it.

Macros and VBA tools are on the tab Developer, which is hidden by default, so you need to enable it first.

Go to settings _gm_ excel... toolbar _gt_ ribbon _amp_.

To record a macro, follow the instructions below.

Working with macros recorded in Excel

On the tab Developer click Macros to view the macros associated with the workbook. This will open a dialog box. Macro.

Note: Macros cannot be undone. Before you run a recorded macro for the first time, save or make a copy of the workbook to prevent unwanted changes. If you are not satisfied with the results of running a macro, you can close the workbook without saving it.

The following is more information about working with macros in Excel.

		Learn how to enable and disable macros in Excel for Mac.
	To save time on performing frequently recurring tasks, you can record the corresponding sequence of actions in the form of a macro. Learn how to create and run macros.
	If a workbook contains a VBA macro that you want to use elsewhere, you can copy the module to another workbook using the Microsoft Visual Basic Editor.
Assigning a macro to an object, shape, or graphic element	On a sheet, right-click the object, picture, shape, or element to which you want to assign an existing macro, and then click assign a macro. In field Assign macro select the macro you want to assign.
	You can assign a macro to an icon and add it to the Quick Access Toolbar or Ribbon.
	You can assign macros to forms and ActiveX controls on a sheet.
Opening the Visual Basic Editor	On the tab Developer click Visual Basic or select Service > Macro > Visual Basic Editor.
	Learn how to find help for Visual Basic elements.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the website

Creating program code to automate certain procedures opens up almost limitless possibilities for developing and using Excel-based applications.

Macro Recording

The first automation tool that the user encounters is the so-called macro recording. After you include this entry, Excel automatically generates program code with a sequence of operations performed by the user.

Assessing the originality of the solution, the practical use of this function, from our point of view, is very doubtful. The fact is that reusing a recorded macro without making changes is impossible with rare exceptions. An untrained user usually sees just a set of commands, but does not get the expected result from the recorded macro. In addition, the user has an erroneous opinion about the ease of writing programs in VBA.

Real programming has nothing to do with recording macros. It is required to know both the basics of the programming language and to study the object model of Excel. By the way, just for the latter case, the "record macro" function is of some use as an addition to the help system. In the automatically written code, you can quickly find the desired properties or methods of an Excel object.

Macros Excel 4.0

Few people have come across this in practice, but in version Excel 4.0 it was possible to create macros and execute their sequences. There is a special type of workbook sheet for this purpose. Excel 4.0 macros (XLM) are supported in all versions up to now.

Try to insert "international macro sheet" and write 2 commands in it (see picture). Then run this macro by specifying the cell (the easiest way to run macros is through the keyboard shortcut Alt+F8):

The "International Macro Sheet" is required to record commands in English; there are also analogues of Russian commands.

XLM macros allow you to execute any Excel commands implemented at that time, as well as form and open dialog boxes. Since the late 1990s, Microsoft has strongly recommended rewriting XLM macros in Excel applications to VBA programs. However, the functionality of the old macros is still fully supported.

Visual Basic for Applications 5.0

The first version of Excel that supports the Visual Basic for Applications programming language appeared in 1993 - Excel 5.0. Originally, the code editor was a special type of workbook sheet.

The full object model of Excel was supported, the user could separate the code into program modules. For the first time, it became possible to visually create and modify dialog forms - a collection DialogSheets. For these forms, there are controls with their own event handlers, the forms themselves can only be opened in modal mode. Using DialogSheets, it was already possible to create quite complex user interface options. In the future, the DialogSheets functionality was replaced with custom forms. UserForms. Old dialog forms in versions starting with Excel 2000 are only supported for backwards compatibility - they do not even have a description in the help system.

VBA 6

Excel 2000 (version 9.0) includes a new version of the Visual Basic for Applications language. It added support for custom classes, forms, and event handler procedures. The syntax of the VBA language is unified with the main applications of Microsoft Office (Excel, Word, Access, PowerPoint). Somewhat stands out, perhaps, is Microsoft Access, which supports its own form editor.

Main innovations of VBA6:

User classes.
Access to standard sheet and workbook event handlers.
Ability to handle events using a keyword WithEvents.
Custom Forms UserForms with new controls.
Ability to use external ActiveX controls and code libraries.
Several new useful features (for example, Join And Split).

In addition, based on the new version of VBA, there were special versions of Excel for developers that supported the creation of COM DLLs. See article for more details add-ons.

Using External Libraries

External libraries of the COM DLL (ActiveX DLL) format are connected through the use of links in the VBA project (menu Tools\References). Visual controls (.ocx-files) are connected in a similar way when selecting the menu item T ools\Additional Controls. When using links to external files, you may need to pay special attention to the integrity of the project when it is distributed to other computers. Standard libraries (for example, MSCOMCTL.OCX) are usually relinked correctly on client computers, automatically picking up a link to the latest version. Problems can arise with non-standard libraries and native ActiveX controls. To check the connection of external COM DLLs, you can theoretically use the collection Application.VBE.ActiveVBProject.References. But in practice, it is better not to use the VBE object in applications, since access to it can be limited by Excel settings (starting with version 2007).

COM technology allows you to provide access to external libraries and applications without the use of hard links. For this, the so-called late object binding is used. As an interface IUnknown type in VBA Object; the standard procedure is used to instantiate an object CreateObject, and to get an existing instance - GetObject.

Dim oWord As Object Set oWord = CreateObject("Word.Document") oWord.Application.Visible = True

To call the functions and procedures of Windows dynamic libraries (Win32 DLL) in any version of VBA, the construct is supported declare. For example:

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal sBuf As String, ByRef iSize As Long) As Boolean

After such a declaration, the function can be called in the usual way as any VBA function.

In addition to standard Win API procedures and functions, you can develop your own DLLs and include them in VBA projects in a similar way.

VSTO

Microsoft provides Excel automation in its other developer products. For example, there is a Visual Basic 6.0 ActiveX DLL project template adapted for Microsoft Office. Microsoft currently supports a .NET-based application development stream called Visual Studio Tools for Office (VSTO). VSTO is a set of project templates and Visual Studio .NET runtime tools for developing libraries and add-ins for various Microsoft Office applications.

An example source code for a .NET project for Excel can be found at Other.

Excel Automation for External Applications

Like calling external COM DLLs, Excel itself can be an automation object for other programs. For example, you can call and manipulate a workbook using vbs scripts. For example, create a text file with the following content:

Set oExcelApp = CreateObject("Excel.Application") oExcelApp.Visible = True Set oWorkbook = oExcelApp.Workbooks.Add Set oWorksheet = oWorkbook.Worksheets.Add oWorksheet.Cells(1,1)="Hello"

Save this file with vbs extension and then open it. Upon successful launch, Excel will be opened, a workbook and sheet will be created, on which the text "Hello" will appear in the upper left cell. Similarly, you can create very complex programs that manipulate Excel files. When transferring code to a vbs script (or another application), it is initially more convenient to create and debug the code in the VBA editor of Excel itself - it contains complete information about the syntax of methods and properties of built-in objects. In this regard, it is good practice when programming in VBA to use full paths to access objects: for example, instead of active workbook prescribe Application.ActiveWorkbook. This programming style will save you from hidden errors when porting code to any other applications.

File Security

Microsoft, showing concern for users, is trying in every possible way to prevent the accidental distribution of computer viruses written in VBA. Based on our own experience, we can assume that such "care" often brings more problems to users than increases the safety of their work. In our practice of working with Excel files (since 1995), we never met macro-viruses written specifically for Excel applications. At the same time, we do not exclude the potential possibility of writing such viruses (we can even try to create something similar), but it is not clear why anyone needs it. Using the same scripting languages is a much more serious threat to the operating system. Not to mention that Visual Basic is not the best medium for writing computer viruses.

But it's not even bad that Microsoft is reinsuring itself in the search for potential threats. The inconsistency and constant complication of Excel-VBA project security management is frustrating. In addition, there is a clear trend towards the use of digital signatures from trusted publishers. At the same time, obtaining the right to sign is a paid service, and the technology itself has errors in its implementation with Excel files (for more details, see the next section).

Here's how security features have changed historically:

Excel 4.0-7.0- not completely sure, but it seems that there was no protection against macro viruses, macros were run without additional warnings.
Excel 97 (8.0) - a protection checkbox has appeared in the general settings.

Excel 2000 (9.0)– a special dialog appeared in the menu Service\Macros with a choice of one of three security levels and a list of trusted certificates. By default, the security level is medium. That is, when opening files with macros, the user himself is asked to decide how safe it is. Excel add-ins activate by default without additional warnings.

Excel 2002 (10.0)- added another "highest" level of security, allowing the execution of macros signed by a trusted publisher, and only from trusted sources. By default, the security level is medium.
Excel 2003 (11.0)– the same as in the previous version, but the security level is set to high by default. That is, macros are disabled without warning.

Excel 2007 (12.0)- the parameters interface has been rewritten, the so-called "Security Control Center" has appeared. Security levels have changed in behavior, although there are still four of them. By default, when you open a file with macros, a window appears under the ribbon with the ability to activate macros. Relatively speaking, this is the same macro connection dialog, but it opens non-modally. On the one hand, the solution seems attractive in terms of interface, but it can lead to incompatibility with older versions: for example, the user can work for some time without macros, and then enable them. There is a separate option to be able to control the VBE object at the code level, which is disabled by default. Also, the security of Excel add-ins is separately configured, which, by default, are still connected without additional questions.

Keep these differences in customization in mind when developing and distributing your own Excel applications.

From our point of view, the weak point in the security setting is that all settings are stored in the Windows system registry in an open form, and theoretically it is not difficult to change them when other applications are running. Often this is done by installation programs for Excel applications.

VBA Certificates

Starting with Microsoft Office 2000, Microsoft Authenticode technology became available, which allows you to sign VBA executable code in macros and add-ins. For a solution developer, this means the ability to run programs with a set security level. High And Very tall.

Digital signatures maintain the integrity of distributed solutions (no one can make changes to the signed code, except for the owner of the certificate) and verify the identity of the developer (unambiguously identify the author of the solution).

There are several options for obtaining certificates:

Self signed certificate
Certificate from local CA
commercial certificate

The possibilities of using a self-signed certificate are limited mainly to personal use. In order to avoid a constant warning about a certificate error, you need to add it to the list of trusted sources.

If there is a local certification authority (within an organization, for example), it is possible to create certificates issued by this authority. With Active Directory, it is possible to distribute the root certificate to users' computers, which will allow signed code to be identified as trusted. To create a local certification authority, you can use Windows Server Certificate Services (Certificate Services), or use solutions based on the OpenSSL package.

If you need the widest distribution of solutions based on Microsoft Office, it is recommended to purchase a commercial certificate for code signing (Code Signing). The use of well-known certificate authorities will allow users to be sure that they receive the product from a well-defined supplier and in an unmodified form.

However, there are a few points in working with certificates that need to be mentioned:

The certificate is required on the computers of all developers who make changes to the VBA project.
Sometimes situations arise when even without changing the code, a signed project is considered changed. In this case, working with macros is impossible, and the only way out of this situation is to remove the signature in the project settings.

Feedback on master classes and trainings

A personal schedule for passing lessons is just a godsend!

Good afternoon, Dmitry! I really enjoyed learning from your program. I liked the content (quite a lot of situations were considered), the organization (the personal schedule for passing the lessons is just a godsend!), the presentation (everything is very clear and intelligible).

I especially liked it when you solved problems live, I always ask teachers to think out loud, because this is exactly the logic of reasoning, the logic of searching and solving problems.

Now, as in a nursery rhyme:

how to write well

no need to stick to a friend,

no need to call

no need to wait

and you can take and write! (to make things easier for yourself)

Thanks a lot!

Tatiana Bogoslavskaya about practical training

In February 2013, I already participated in a webinar (block "Practitioner" and "Specialist") led by Dmitry.

November-2013 training is already my second. There will be an opportunity and an interesting topic - I will definitely try to take part in the next webinars, because they are really informative, interesting and most importantly - at the end of them there is a return in the form of quality results in the work.

I will not spread my thoughts along the tree, only the specifics:

1. Available for money;

2. Clear explanation of the material with practical examples;

3. Prompt and qualified answers to questions that arose during the training, in the forum and by mail;

4. To maintain "form" and consolidate skills - I highly recommend such webinars and master classes where Dmitry teaches.

Andrey Aganin about practical training

Would love to attend another training.

Good afternoon, Dmitry!

Thanks for the training! I am one of your three students who completed all your assignments. It was very interesting and exciting. Some tasks turned out to be simple, and some very difficult - this was a very useful experience. The manner of presenting the material turned out to be unusual - a video was sent in which the main thing in the task was highlighted, due to this, time was saved - it was possible to study when there was time. On the other hand, there was a lack of live communication during which it was possible to ask emerging questions. With great pleasure I would visit some other training. Your teaching style is very simple and intelligible to explain complex things, it helps a lot in mastering the material.

Sergey Borzenkov about practical training

Our group was unanimous

Yesterday, 11/20/2012, the first webinar (hopefully not the last) was held by Dmitry.

A little about my impressions.

1. Even when studying his free video tutorials, I really liked his manner of presenting the material. From my own experience I know that having knowledge on any issue and the ability to explain to others are TWO huge differences.

Fortunately, Dmitry has both a wealth of experience and teaching inclinations. Explains everything in an accessible and understandable way.

2. During the webinar, he explained what result should be obtained, and wrote a macro from scratch, giving explanations for one or another command. Answered the questions that came from the "students". I want to note right away that there were few questions.

ALL-RUSSIAN CORRESPONDENCE FINANCIAL AND ECONOMIC INSTITUTE

DEPARTMENT OF AUTOMATED PROCESSING OF ECONOMIC INFORMATION

Course work

in the discipline "Informatics"

on the topic

"Automation of the user's work in the environment MS office »

Executor: K. V. Baburin

Group TNF

record book number 1271/737

Supervisor: G. A. Zhukov

Moscow

- Introduction

1. Interaction of applications (3-4 pages).

- Theoretical part

MICROSOFT WORD

2. General provisions (5 pages).

3. Automating tasks and getting help (5 pages).

a) Moving around a large document (5 pages).

b) Copying text (5-6 pages).

c) Saving the document while working (6 pages).

d) AutoCorrect (6 pages).

e) Auto format when entering (6-7 pages).

f) Abstract (7 pages).

g) Master of Letters (7 pages).

h) Assistant (7 pages).

4. Spell check (8 pages).

5. Tables, borders and fills (8-9 pages).

6. Collaboration of several users (9 pages).

7. Multi-language support (10 pages).

MICROSOFT EXCEL

8. General characteristics of the spreadsheet processor (10-11 pages).

9. Basics of working in the Excel environment (11-12 pages).

10. Building tables (12-13 pages).

11. Construction and design of diagrams (13-14 pages).

12. Excel functions (14-15 pages).

13. Additional features of Excel 15-16 pages).

MICROSOFT ACCESS

14. A few words about ... (16 p.).

15. Tables (16 pages).

17. Forms (17 pages).

18. Readouts (18 pages).

19. Macros, macro commands (18 pages).

20. Modules (18 pages).

- Conclusion(19 pp).

- Practical part

21. General characteristics of tasks (20 pages).

22. Designing forms of output documents .... (20 pages).

23. Tables and diagram (21-24 pages).

25. User manual (25 pages).

- List of used literature(26 pages).

- Introduction:

Any enterprise is faced with the problem of automating the work of individual employees and departments as a whole. The first problem with this is how to choose the right software products? The desired properties of an information system are highly dependent on the size of the enterprise and the specifics of its business, but there are also universal requirements.

Of course, any enterprise needs tools for working with texts (creation, editing, paperwork), as well as for data processing (spreadsheets, analysis and planning tools, DBMS). But this is usually not enough. After all, different people work with documents, and documents often consist of various parts, including texts, numbers, tables, graphs, diagrams, drawings, even sound or video.

Today, all this is possible thanks to the family of programs created by Microsoft to support office activities Microsoft Office.

The Microsoft Office suite of software products includes several applications that form a single environment for processing a wide variety of information. Microsoft Office includes the powerful Microsoft Word word processor, Microsoft Excel spreadsheets, Microsoft PowerPoint presentation and presentation tool, and Microsoft Outlook organization and scheduling tool. In addition, there is a professional version of Microsoft Office, which, in addition to the listed applications, includes the database management system (DBMS) Microsoft Access.

Application interaction

Microsoft Office is highly integrated applications. This means that all programs that are part of Microsoft Office "know how" to exchange information and allow you to do this quite simply.

For example, if it is necessary to prepare a report on the results of a financial period containing illustrations, then the data can be processed in Microsoft Excel, graphs and charts can be built on their basis and then placed in the text of a document created in Microsoft Word. The chart is moved using copy to the Microsoft Windows clipboard and paste into the document, which are available through the main application menu, through toolbars, through hot keys or through the context menu that appears when you click the right mouse button. Finally, an illustration, like any other object, can be "linked" to a document by simply "grabbing" the mouse in one application and "drag" it to a suitable place in another ("Drag and Drop" mode).

Microsoft Office applications allow the exchange of any data between any applications. For example, you can use charts from Microsoft Excel to design reports in Microsoft Word or to illustrate a presentation in Microsoft PowerPoint. You can place a piece of a table from Excel into the text of a Word document or into a PowerPoint slide, or you can import it into a Microsoft Access database. In addition, any Microsoft Office application allows you to put a picture in any format, as well as a video clip or just a sound clip into a document (whether it be text, a spreadsheet, a presentation or a database).

When transferring individual objects from one application to another, in addition to the usual copy / paste operation, it is possible to use the mechanism of linking the original object and its copy. In this case, when you change the source data in the Excel table, the chart built on their basis changes, and with it the copy of the chart that is contained in the Microsoft Word document. This makes it possible to work efficiently with compound documents that use different sources. For example, data can be taken from an Access database. They will be updated automatically and anyone who wants to read such a document will see the most recent numbers.

The Microsoft Office family contains a set of tools common to all applications. These include spelling and grammar checkers, a drawing tool, a colorful heading tool, an org chart editor, a math formula editor, a photo editor, a picture library, etc. This includes toolbars and even menus, which are standard elements of any Microsoft Office application.

These tools are installed on disk once and then used by all Microsoft Office applications. This feature allows you to save disk space, provide high-speed applications and a unified interface. You can easily get started with any Microsoft Office application by learning just one.

Starting with version 97, Microsoft Office includes a new application, Microsoft Outlook. This application is a tool for organizing and planning personal activities. Microsoft Outlook includes email, contacts database, calendar, etc. But the main thing is that it connects all applications, acting as a means of organizing work with them. Using Microsoft Outlook, you can set up an appointment by adding an accompanying document to the description. This could be a list of topics to be discussed in Microsoft Word format, or a year-end sales report in Microsoft Excel format. You can send the completed document by e-mail or as a fax message.

- Theoretical part:

MICROSOFT WORD

General provisions

Microsoft Word is a multifunctional word processing system. Since the Word word processor works in the Windows environment, its exceptional user-friendliness is in the first place among its advantages.

Management of all menu items and commands can be performed both with the mouse and with the help of hot keys (Short-Cuts), to save the user's time to perform a certain set of operations. The WYSIWYG (Wat You See Is What You Get) view lets you view a print-ready document on the screen without wasting time and paper on printing test pages. Character formatting, such as italic or bold, has adequate representation on the screen. And this is true for any headsets and pins.

The Microsoft Word editor as well as Microsoft Words are developed by the same corporation. This allows for maximum consistency of these systems with each other, as well as with other programs compatible with Microsoft Windows. Texts and illustrations in many formats can be imported into Word directly from other programs and embedded in the text of the document. As a result of this procedure, they become part of the WinWord text file or continue to exist separately due to the mechanism of objective linking and embedding (Objekt Linking And Embeding). There are also a number of well-established features, such as the ability to print some pages without interrupting the work with the document. In this case, the spooled print manager takes over the monitoring of the print in the background. Letters and faxes, notes and reports, publications and web pages - this is not a complete list of documents with which the user has the opportunity to work using the Word word processor.

Automate tasks and get help

Word has a wide range of automation tools that make common tasks easier.

Moving around a large document

Moving around the document is performed with the mouse, using the scroll bars, and with the keyboard, using the following keyboard shortcuts:

Ctrl + ® - one word to the right, Ctrl + ¬ - one word to the left,

Ctrl + ¯ - to the next paragraph, Ctrl + - to the previous paragraph, page,

Ctrl + PgDown - screen down, Ctrl + PgUp - to the beginning of the next page,

Ctrl + Home - to the beginning of the document, Ctrl + End - to the end of the document,

Alt + Ctrl + PgUp - to the beginning of the screen, Alt + Ctrl + PgDown - to the end of the screen.

Copying text

Ways to copy text:

1 way:

Select a piece of text;

use the commands Cut, Copy, Paste in the Edit menu;

2 way:

select a fragment;

press Ctrl when moving, Ctrl + Shift when copying;

place the cursor at the insertion point, press the right mouse button;

3 way(using keyboard):

select a fragment;

press F2 when moving, Shift + F2 when copying;

move to the insertion point with the cursor keys, press Enter;

4 way:

select a fragment;

drag the mouse to the desired location while holding down the left key, while copying, hold down the Ctrl key.

Saving a document while working. Using document properties

Ways to save a document:

The editor allows you to save documents in the formats: dot (templates), rtf (formatted text), txt (text without formatting), HTML (text with hyperlinks), MS-DOS, doc (previous version of Word 6.0/95), xls (spreadsheets), etc.

AutoCorrect

The following are some types of errors that can be corrected automatically as you type:

· The consequences of accidentally pressing the CAPS LOCK key (first appeared in Word 95). For example, at the beginning of a sentence, the word THOT is automatically replaced by This

And the capitalization mode (corresponding to the pressed CAPS LOCK key) is automatically turned off.

Typical misspellings in phrases. For example, the words postal address are automatically replaced with the words postal address.

· Grammatically incompatible pairs of words. For example, the words this word are automatically replaced by the words this word.

Autoformat as you type

Word has the ability to automatically format text as you type.

· Automatic creation of numbered and bulleted lists (first appeared in Word 95). For example, if you enter an asterisk at the beginning of the first element of a list, a bulleted list will be created.

· Automatic creation of borders (first appeared in Word 95). For example, if you type three or more hyphens (-) or equal signs (=) in a row and then press ENTER, a single or double border will be automatically created.

· Automatic assignment of built-in styles to text (first appeared in Word 95). For example, if you end a line of text not with a punctuation mark, but by pressing the ENTER key twice, it will automatically be assigned the Heading 1 style.

· Automatic formatting of serial numbers and fractions (first introduced in Word 95; applicable to English text only). For example, the combination "1st" is automatically replaced by the combination, and the fraction "1/4" is automatically replaced by the symbol ј.

· Automatic design of network paths and Internet addresses (for example, \\Reports\May\Week1.doc and http://www.microsoft.com/) as hyperlinks. Selecting a hyperlink to an Internet address automatically launches your existing Web browser and opens the specified Web page. Selecting a hyperlink to a network path automatically launches the program required to open the destination file and opens the specified file.

· Automatically apply the formatting used to decorate the beginning of the first element of a list to the beginning of subsequent elements in that list. For example:

1. Word will do all the work for you.

2. Word will make your documents beautiful.

· Automatic character change. For example, text *Bold* is automatically converted to Bold and _Italic_ to Italic.

· Automatic conversion of a sequence of pluses and hyphens (+----+----+) into a table. Each pair of pluses (+) corresponds to one column.

· Automatic replacement of spaces at the beginning of an element of a bulleted or numbered list with the corresponding left indent.

Autocomplete

Automatically suggest the full version of a word or phrase after typing the first few letters. To accept the suggested option, press the ENTER key. The following are some of the items that autocomplete applies to:

· The current date;

· days of the week;

Autotext list items.

abstract

In Word, it became possible to perform statistical and linguistic analysis of a document in order to highlight its key provisions. Based on this analysis, an abstract is compiled. In this way, you can highlight key provisions in an electronic document and view it with varying degrees of detail, or place an automatically compiled abstract in a separate document. The size of the abstract is set by the user and may vary.

When compiling abstracts, you must comply with copyright laws. The responsibility for the accuracy of the abstract lies not with the program, but with the user.

Automatic creation and preview of styles

Use the following new features when working with styles in Word:

· When new formatting is applied to text, a new style is automatically created.

· If you change the text formatting, the appropriate changes are automatically made to the style definition.

· The list of styles in the formatting pane contains sample styles.

Letter Master

The Letter Wizard allows you to easily and quickly create the desired letter by selecting predefined letter elements. In addition, once the information about the addressee (name, surname, address, position, etc.) is entered, it is automatically saved. When creating the next letter, it will be enough for this addressee to select his name from the list; all other data will be added automatically.

Assistant

The new Office tool, the Assistant, uses IntelliSense™ technology. Its main purpose is to automatically provide tips and reference information that may be needed as the task progresses. For example, if the assistant decides that you are going to start creating a letter, it will offer to start the letter wizard. The Assistant is your primary means of getting help with working in Word, as well as tips for improving your work efficiency, visual examples, and step-by-step instructions for performing specific tasks.

Spell check

The following are the main spell checking and text editing tools used in Word.

1. Check spelling as you type

Checking for spelling errors as you type and highlighting possible errors right in the document (first appeared in Word 95).

2. Check grammar as you type

Checking for grammatical errors as you type and highlighting possible errors right in the document. Like automatic spell checking, grammar checking automatically suggests possible correct spellings. To display a list of options, right-click the selected text.

3. Skipping certain elements of text when checking spelling

· At the request of the user, the spell check skips words consisting of uppercase letters and / or containing numbers.

· Skipping filenames and Internet addresses.

4. Grammar check

Highlighting errors and automatically suggesting correct spellings can significantly reduce editing time. Compared to previous versions, parsing has been improved, spelling suggestions have become more accurate, and the set of grammatical and stylistic rules has been expanded.

5. Joint spelling and grammar checking

The text is simultaneously searched for both spelling and grammatical errors. All errors are eliminated in one test session.

Tables, borders and shading

Word has added the following tools to make it easier to work with tables, borders, and shading.

tables

1. Drawing tables

Allows you to create tables - columns, rows and cells - by drawing them with the mouse, like a pencil. Now individual table cells can have any width and height.

2. Eraser

Allows you to easily remove the border of any cell, row, or column in a table, giving the same effect as merging cells. In previous versions of Word, you could only merge cells that were on the same row. In Word 97, you can merge any adjacent cells - both vertically and horizontally.

3. Vertical alignment

Allows you to quickly align the contents of selected table cells to the top or bottom, or to the middle of the cell, using the alignment buttons located on the toolbar. If the text in the cells is oriented vertically, the purpose of these buttons changes automatically: use them to align to the top or bottom, or to the center of the cell.

4. Vertical text orientation

Allows you to position vertically (rotated by 90 degrees) text in table cells, text boxes, and frames. This tool can be very useful when creating labels, as well as printed documents containing copyright information, etc., arranged vertically.

5. Resizing a table row

Allows you to adjust the height of any row directly in the table by dragging the row border up or down, similar to how you can change the width of a column. If you hold down the ALT key while dragging, the vertical ruler will show the actual row height.

Borders

1. New types of borders

There are more than 150 different types of borders for documenting, including 3D borders and multi-line borders, which are especially popular in professional documents.

2. Page borders

Allows you to create a border around each page. In addition to 150 new border types, there are over 160 types of graphical page borders. It is also possible to customize page borders. For example, you can:

· set your own border type for each edge of the page;

place headers and footers inside the border;

· design the first page differently from the rest.

3. Text borders

Allows you to apply borders to individual words and characters within a paragraph. In this case, you can use any available types of borders. For example, this makes it much easier to create borders around headings.

fill

1. Text fill

Allows you to highlight the most important fragments of the document, to which you should draw the attention of other users. In Word, the fill can be applied not only to the entire paragraph, but also to individual words and characters within the paragraph. In this case, you can use any available fill types.

Collaboration with multiple users

Word has new features and tools that improve the productivity and efficiency of workgroup members.

1. Versioning

Keeping a history of working on a document. This makes it possible to accurately determine the time, nature and author of the changes made to the document. In addition, arbitrary notes can be added to each version of the document. All document versioning information is stored in the document itself, eliminating the need to keep multiple copies of the document. In addition, you can set the mode of automatic saving of the current version of the document at the end of each session of working with it. This allows you to identify the author of any version of a document.

2. Merging documents

Combining all the changes and comments made by multiple reviewers into one document with one simple operation. Several reviewers can be given separate copies of the document for review, after which all corrected copies are combined into the final document. Or, for example, you can take a copy of the document home, correct it there, and then return the corrected copy and automatically merge your changes with the original document.

3. Notes and tooltips

Quickly view on-screen comments of any reviewers directly in the document without opening a separate area. Annotations are easy to spot in a document - text with an annotation is highlighted in yellow. When you place the pointer on this text, a tooltip appears above it containing the text of the note and the name of the reviewer.

4. Review Panel

Now all the usual document review tools are on the same toolbar: recording and viewing changes, inserting and viewing comments, highlighting text, saving versions, and sending a document by email.

Multi-language support

Word 95 introduced automatic font and language switching when switching keyboards, making it possible to use text in multiple languages in a document and some dialog boxes. Word 97 makes it even easier to create and view documents containing text in various European languages. In addition, you can view documents created with Far Eastern versions of Word in the US version of Word.

1. Keyboard switching

Automatic switching of fonts in the document when switching the keyboard (the set of fonts used is determined by the current keyboard layout). Switching the keyboard also causes a language switch, and therefore provides verification of text in another language, such as Greek, by means of checking that particular language.

2. Support for texts in several languages in dialog boxes

It became possible to edit and view text in several languages in localized versions of Word. For example, you can specify the Greek name of the author as a search term for files in the Find Files dialog box.

3. Opening documents with text in multiple languages in a localized version of Word

Correct display of the text of a document created in a localized version of Word, if the system has the appropriate fonts. For example, documents created in the Japanese version of Word can be opened and viewed in the US version of Word. It should be noted that this possibility does not apply to languages in which the direction of writing can change (Hebrew, Arabic, etc.).

4. Copy and paste Unicode text

It is possible to copy text in Greek, Russian or any other European language from the American version of Word and paste it into a Microsoft Excel worksheet. In addition, you can copy and paste text in Unicode format into the fields of some dialog boxes (Find, Replace, etc.).

5. Sorting

It is possible to specify the language, and thus the sort order, to be used when sorting text in a document. For example, Hungarian double letters such as "cz" are sorted correctly in the American version of Word when Hungarian is specified as the sort language.

6. Insert symbols

To be able to insert characters and letters used in a particular language, simply select the appropriate Unicode font character set in the Symbol dialog box (Insert menu). For example, to insert Russian letters into the text, you should select the "Cyrillic" set.

MICROSOFT EXCEL

File Management

With Excel, you can create a variety of documents. Worksheets can be used for tabulation, statistical evaluation, database management, and charting. For each of these applications, Excel can create a separate document that is saved to disk as a file.

The file may contain several interconnected worksheets that form a single multidimensional document (notebook, working folder). With the help of multidimensional documents, the user gets direct access to several tables and charts at the same time, which increases the efficiency of their processing.

It is possible to set the autosave mode for files at certain intervals, the backup mode and the data protection mode (saving the document with a password).

Document structure

Multidimensional documents (notebooks, folders) are one of the most important innovations in Excel, which greatly simplifies and speeds up work with tables and worksheet management. In the new version, working documents can contain up to 255 tables, diagrams or VBA programs in one file, and the principle of working with them resembles the usual work with business notebooks in any office. Each working notebook can contain all business information related to one topic and store it in one file, which can greatly increase the visibility of working documents.

Separate worksheets of one document are located under each other. Using the name index (case of names) located at the bottom of the screen, you can move from one sheet to another. On the name index are the roots of the worksheets, arranged in ascending order of numbers: Sheet1, Sheet2, etc.

The user can add elements of the following types to the document:

Worksheets for creating tables;

Charts (as a table element or on a separate sheet);

Worksheet for writing a macro as a program module;

Worksheet for creating a dialog box.

The new sheet is always inserted before the active worksheet. The user can right-click on the worksheet title, which will open a contextual (situational) menu that also has an add directive. If you want to delete a worksheet, you need to right-click on the context menu and execute the delete directive.

Row Height and Column Width Adjustment

First of all, the corrected row or column of the table must be marked. To do this, left-click on the number (coordinate) of the row or column. On the menu Format there are submenus Line And Column. Selecting one of these submenus opens the next level menu. In the submenu Column call the directive Width, then a dialog box will open where the user can specify the column width. You can use one character as the unit of measure. Team AutoFit Width from the menu Column automatically adjusts the column width to fit its content.

Correction can also be performed using the mouse. To do this, the mouse pointer must be placed on the border between row numbers or column addresses.

Table cells and their properties

All table data are recorded in the so-called cells, which are at the intersection of the rows and columns of the table. By default, the contents of a cell are presented by Excel in a standard format that is set when the program starts. For example, for numbers and texts, a specific type and size of the font is set.

The Excel program has context menus, which are called by the right mouse button when some area of the table is marked. These menus contain many table processing and formatting directives. Formatting directives can also be called up on the formatting panel (second line of the icon menu) by clicking on the corresponding icon.

Cell labeling. Individual table cells are marked (highlighted) automatically using the cell pointer. To move the pointer to a given cell, you need to click on it with the left mouse button or use the cursor keys. To mark several cells, click at the beginning of the marked area (upper left corner) and, holding the mouse button down, move the manipulator to the end of the area (lower right corner).

One row or column of the table is marked by clicking on the number (address) located in the header of the row or column. To mark several rows or columns, you need to click on the number of the first row (column), and then, holding the mouse button down, move the manipulator to the desired position.

Data copying. First of all, you need to mark a group of cells whose contents should be copied, and then call the command Copy from the menu Edit. After that, the marked area will be framed. Now you should move the mouse pointer to the position where the data should be copied and press the enter key Enter. The contents of the bulleted area will appear in the new location. If you need to delete the contents of a group of cells and place it in a new place in the table, then you should use the directive Cut from the menu Edit .

Deleting data. First of all, you need to mark a group of cells whose contents should be deleted. Then you can use one of the ways to delete data.

Number formatting. First of all, you need to mark the cells in which you need to change the format for representing numbers. After that, you can either open the context menu with the right mouse button and call the directive in it Cell Format, or call the directive Cell from the menu Format. In either case, a dialog box will appear on the screen. Format cells. By default, the option will be selected in the top field of this window. Number, which is used to format numbers.

Data alignment. To align the contents of the cells, first of all, you need to mark the aligned cells. Next, call the dialog: Format - Cells - and in the upper field of this window select the tab Alignment.

Installing fonts. It is necessary to mark the cells in which you want to change the font. After that, you can either open the context menu with the right mouse button and call the directive Cell Format. In the upper field of the window that opens Cell Format you need to select an option Font. A box will appear in the window. Font, in which you can select the type of font from the proposed list. The content of this list depends on the settings made in the Windows environment.

In the right field Size set the size (point) of the font. In the list of font styles inscription You can choose regular style (Regular), italic (Italic), bold (Bold) and bold italic (Bold Italic). In field underlining you can choose the type of text underlining (one or two lines), etc.

Table computing

Usability formulas and functions is one of the most important features of a spreadsheet program. This, in particular, allows statistical analysis of the numerical values in the table.

The text of a formula that is entered into a table cell must begin with an equal sign (=), so that Excel can distinguish the formula from the text. After the equal sign, a mathematical expression containing arguments, arithmetic operations and functions is written to the cell.

As arguments in the formula are usually used numbers And cell addresses. The following symbols can be used to denote arithmetic operations: + (addition), - (subtraction), * (multiplication), / (division).

Entering formulas. Excel interprets the data you enter either as text (left-aligned) or as a numeric value (right-aligned). To enter a formula, you must enter an algebraic expression that must be preceded by an equal sign (=).

Suppose that in cell A1 of the table there is the number 100, and in cell B1 - the number 20. To divide the first number by the second and place the result in cell C1, enter the appropriate formula in cell C1 ( =A1/B1) and press Enter .

Entering formulas can be greatly simplified with a little trick. After entering the equal sign, simply click on the first cell, then enter the division operation and click on the second cell.

Editing formulas. To start editing the contents of a cell, you must first mark that cell. In the next step, you need to enable the editing mode by pressing the key F2 or by double-clicking. In edit mode, a type-setting line is activated at the top of the screen (under the icon menu bar), in which the formula itself is visible, and not the result of its calculation.

Information links. In Excel, it is quite easy to enter a reference to a cell located in another table. Once the link is established, the values in the cells will be automatically updated.

To refer to the value of a cell located on another worksheet, you must specify the name of this sheet along with the address of the corresponding cell. For example, to access cell B3 on the worksheet Sheet2, enter the formula: =Sheet2!B3. Cell addresses must be indicated in Latin letters.

Graphic charts enliven the dry columns of numbers in the table, so the ability to build charts was already provided in the early versions of Excel. Excel includes a new Chart Builder, which allows you to create "presentation quality" charts.

A beautiful 3D chart is not only pleasing to the eye, but also improves the quality of the document. A new type of 3D charts has appeared in MS Excel - the so-called donut charts, which complement the surface and grid 3D charts of the fourth version.

The chart can be placed next to the table or placed on a separate worksheet. The Chart Builder is one of the most powerful tools in the Excel program. Building a diagram with its help is carried out in several steps. The designer is given the source area of the table, the type of chart, the labels and colors used. The main panel has an icon to call the Chart Builder.

Construction of diagrams. Before building a chart, you must complete all the work in the table, including its formatting. Call the Chart Builder (see Figure 11). You must use the mouse pointer to draw a rectangle on the free space of the worksheet where the diagram will be placed.

After that, the Designer opens a dialog box in which you need to build a chart in four steps. Working with the Constructor is so intuitive that it does not require any explanation.

Additional objects. On the menu Insert there are directives that allow you to give the diagram a more attractive look. In particular, there is a directive here Drawing, which imports pictures in standard graphic formats (BMP, PCX, etc.). After calling this directive, a window for loading a graphic file opens. The selected figure appears in the diagram, enclosed in a frame. This frame can be moved around the chart with the mouse and resized.

In addition, the Chart Builder provides the ability to insert additional text into the chart, which will draw attention to certain parts of the chart. To insert text, you need to enter it from the keyboard into the dialing line and press the enter key Enter. Then a text frame will appear in the center of the diagram, which can be given an appropriate size with the mouse, and then moved to the desired location on the diagram.

Diagram update. If you make changes to that part of the table on which the chart was built, Excel will automatically modify the chart. If you have added a new row or column to the table, then proceed as follows. Mark the new data in the table and transfer it with the mouse to the diagram. To do this, place the mouse pointer on the border of the marked area and, without releasing the left mouse button, move it to the diagram. As soon as you release the mouse button, the diagram will be changed (updated). This technique is called by Microsoft Drag & Drop .

Formulas are required to perform spreadsheet calculations. Since some formulas and their combinations are very common, Excel offers more than 200 pre-programmed formulas, which are called functions .

All functions are divided into categories to make them easier to navigate. built-in Function constructor helps at all stages of work to correctly apply the functions. It allows you to build and calculate most functions in two steps.

The program has an alphabetically ordered complete list of all functions, in which you can easily find the function if you know its name; otherwise, search by category. Many of the functions differ very little, so when searching by category, it's helpful to use the short descriptions of the functions that the Function Builder offers. The function operates on some data, which are called its arguments. A function argument can occupy one cell or be placed in a whole group of cells. The function constructor provides assistance in specifying any types of arguments.

Function constructor. Mark the cell in which the result of the calculation should appear. Then, by clicking on the Function Builder icon, open the Builder dialog box. The function categories are listed in the left field of this window, and the functions corresponding to the selected category are listed in the right field. To see all functions, click on the option All in the categories field.

Click on the name of the function you need, then the name of the function will appear in a separate line along with a brief description of this function. The types of function arguments and their number are also indicated here. Arguments are set at the next step of working with the Constructor. To access it, click on the command button ABOUT K .

The second step is to specify the function arguments in the Constructor dialog. The constructor distinguishes between arguments that must be taken into account, and optional (optional) arguments. To set a function argument, you must either enter its address from the keyboard, or mark the area where it is located in the table. Then the address of the function argument will appear in the corresponding field of the Constructor dialog box. Click the command button OK to close the Function Builder window. After that, the calculation result will appear in the specified cell of the table, but the cell will remain marked. In the typeset line, you can see the selected function along with the corresponding arguments.

Autosum. Mark the cell where the sum should be located, and click on the addition icon.

An equals sign and the word "SUM" will appear in the typed line, usually followed in parentheses by the address of some area that the program proposes, after analyzing nearby cells, as the summation area. If Excel "guessed" correctly, click on the checkmark icon.

Otherwise, mark the group of cells to be folded. You can mark several non-contiguous groups of cells using the key ctrl. This key must be kept pressed until all groups are marked. Group addresses can be entered using the keyboard.

Data exchange

Data Exchange allows an Excel user to import objects from other applications into their spreadsheets and transfer (export) their spreadsheets for embedding in other objects.

The concept of communication is one of the core concepts of the Windows environment. Information links are created between objects that are processed by various application programs, for example, between tables and texts. These information links are implemented dynamically, for example, a copy of the table embedded in the text will be updated (actualized) whenever changes are made to its original.

List Processing

Lists allow you to work effectively with large, homogeneous datasets.

A list is an ordered set of data that has the same structure. For example, a list is a telephone directory, which contains the names of subscribers and their phone numbers. In Excel, developers from Microsoft have greatly simplified the work with lists. From a technical point of view, lists are ordinary tables for which Excel has additional processing operations: entering a list of data, searching for an element in a list, editing lists, filtering data, sorting data, analyzing data (searching for trends and patterns)

Database

Excel is supplied with a program for processing external databases MS Query. The Query program (Request) can work with text files and with data that has been prepared by the following database management programs: Ms Access, dBASE, FoxPro, Oracle, Paradox, SQL Server.

If, while working with a table in Excel, it becomes necessary to access an external database, you need to run the Query program. This will allow you to import data from the database and process it like regular tabular data in Excel. In some cases, external data can be processed directly by Query.

After loading a dataset, you can select a portion of the records from it by including data selection criteria.

Program configuration

Excel can be customized to meet the individual needs of a very wide range of users. Each user of the Excel program, having determined the circle of the most frequently used functions, can organize work with them in the most convenient way for himself. On the other hand, those functions of the program that are never used can be removed from the configuration altogether in order to save computer resources and increase processing efficiency. For example, each user of the program can create an icon menu, change the appearance of the main window, change the appearance of the worksheet, install the desired fonts, create table templates, etc.

MICROSOFT ACCESS

A few words about...

Microsoft Access is an interactive relational DBMS( relational database management system - RDBMS) For WINDOWS . This is a program that you can use to store and retrieve data depending on the relationships you have established. Working with it is simplified by means of the mouse manipulator. The shell's graphical capabilities make a big impression when producing high-quality reports and printouts. All this thanks to the support true-type fonts and embeds OLE objects (Object Linking and Embedding) within the environment WINDOWS . OLE - object is a link to certain information that remains in its original form. OLE object May be EXCEL - spreadsheet , Paintbrush - illustration or Sound - file.

Program novelty Access is Cue Cards - Prompting system. This is a training system that provides the user with recommendations on how to get out of real situations when solving applied problems. With knowledge of English, the user can always get a hint about what to do next.

Access also provides the user with mechanisms for working with databases of various formats. For example, you can directly access databases dBASE , Paradox or Btrieve without converting them to the format used Access . Included in the package Access also includes language Access Basic (built-in dialect of the Visual Basic language), which makes it possible to form specialized database management systems.

Database it is a set of data that is associated with a specific topic or purpose. When you create a database in Microsoft Access, you create one file that contains all the tables, queries, forms, reports, and other objects that help you use the information in the database.

tables

TABLE - this is a set of records (table rows) consisting of separate fields (table columns) that store information and make up, in fact, the content of the database.

Tables are the main form of presentation of information contained in a database. Without a table, it is impossible to design a form; queries and reports are compiled on the basis of tables. There are two ways to create a table:

1. Using Table Designer .

2. Without help Constructor .

Requests

You can think of a query as looking at the data included in a table. Queries are used to select and filter a data set. They allow you to select only the necessary information from the database, i.e. one that meets a certain criterion (condition) and is needed to solve a specific problem. For example, you may need information about suppliers and the products they supply. You can create a query like “What goods are supplied by Moscow suppliers” or “Which of the St. Petersburg cigarette suppliers sold the largest batch in the last quarter”. The result of processing by the program Access such a query is a table called Dynaset . This table includes data blocks selected from the main table (or several tables) that satisfy the query criteria. Dynaset - a dynamic, temporary data set, therefore, each time the query is executed, it is rebuilt based on “fresh” tabular data.

Microsoft Access gives great opportunities in query design flexibility. You can:

· Select fields You don't have to include all the fields in the table in the query.

· Select records, you can specify the query in such a way that you will receive data of a certain type. For example, the prices of goods for a certain month.

· Sort records, You can view the records in a specific order. For example, you can display customer names in alphabetical order.

· Query data from multiple tables, data from multiple tables can be processed together and you can view the combined data. You can also query data from other databases such as Microsoft FoxPro , Paradox , dBASE , Btrieve and Microsoft or Sybase SQL Servers .

· Perform calculations, You can create new fields that will contain the results of calculations.

· Use a query as a data source for forms, reports, and other queries. Each time you open a form or print a report, your query will retrieve the latest information from the tables. You will also be able to modify existing data directly in the request-based form.

· Modify data in tables, you can update, delete, add groups of records all at once. You can also create a new table based on an existing table or group of tables.

There are two types of requests:

1. QBE queries (Query by Example). The user defines them by specifying individual parameters in the design window using hints (patterns).

2. SQL queries (Structured Query Language - Structured Query Language). The user formulates them using instructions and functions, building a description. QBE query Access easily translates into the appropriate SQL query . The reverse operation is also not difficult. In general, Access doesn't care what type of query the user is working with.

Queries can be created using the Query Builder. It speeds up the design of several special types of queries.

Forms

Viewing the database as a table in fill mode allows the user to evaluate the database as a whole, compare records, and so on. Often, however, there is a need to work with individual database records. In this case, the presence of other records on the screen (as is the case in fill mode) only interferes and distracts. Working with individual records through forms allows you to focus only on relevant information.

A form is a blank to be filled in or a mask to be applied to a data set. The blank form allows you to simplify the process of filling in the database, which makes it possible to entrust the entry of information to low-skilled personnel. The form mask allows you to limit the amount of information available to the user accessing the database. It can also be about blocking the indication of service or secret fields.

Reports

Report it is information that you have formatted according to your specifications. The report allows you to extract and present data as meaningful information that you can use and share. Examples of reports are postal addresses, invoices, sales amounts, or phone lists. By using Microsoft Access You can design a report that will present the information the way you want. You can use many different design elements such as text, data, pictures, lines, fields, and graphics to create your report. It is up to you how and which elements to use to build your report.

Microsoft Access provides several ways to get information from your database - using a query, form or report. You can choose the method that best suits your task.

For example:

· To view all products of a specific sales shipment, use the query.

· To view complete information about only one product, use the form.

· To print out trade sales for formal presentation, use the report.

Reports are also an effective tool for printing information of regular use. You can create a draft report and save it for later use. The draft report will remain unchanged, but each time you will receive a printout of the current data.

Macros, macros

macro is a sequence of operations written as instructions in a special language. When processing data, you often have to do long sequences of actions. But any actions, operations can be written as macros. Calling macros leads to the execution of a sequence of previously described actions. The use of macros allows you to automate various repetitive processes, such as filling the Base, executing queries, opening various objects, etc.

The sequence of operations is entered by the user into the macro command design window.

Modules

When solving fairly complex problems, you will soon encounter the limitations of macros. To overcome the problems that have arisen, you can try to write your program on the built-in Access programming language - Access Basic . It is a visual programming tool and is a dialect of the language Visual Basic. This procedure is formatted as Module .

Module is a set of declarations (declarations) and sequences of executable commands (procedures, functions) written in the language Access Basic .

- Conclusion

Naturally, within the framework of this course there is no hope of fully highlighting the possibilities of such multifaceted programs, since the full Microsoft Office package is very diverse. In order to know "Microsoft Office" you need to work in it. Using this package, you will surely discover more and more new features and properties. Explore and experiment. If you don't like the results, try again. Almost everything you do in Microsoft Office can be undone, so you won't lose anything but a few minutes of your time by experimenting.

- Practical part:

General characteristics of the task

The company LLC "Initiative" carries out activities related to construction work and the sale of building materials. Prices for goods and services are presented in (Table 1), data on firms whose orders must be completed in September 2001 - (Table 2).

Using the PPP on a PC, you must:

Build tables according to the table data below;

Organize inter-table links for automatic filling of the columns: “Company name” and automatic calculation of the amount of orders of each customer company in the designed documents;

Present the results of the calculations in the form of a table containing the monthly totals for each customer company and for the company LLC "Initiative", and in graphical form;

This task is solved in the spreadsheet MS Excel, as it is a means of preparing and processing tabularly organized electronic data. Excel performs mathematical and trigonometric functions, including summation, square root, product, logarithmic expressions, etc. Also, this spreadsheet is designed for plotting charts.

Designing forms of output documents and graphical representation of data for the selected task

Location of tables with source data on MS Excel worksheets: "Goods" (Table 1) and "Customers" (Table 2);

Table with final data on firms (presented in the form of formulas) (Table 3);

Graphic table in the form of "Charts" (Table 4);

tab. 1

tab. 2

date	Company name	Company code	Order code	Name of product
	LOOKUP(C2;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F2;Products!$A$2:$A$5;Products!$B$2:$B$5)
	LOOKUP(C3;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F3;Products!$A$2:$A$5;Products!$B$2:$B$5)
	Medic Outcome
	LOOKUP(C5;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F5;Products!$A$2:$A$5;Products!$B$2:$B$5)
	LOOKUP(C6;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F6;Products!$A$2:$A$5;Products!$B$2:$B$5)
	LOOKUP(C7;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F7;Products!$A$2:$A$5;Products!$B$2:$B$5)
	Private Total
	LOOKUP(C9;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F9;Products!$A$2:$A$5;Products!$B$2:$B$5)
	Factorial Total
	LOOKUP(C11;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F11;Products!$A$2:$A$5;Products!$B$2:$B$5)
	LOOKUP(C12;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F12;Products!$A$2:$A$5;Products!$B$2:$B$5)
	Project - M Outcome
	LOOKUP(C14;Customers!$A$2:$A$6;Customers!$B$2:$B$6)			VIEW(F14;Products!$A$2:$A$5;Products!$B$2:$B$5)
	Service Total
	Grand total

tab. 3

continuation of the table. 3

Product code	Quantity	Amount, rub.
		VIEW(F2;Products!$A$2:$A$5;Products!$E$2:$E$5*G2)
		VIEW(F3;Products!$A$2:$A$5;Products!$E$2:$E$5*G3)
		INTERIM.TOTALS(9;H2:H3)
		VIEW(F5;Products!$A$2:$A$5;Products!$E$2:$E$5*G5)
		LOOKUP(F6;Products!$A$2:$A$5;Products!$E$2:$E$5*G6)
		VIEW(F7;Products!$A$2:$A$5;Products!$E$2:$E$5*G7)
		INTERIM.TOTALS(9;H5:H7)
		LOOKUP(F9;Products!$A$2:$A$5;Products!$E$2:$E$5*G9)
		INTERIM.TOTALS(9;H9:H9)
		LOOKUP(F11;Products!$A$2:$A$5;Products!$E$2:$E$5*G11)
		LOOKUP(F12;Products!$A$2:$A$5;Products!$E$2:$E$5*G12)
		INTERIM.TOTALS(9;H11:H12)
		LOOKUP(F14;Products!$A$2:$A$5;Products!$E$2:$E$5*G14)
		INTERIM.TOTALS(9;H14:H14)
		INTERIM.TOTALS(9;H2:H14)

tab. 4

User's Manual

Excel is part of the Microsoft Office suite and is designed to prepare and process spreadsheets under Windows.

In order to start the Excel program, you need to press START, then PROGRAMS and MICROSOFT EXCEL. A blank tabular sheet appears, we begin to enter data into the table. The table consists of 16384 lines and 256 columns. Rows are numbered with integers from 1 to 16384, and columns are marked with Latin letters A, B, …, Z, AA, AB, …, IV. At the intersection of the column and row is the main structural element of the table - cell. In any one, you can enter the initial data - a number or text - as well as a formula for calculating derived information. Column width and row height can be changed.

This application program works in the MS Excel environment. To enter new calculated data, select File - Create, specifying the template "Statement of calculation of the cost of products, taking into account the discount."

This template has a limit of 12 calculated data positions. To add a new line, copy the last line. Thus, the size of the borders is limited to only 32000 lines

To print the chart, you need to adjust the range of values depending on the amount of data entered. To set the values, you need to go to the chart sheet, enter the menu Diagram and select item Initial data. Depending on the amount of data entered, set the number of the last line in the value and label fields of the X axis.

- List of used literature:

1. A.P. Miklyaev “Handbook of the IBM PC user”.

2. Kenin et al. “Using MS Office”.

3. "How to work with Microsoft office » // Microsoft Press , official publication, 1999

4. Application programs office .

5. Program Help Microsoft excel .

6. V. Pasko - excel

7. Bemer S., Frater G.: Microsoft Access for the user .

When working with Excel, sometimes you have to adjust the interaction of the program code with other applications of the office suite or outside of it, for example, with Internet Explorer. Connect so to speak. For example, let's take Word, although we will insert a little IE.

Let's start with the concept of late and early binding (binding).

Word Automation Server allows you to manage Word from other applications. Other office applications (and not only office ones) have the same server. In this case, the server will be Word, and the client is Excel. Requests will be sent from it to execute some code, and the Word Automation Server will process these requests. This theoretically means that you can write code in Excel that will run in Word and use Word's built-in functions. To use this feature, you must specify a link to the library, the functionality of which we are going to use. In this case Word. This can be done in two ways: specifying the library explicitly, before executing the program code, and during the execution of the program code.

First way called early binding. On the menu Tools-References select vba editor Microsoft Word 12.0 Object Library . In other versions of office suites, as you understand, .

With early binding, a new instance of the Word class will be created like this:

Dim WApp As Word.Application Set WApp = New Word.Application

Second way called late binding. In this case, there is no need to specify a link to the library. An instance of the class (object) is created and a reference to it is returned using the function CreateObject .

Syntax CreateObject:
CreateObject(class,[servername])

Class - required parameter. Data type Variant(String). The name of the application and the class from which the object is derived. Has the syntax appname.classname
servername- optional parameter. The name of the computer on the network where the object will be created. If the parameter is not specified (an empty string), the object will be created on the local machine.

Examples of object creation with late binding:

Creates a new Word.Application object

Dim WApp As Object Set WApp = CreateObject("Word.Application")

Creates a new Word.Application object and makes it visible.
Dim WApp As Object Set WApp = CreateObject("Word.Application") WApp.Visible = True

Creates an object on the remote machine. You must have the appropriate access rights for this operation.
Dim WApp As Object Set WApp = CreateObject("Word.Application", "192.168.1.4")

If there are several installed office products of different versions in the system, you can specify on the basis of which version the object will be created. However, if the specified version does not exist on the computer, an error will occur.

Dim WApp As Object Set WApp = CreateObject("Word.Application.12")

Notes: the generated Word.Application object is invisible. This speeds up work with it, but in case of an error, it remains in memory. Use WApp.Visible = True in your error handler. Each time you use the CreateObject function, a new object will be created.

If the application object has already been created, then you should not use the CreateObject function. Instead, it is advisable to use the GetObject function, which simply returns a reference to the object.

SyntaxGetObject:
GetObject([path][,class])

path- optional parameter. Data type Variant(String). Full path to the file associated with the object.
Class- Optional parameter. Data type Variant(String). A string indicating which type of object will be referenced. If the path parameter is not specified, that parameter is required.

Dim WApp As Object Set WApp = GetObject(, "Word.Application") Debug.Print WApp.Documents.Count

Dim WDoc As Object Set WDoc = GetObject(ThisWorkbook.Path & "\Pacienti.txt") Debug.Print WDoc.Sentences.Count

Here is an example with catching the error of the absence of an open file:
Dim WDoc As Object On Error GoTo cvv_Error Set WDoc = GetObject(ThisWorkbook.Path & "\Pacienti.txt") Debug.Print WDoc.Sentences.Count On Error GoTo 0 Exit Sub cvv_Error: If Err.Number = 432 Then MsgBox "File " & ThisWorkbook.Path & "\Pacienti.txt" & " is not open..."

Here is an example of late binding with Internet Explorer:
Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True

Well, for early linking with this browser, you need to link to the library Microsoft Internet Controls . Well, if you plan to work with Internet pages (parsing the structure and data), then Microsoft HTML Object Library .

Late binding has only one advantage: the link to the library will never fail (if the application is registered in the system). Use this method if you are not sure that the end user will have the same version of the office suite as yours. In any other case, it is better to use early binding, and there are two reasons for this:
1. The Application Object Model is available in the Excel Object Browser.
2. Performance does not suffer, and all functions work.

Instead of an afterword:
In this short article, I talked about ways to connect to the application object model. How to use it is a huge topic that requires separate articles. Of course, using applications without knowing their object model will not work. Use automation wisely, without unnecessary fantasies. I once saw an application in Excel that, in order to insert data from an Access table into a sheet, created an instance of Access, opened a database file, then copied the table from this instance to the sheet, then closed the instance. Although it was easier and faster to use data access using ADO or even DAO ...

Program for burning mp3 disc

Download mods minecraft 1

How to craft a spectral arrow in minecraft 1

How to make a bow and arrow in minecraft