How to save an invoice from 1c in excel. Tricks for saving a spreadsheet document in Excel for further work with it. Create a new Excel workbook with a list

For further work with data received in a spreadsheet document from the 1C system of any version, they can be saved in xls(x) format.

Everything would be fine, but when opening it in Excel, the user immediately understands that there is something wrong with the table, well, yes, there are no shortcuts to the sheets of the book. %)

This was the case in the 7th version of 1C and migrated to the 8th, yes, there is progress, in 8 you can save in the format xlsx, the native format of the latest versions of Excel.

To solve the problem there are several simple manipulations, the first thing that comes to the mind of any user is to save the table in another format, but even here there is no desired result, then we start making copy-paste into a new book Excel.
There is another option, as if for advanced users, - this is to go to settings Excel and in the Advanced section, check the box "Show sheet labels". It is correct, but it is also routine, in light of the frequent processing of data obtained from 1C in the form of flat tables and further analysis of the data in the form of pivot tables and other ways of presenting data in Excel.

I myself got tired of ticking boxes in the same way and solved the problem by rummaging through the Internet, for example, and looking at some other developments, I managed to implement a simple (for some it’s a button accordion, for others a crutch, however, there’s no arguing about tastes) automation mechanism Solving the problem with displaying sheet labels.

All you need to do is use two Excel window properties:

DisplayWorkbookTabs // indicator for displaying workbook sheet tabs TabRatio // ratio of the width of the workbook tabs area to the width of the horizontal scroll bar of the window (as a number between 0 (zero) and 1, default value 0.6)

TabDocument.Write(FullFileName, TabularDocumentFileType.XLS); Excel = New COMObject("Excel.Application"); Excel.WorkBooks.Open(FullFileName); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 //Excel.Visible = 1; // if you need to work further with the book
//Excel.Application.Quit() //if we just exit

TabDocument.Write(FullFileName, "XLS"); Excel = CreateObject("Excel.Application"); Excel.WorkBooks.Open(FullFileName); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 //Excel.Visible = 1; // if you need to work further with the book
//Excel.Application.Quit() //if we just exit

The need to save an Excel file from 1C arises for accountants and other employees working with both applications. Transferring data manually by copying or manually entering from the keyboard takes too long, so the question arises about modern methods. The developers took care of this and provided clear ways to copy data in both directions. All the user needs is to know these methods.

How to upload files from 1C

A convenient option for transferring information from 1C is uploading. To do this, you do not need to copy data via the clipboard; everything is simpler:

The user opens the file in the 1C program;

All that remains is to select the desired location on your computer or on a removable disk where the file will be sent, and the format of this file. If the question is how to save in Excel, then choose the XLSL format.

This option does not take much time and helps to programmatically transfer large files to Excel.

When you need to work in the opposite direction, loading Excel files into the 1C system, this is done in two ways.

How to send files from Excel to 1C manually

One of the manual methods is copying via the clipboard. The data is highlighted in an Excel document and transferred to the 1C database using the usual combination of Ctrl+C – Ctrl+V. It is important to ensure that the formatting is not lost during transfer and that all data ends up in the right cells.

The second method is loading, the reverse of unloading. The user again goes to the “File” menu in the 1C system and selects the “Download” function, after which he finds the file on the computer. The download takes a little time, usually a few seconds, but longer for larger files. It is strongly recommended to check the format for glitches and errors so that they do not become embedded in the common database and cause confusion.

How to send files from Excel to 1C automatically

Copying information from 1C 77 to an Excel document is required relatively rarely. This is typical for cases when you need to print a report or provide separate invoices to your superiors or contractors. Loading data back from Excel into 1C is more common because it is necessary for collecting and summarizing data.

In order not to do everything manually and not waste time on it, the company can order a special loader, with the help of which data will be received and processed without the participation of personnel. The loader is not a complex software product, but the problem is that it needs to be customized to the needs of a specific company. Therefore, it will not be possible to download it from free access on the Internet; you will need the help of a specialized company that will develop a downloader and implement it for the company.

Before you start using such a bootloader, it is advisable to check three points.

How correctly does it transmit data? It happens that the program works with errors, in this situation it is better to immediately detect this and make adjustments than to subsequently deal with many unreliable numbers. After implementation, the developer is responsible for checking the stability of the program, but company personnel must also check the correctness of the transfer at first, until they are confident that everything is in order.

Do all devices have a bootloader installed? Skipping individual computers and the employees working on them is unacceptable when forming a common database, because they can become the cause of leakage of both information and funds of the enterprise. The introduction of automation for a company always involves complexity.

Before sending a copy of data from Excel to 1C, great attention should be paid to uniformly filling out Excel cells. In cases where this is done carelessly, employees confuse the order of cells, enter data from two into one and do not follow the abbreviation rules, serious problems will also be found in the 1C database. The head of an enterprise or chief accountant must carry out serious work with employees before implementing business automation using 1C. Compliance with the rules for filling out reports and maintaining a database will help you subsequently avoid many difficulties and unreliable data, speed up your business and take it to a new level, ahead of your competitors.

Very often you need to obtain data from 1C 8.3 and save the information to an external file, in other words, download it from 1C enterprise. The task is almost always possible without the involvement of 1C programmers.

Let's look at a number of useful tips with which you can upload data from 1C to an external information system.

In 1C there are several methods by which you can obtain information without the involvement of specialists:

• Uploading the entire database from 1C 8.3 - for subsequent loading on another computer
• Save reports and printables in Excel, Word or PDF
• Uploading tables from 1C
• Retrieving Information Using the Query Console

More details about them:

How to download the entire database from 1C 8.3 (to a .dt file)

To do this, you will need to go to the configurator mode, then in the main menu select “Administration” - “Upload information base”:

Then you only need to indicate the name of the future file and its location on the disk.

To subsequently upload to another database, click on the “Load information base” button and select the previously uploaded file.

Uploading reports and printed forms from 1C to Excel

This method is universal and very simple. To do this, it is enough to open a printed form or report.

For example, open the TORG 12 printable form, press ctrl+S (or in the File – Save As menu) and select the file type and its name:

The same system applies - the report is downloaded in two clicks:

Get 267 video lessons on 1C for free:

Saving in the main formats is available - excel, word, pdf, html, txt, etc.

Uploading tables from 1C 8.3

Often there are such trivial tasks as “get a list of payments for the current year”, “unload a book of purchases or sales” or “unload goods from invoice No. 256”. Solving such problems is quite simple.

For example, I need to receive all receipts from the counterparty “Vector” with an amount greater than 1000. Let’s open the list form and set the necessary selections in the list:

The system will offer an output option - to a spreadsheet document or a text document, select tabular. The 1C program will display the information in the following spreadsheet document, which, like reports, can be saved in the format we need:

Similar actions can be performed for almost any tabular part in 1C 8.2. For example, we needed to unload a list of goods from receipt No. MSK00003, no problem. This is done very simply - open the document, call the context menu and after a couple of clicks we get the desired upload in the required format:

Uploading a journal of transactions from 1C to Excel:

Retrieving Information Using the Query Console

However, not all information can be found in the simple tabular part of 1C 8.2. Sometimes you need to collect data from several data sources and connect them using some fields.

This method is suitable for technically savvy people who know the basics of query language. It consists in obtaining the result of the request using and saving it in the format we need.

For example, you need to get all supplier counterparties and their phone number. To do this, we will write the following query:

The resulting table can also be downloaded into the desired file format:

You can load the received data into another 1C database using “ “. Watch the video instructions using the nomenclature as an example:

Quite often, users need to save data from 1C 8.3 to a file, for example, pdf, Word, Excel and other formats. For example, send a report by email to another person, make calculations in Excel. Unfortunately, not all users know that it is very easy to download such data from 1C on their own without outside help.

It is often necessary to download a list of some data from the tabular part of a document, a list form, a document log, etc. Such tasks are often isolated, and it is inappropriate to involve a specialist to write a report.

For example, you need to get the entire item with the “Materials” view, but you only need the columns “Item” and “Unit”.

In the case when you need to upload data with filtering, first of all, you need to configure the list in the “More” menu of the tabular section.

In the window that appears, from the left side (available fields), you need to select those by which you will make a selection (in this case, “Type of item”). In the right part of the window, the values ​​of the selection parameters are set. Moreover, the type of comparison is not limited to equality alone.

After the stock has been set, in the list form of the “Nomenclature” directory, only those items that meet the specified condition will be displayed.

The next step is to output this list into a spreadsheet document.

From the More menu, select List.

If you need to display not all columns, as in our example, then check the boxes only for those that are necessary.

As a result, you essentially get a report that we can save in any available format. How to do this will be discussed below. In a similar way, you can download tabular parts of documents and reference books, data from document journals, list forms, and more.

Uploading reports and printed forms from 1C

Any report, printed form, spreadsheet document (for example, which we received in our example) in 1C 8.3 can be uploaded to an external file in two clicks.

On the top panel of the program there is a button to save to an external file (Taxi interface). It will be active on any reports and printed forms where saving is available.

Uploading the entire 1C database

This method is necessary in cases where the 1C database needs to be unloaded as a whole, for example, to create a test copy.

Go to the desired information base in configurator mode.

In the “Administration” menu, select “Download infobase”.

Specify a different file name and save path if necessary.

The resulting downloaded data file with the *.dt extension is very easy to download in the future. To do this, in the configurator mode, in the “Administration” menu, select “Load infobase” and open the *.dt file received earlier.

Send this article to my email

The procedure for transferring data from 1C (list of directory or document elements, tabular part of a document, etc.) to Excel format is a very convenient 1C function, because allows you to save almost any data in Excel for further analysis.

1c enterprise version 8.2:

For example, we want to download data from the Nomenclature directory; to do this, open the Nomenclature directory, display the necessary data (use selections, open the desired group):

And click OK.

After this, an Excel file with the necessary data from 1C will be created in the specified path.

But, you don’t have to save the data to a file, but simply (after you have displayed the list) select the data, then save it to the clipboard (right-click - Copy), open Excel and paste the data from the clipboard into a blank sheet (right mouse button - Paste).

In the same way, you can save lists of documents and tabular parts in them.

If you want to save the report in Excel, then create a new report, and in order to upload it to Excel, you no longer need to display it in the list, but simply click in the main menu "File" - "Save a copy" and then according to the scheme described above . Or simply copy it via the clipboard.

1c enterprise version 8.3:

On the 1C platform version 8.3, the technology for saving data in Excel is slightly different from version 8.2.

Here, if you right-click on the list, you will no longer see the “Display list...” item; instead, you need to click on the “All actions” menu and select the already familiar “Display list...” item:

In the same way, you mark the required columns with checkboxes, but there is also a wonderful “Only selected” flag; when you set it, only the selected rows are displayed in the table. To select several lines in the list of the nomenclature directory, click on the lines with the mouse while holding down the "Ctrl" key, you will get something like this (selected lines are highlighted in blue):

In the end, it should look something like this:

Next, you can save this table in Excel via the clipboard (as I wrote above), or save it directly to an Excel file. To do this, in 1c (in the displayed list), click on the special main menu button (with the down arrow) and select the item “File” - “Save as...”, then a standard file saving window opens, in it you specify the file name and type “Excel Sheet” and click “Save”.

To save the report to a file, do the same as I wrote just above: