Configuration: 1c accounting

Configuration version: 3.0.44.177

Publication date: 20.01.2017

The 1C: Accounting program now offers the ability to routinely upload and download data from Excel spreadsheet documents, without the use of third-party processing or configuration changes. Loading and unloading is so easy that experienced users will quickly fall in love with this convenient mechanism, and new users will quickly master it.

Download from Excel 1C: Accounting 8.3 ed. 3.0

For example, we have this Excel spreadsheet document for loading a list of items and prices (retail and purchasing).

Come to .

At the top click Nomenclature and Prices - Download. This is where loading and unloading from spreadsheet documents is hidden.

The download supports xls (old Excel 97-2003 format) and xlsx (new format), as well as mxl and ods formats. Now we select our file with the nomenclature and wait for it to load.

The spreadsheet document has been loaded in the form we saw it in Excel, now we’ll assign columns and delete the unnecessary ones.

Click on Please provide details and select the desired item from the list.

Select the row that contains unnecessary entries, namely the table header, and click Delete line.

Now we delete the column with the purchase price, note that processing can fill out only 1 type of price at a time!!! Then you can do this operation again and load the 2nd type of price, for the 1st load Retail for the 2nd purchase.

Fill in the blank data, Price type, Set prices for. The buttons All, Found, New, Duplicates will greatly facilitate the search and help compare items. Fill in and click Download after which the data will be loaded into 1C: Accounting.

New items have appeared in the product list, filled and with assigned prices. If you need to load another type of price, do all the steps again, but select a column with a different price, 1C itself will find already created positions and will not try to duplicate them, but will simply add a new type of price.

As you can see, the download created 2 documents for setting item prices, for the Purchase Price and for the Retail Price. The difference between this treatment is Loading from Excel to 1C: Accounting 8.3, The fact is that this is standard processing and it works without failures and takes into account all the features of filling out the document.

Uploading to Excel from 1C: Accounting 8.3 ed. 3.0

Very useful function in version 3.0.44.177 and higher is uploading to Excel from 1C: Accounting 8.3, this processing will help you create a company price list or transfer your list of items to suppliers or buyers immediately with prices.

It's all there in the directory Nomenclature - Nomenclature and Prices - Unload.

In point Columns, select the columns that need to be built in Excel document. Selection serves to select items by Groups, properties, names... etc.

To upload to Excel, we will add a new column, namely the VAT rate, it doesn’t seem to be very necessary, but we are practicing.

• When exchanging information between counterparties (price lists, reconciliation reports, etc.);
• To facilitate the work of operators in cases where the main accounting is carried out in 1C, and some of it is carried out in Excel tables;
• When filling the database for the first time.

To perform this operation you can use both typical functionality, available with a subscription to information technology support (ITS), and independently written processing, implemented through various connection options. In our article we will try to analyze as fully as possible all possible cases and answer most of the existing questions regarding loading data from Excel into 1C.

Universal mechanism

On the ITS disks, as well as on the 1C portal, by going to the “Technological Support” -> “Universal Reports and Processing” menu, the corresponding processing is located in the “Loading data from a spreadsheet document” folder.

When you launch it, the form opens (Fig. 1):

As you can see from the form, it allows you to transfer information to the following configuration objects:

1. Directory;
2. Tabular part document or reference book;
3. Register of information.

Depending on the position in which the switch is set, the object selection field changes.

The processing tabular form is filled in when the downloaded file is opened.

Typical processing supports downloads from:

• Developed by specialists from 1C, file formatmxl;
• Lisztxls saved in the formatExcel 97-2003;
• Text filetxt;
• Tablesdbf.

Excel files of large size can take quite a long time to load, so if it is assumed that data loading can occur in several stages or you know for sure that you will have to open an existing file several times, to save nerves and time, it is better to save the information from it in mxl format. This can be done directly from the processing form.

It is important to understand that if there are line groupings, subheadings and notes in the source file, they will need to be removed manually.

Now let's move on to the “Settings” tab (Fig. 2):

Fig.2

Quite often, Excel tables contain a header with all sorts of details and data (name of the printed form, details of the counterparty, date and number of the incoming document, column names, etc.), in order to prevent them from being processed by the program on the form, you must specify in the “First line of the spreadsheet document” the first line with the transferred information

After selecting a metadata object into which information will be recorded, the tabular part of the “Settings” tab will be automatically filled in with the names of details, descriptions of their types, and other important information. A separate section should be devoted to consideration of the columns of the tabular part of the “Settings” tab.

Columns "Settings"

Marking – by checking or unchecking a checkbox in a line, it is determined whether the corresponding attribute will be filled in.

Attribute representation – here the synonym (name) of the metadata attribute is written, as it is specified in the configurator.

Search field – if you check this field, processing will search for elements using the corresponding details and, if successful, change existing data, additional function This field is protection against duplicates.

Description of types – displays the data type that a particular metadata attribute has.

Boot mode – offers three options to choose from (Fig. 3):

Fig.3

• Search – a search will be carried out for the corresponding element; if it is missing, a new one can be created;
• Set – a certain value is subordinately set;
• Calculate – in this case in the field element being created the result of calculating the expression specified in the “Connection condition/Expression for value” column will be set.

In the latter case, activating the Expression field will open the form (Fig. 4).

Fig.4

Column number is a field used to indicate which column of the Excel table should be filled with data.

Default value – quite often a situation arises when the downloaded file does not contain all the data necessary to record an element; in this case, the information contained in this field will be filled in.

Link condition/Expression for a value – we have already partially touched on this field when we looked at calculated fields; in addition, you can specify a condition in accordance with which the source data will be synchronized.

This is, in principle, all the information that is available on the “Settings” tab.

In order not to waste a lot of time each time loading, writing correspondence and expressions, the developers have provided the ability to save the configuration option to a file with the mxlz extension.

You can check the correctness of the transferred data by clicking on the “Filling Control” button (Fig. 1). After this, you can start the download process. You will be notified separately about the successful completion of the procedure or unusual situations.

To load data from Excel into information databases“Management and Trade” there is another mechanism. It is less universal than the above method, but does not require an ITS subscription and is included in the standard delivery.

This processing can be found on the “Purchases” tab, in the “Service” menu, it is called “Loading supplier prices from files” (Fig. 5)

Fig.5

The processing form contains:

1. A date selection field that will indicate for what time this price is relevant;
2. Field for selecting the counterparty who sent their price list;
3. A button that allows you to select the type of prices to be set;
4. A tabular part that can be filled with downloaded data.

This shape can be seen in Fig. 6

Fig.6

The explanation at the top of the form explains how to use the first tab of the form.

After selecting a counterparty (depending on whether he is a supplier, commission agent or seller), additional columns for each type of price will become available in the table.

When working through the web interface, some browsers may require the installation of a browser add-on (Fig. 7). We need to click the “Start installation” button and restart our connection.

Fig.7

After this, using the clipboard, we will be able to transfer information from one table to another. When the columns we need (“Article”, “Name”, “Price”) are filled in, we click the “Next” button and go to the second page (Fig. 8)

Fig.8

The program will automatically search for matches within the database and, if none are found, offer options for eliminating the error. Appearance The tabular part can be controlled using a switch. In addition, the user can independently compare the elements of the downloaded file with the data available in the database.

• Register everything;
• Register only those that have changed compared to those already in the database.

In the text field you can enter a comment that will be recorded in the document (Fig. 9):

Fig.9

After processing:

• The corresponding element will be created in the “Supplier Nomenclature” directory (if it did not exist);
• The directory element “Nomenclature” will be associated with it;
• The document “Setting item prices” will be created and posted, indicating: supplier, type of prices and date of data recording.

The “Loading products from external files” processing works in a similar way.

DIY transfer processing options

The main problem in extracting data from an Excel file is that 1C does not have a built-in unambiguous mechanism for opening it. There are several options for connecting Excel to 1C:

• Via Microsoft ADODB – enough quick way, which, as a rule, is applicable for both file and client-server database storage options;
• Through Microsoft use Office - a method that sometimes fails when working with SQL databases, as a rule, it works somewhat slower than the first method, and it also requires Office installed;
• Via Libre Office - unlike the previous method, it is free, in addition to xls and xlsx formats, it also supports its own tables, but requires the installed LibreOffice package and some preparation of the downloaded file (the first line of the table must contain the names of the columns).

Let's take a closer look various ways and options.

Via ADODB.Connection

In general, ADO stands for ActiveX Data Object and is used for software access to various databases. The biggest problem when creating any connection to a third-party file (including Excel) is to correctly compose the connection string.

For Excel files There are three possible options here:

Connection string options:

• Provider – the driver used is defined here;
• Data Source – defines the name of the file that we will open;
• Extended Properties – here you can specify whether a header line is needed for the table (HDR = YES indicates that the data will be read from the first line, HDR = NO - what from the second), whether the file is opened read-only (ReadOnly) and some other additional parameters.

Having created a connection string, we can connect to the downloaded file (Fig. 13)

Fig.13

Now we can use a simple request (Fig. 14) to start retrieving information from the downloaded file.

In this case, the “Sheet” parameter determines which sheet from the Excel workbook we will work with.

The set of records stored in a worksheet can be read using a Recordset object. In this case, the first record of the sheet can be obtained using the BOF (beginning of file) parameter, and the last EOF (end of the file).

Via Excel application

The main difference from the previous method is that in addition to database drivers, Excel must be installed on the computer where the connection is made. Only in this case can we initialize the application to read data from the table (Figure 16).

This one has COM object There are several child parameters, but the main one for us, given the current conditions of the task, is the WorkBooks parameter (Fig. 17).

After initializing the workbook, it is necessary to determine the sheet from which data will be read (Fig. 18).

After this, you can loop through the rows and columns of the table of the file being opened.

A few words about possible errors

The lion's share of errors when connecting to a file occurs due to the fact that the file is already occupied by another application. It’s good if the taskbar shows that Excel is running on the computer, but if you or another user opened it from external processing, this can only be determined visually through the “Task Manager”, so do not forget to close the connection before completing the transfer procedure:

In the case of working through ADO (Fig. 19);

Fig.19

• In the case of working with the application (Fig. 20).

Rice. 20.

It is best to organize the connection and the procedure for completing work with data within the Attempt-Exception-EndAttempt construct, calling an error description in an exceptional situation. Although this sometimes slows down the work, it makes it much easier to determine the cause of the error and, ultimately, how to eliminate it.

Counterparties in 1C 8.3 are one of the most important reference books in almost all typical configurations. This directory stores information about buyers, suppliers and other legal and individuals with whom the company interacts. Let's look at how to create a new supplier or buyer and check the counterparty using the Taxpayer Identification Number (TIN).

Let's look at how to add a counterparty in 1C. To maintain a list of counterparties in 1C: Accounting, there is a reference book “Counterparties”. Where can I find it? You need to go to the “Directories” menu, “Counterparties” item.

The directory list form window will open. To add a new directory element, click the “Create” button or the “Insert” key on the keyboard. A form will open for filling out and editing the counterparty's data.

Working with the 1C Counterparty service

Currently, the system has the ability to obtain counterparty data and check by its TIN or name. Dossiers on counterparties are taken from the state registers of the Unified State Register of Legal Entities/Unified State Register of Individual Entrepreneurs.

Important! The 1C Counterparty service is available only to those who have subscribed to information technology support (ITS) and paid for access to the 1C Counterparty service. The cost of the 1C contractor service is 4,800 rubles for 12 months (you can order the service from). If your organization for some reason does not subscribe to ITS, then the data can be entered manually. Let's consider both options.

If you are a subscriber, but the 1C Counterparty service does not work or displays errors during operation, you need to check the connection settings to it. To do this, go to the “Administration” menu, the “Connecting Internet support” item. Click the “Connect Internet support” button. In the window that opens, enter your username and password to connect. Click the “Login” button, and if everything is entered correctly, you will be connected to 1C Counterparty.

Now you can return to entering the counterparty.

We enter the TIN, click the “Fill” button and, if the counterparty with the same TIN is found in the state register, we get the following picture:

Get 267 video lessons on 1C for free:

As you can see, all the necessary fields with the counterparty’s data are filled in, all that remains is to check them just in case and click the “Record” button. The “Address and telephone” line opens if you click on the “>” button. Here we will see the legal, actual and postal addresses. If they match, you can check the appropriate boxes.

If you do not have Internet support, you can enter the details of the counterparty manually, starting from the “Type of counterparty” field.

If you need to create a foreign counterparty in 1C, you need to determine the country of its registration. After this, fields will become available for the foreign supplier or buyer to fill out. This will hide the TIN and checkpoint fields.

Entering an agreement or bank account of a counterparty

In 1C: Accounting 8.3, almost any document requires an indication of the counterparty agreement, so you should immediately create at least one agreement. Each counterparty for our organization can be a buyer, supplier, principal, etc. Therefore, contracts are divided into types. The following types of contracts are provided:

• With the supplier.
• With the buyer.
• With the committent.
• With a commission agent.
• Other.

Let us assume that transactions with this counterparty will mainly be carried out on the sale of goods and services. Then follow the “Agreements” link to the list of agreements and add a new agreement there. Type of agreement – ​​“With the buyer”, name “Main agreement”. Now, for example, in the document “”, when you select this counterparty, the “Agreement” field will be filled in automatically.

To carry out banking transactions, you must add one or more bank accounts to your counterparty (link “Bank accounts”).

The counterparty is ready to work.

Watch also our video on filling out the “Counterparties” directory:

Checking the counterparty's TIN

The TIN field is not required to be filled in, but if it is filled in, the system will first check the correctness of the entered value. The TIN must comply with the format introduced by the Federal Tax Service of the Russian Federation. Checking counterparties using tax identification numbers and checkpoints has become especially relevant after recent innovations in VAT returns.

In addition, when entering the TIN, the uniqueness of the value in the directory is checked. If a counterparty with the same TIN already exists in the directory, a warning will be issued. This is necessary to avoid duplicate elements.

These checks do not require a permanent connection to the Internet.

On this moment in the latest releases of 1C:Accounting appeared new service to check the TIN directly in the Federal Tax Service database. This service requires a permanent Internet connection. It is turned on in the “Administration” menu, the “Support and Maintenance” item.

Check the appropriate box and click the “Check access to web service” button.

If access is successful, the program will notify us about this.

The directory of counterparties in 1C 8.3 Accounting 3.0 is very important. It stores data about all suppliers and customers in your organization. Errors in this directory can be critical, especially with registration data.

You can access this directory through the “Directories” section.

Create a new counterparty from the list form that appears.

Automatic filling

In the header of the counterparty’s card, you can enter its TIN or name and click on the “Fill” button. In this case, the details will be automatically downloaded from the Unified State Register of Legal Entities/Unified State Register of Individual Entrepreneurs.

This functionality only works when connected to Internet support. The cost of access to the 1C Contractor service is 4,800 rubles/year.

If you are not connected, the program will display a corresponding message and prompt you to do so.

Click “Yes” and in the window that appears, enter your ITS login and password. It should have been provided to you along with the configuration supply agreement. If for any reason you do not have access to ITS, please contact us.

Manual filling

You can also fill out all these fields manually.

Please note that when entering the TIN and KPP, the program checks them. If the program already has a counterparty with such details, you will be notified about this. It is highly undesirable to duplicate counterparties. You yourself will then get confused in them.

The program also checks the entered TIN and KPP according to the format approved by the tax service.

If you have Internet support enabled, all counterparties will be checked for availability in the Federal Tax Service database.

Counterparties found in the Federal Tax Service by TIN and KPP will be displayed as follows.

Those counterparties that could not be found will be displayed as shown in the figure below.

In the form of a list of counterparties, it is also implemented for convenience automatic check for availability in the Federal Tax Service database. The TIN of unfound counterparties will be highlighted in red.

Write down the counterparty you created after you have filled out and verified all the details.

Bank accounts

Another important detail in the counterparty’s card is his bank account. It is used when non-cash payment. We will transfer to this account to the supplier cash. If the counterparty is a buyer, then he will make payment from this account. Cashless payments are now the most popular.

In the counterparty's card, select the bank in which it is serviced and indicate the current account number. This will be the main account of our counterparty for non-cash payments.

In 1C 8.3 there is a built-in check to ensure that the account number you entered is correct. And if an error is made, the system will notify you about it.

If we go to the counterparty’s card using the “Bank Accounts” hyperlink, we will see that our account was automatically added there and set as the main one. You can add other accounts to this list.

Documentation

On the “Documents” tab of the counterparty’s card, you can view or create specific documents for it. These include, for example, implementations, returns, etc.

Treaties

When working with a counterparty, be it a purchase, sale or other actions, we enter into an agreement with him. In the future, it is selected in the relevant documents. All agreements with our counterparty are displayed on the corresponding tab of his card in the directory.

Let's create from this list new contract. The most important thing is to correctly indicate its type (with supplier, buyer, etc.).

The same counterparty may have several contracts, including those with different types. For example, our company produces office chairs. We can purchase materials for their manufacture from any organization, and in this case we will sell them a batch of our chairs.

Fill out the basic fields and write down the agreement.

You can designate an agreement as the main one from the list of all counterparty agreements. Then he will have the corresponding sign in the first column (green flag).

You can also attach a file to the agreement, for example, a scanned copy of a signed paper agreement or copies of the counterparty’s documents

Watch also the video about filling out and working with the “Counterparties” directory in general: