Buhoblik info. Buhoblik info Zavantazheniya file from excel in 1c

Configuration: 1s Accounting

Configuration version: 3.0.44.177

Publication date: 20.01.2017

The 1C:Accounting program now has the ability to routinely import and import data from Excel spreadsheet documents, without installing third-party processing or changing configurations. The attraction and fascination is so simple that old-timers are already very fond of this manual mechanism, and new ones are quick to master it.

Zavantazhennya with Excel 1C: Accounting 8.3 ed. 3.0

For example, we use such a spreadsheet Excel document to create a list of items and prices (partial and purchasing).

Come by.

At the top part there is pressure Nomenclature and Prices - Zavantazhiti. This is where the attraction and vivantage of tabular documents lies.

Interest is supported by xls formats (old format Excel 97-2003) and xlsx (new format), as well as mxl and ods formats. Now we select our file with the nomenclature and check for value.

Having created a spreadsheet document that looks like we used it in Excel, now the columns and the application are visible.

Pressing on Enter details Then select the required item from the list.

You can see a row that has unnecessary records, and the table header itself is pressed Vidality row.

Now we can see from the purchase price that Processing can only be completed for 1st price type!!! Then you can perform this operation again and purchase the 2nd type of price, for the 1st purchase, the price will be divided for the 2nd purchase.

The data is not filled in, Price type, Set prices. The All, Found, New, and Duplicates buttons will make your search easier and help you adjust the nomenclature. We remember and press Vantage After which the data will be imported into 1C: Accounting.

The list of items has new items, filled with assigned prices. If you need to select a different type of price, try all the points again, or choose an option with a different price, 1C find the already created positions and do not have to duplicate them, but simply add a new type price

As you know, Vantage has created 2 documents for establishing the prices of the nomenclature, for the Purchase Price and for the Distribution Price. Replacement of the processing price for Interest in Excel in 1C: Accounting 8.3, the fact that the processing is regular and works without failures and all the features of filling out the document are ensured.

Vivantage in Excel from 1C: Accounting 8.3 ed. 3.0

Even the most important function in edition 3.0.44.177 is even more impressive in Excel with 1C: Accounting 8.3, this process will help you create a price list for the company or transfer your list of items to customers with prices.

Everything is there at the witness's Nomenclature - Nomenclature and Prices - Vivantage.

At the point Columns, select the columns that are required in the Excel document. Vidber serve for the selection of nomenclature for groups, authorities, names... etc.

For visualization in Excel, we add a new column, and the MDV rate itself is not required, but we are practicing.

When you exchange information between counterparties (price lists, transactions, etc.);
To make it easier for operators in situations where the main part is carried out in 1C, and part of it is compiled in Excel tables;
First of all, the data base is up-to-date.

For this operation, you can use both the standard functionality available for the payment of an advance payment to the information technology support (ITS) and the independent writing of the procedure, implemented through various options. connection. Our article aims to explore as much as possible all possible implications and connections to the largest number of sources in order to collect data from Excel in 1C.

Universal mechanism

On the ITS disks, as well as on the 1C portal, when you go to the “Technological support” -> “Universal data and processing” menu, the “Data acquisition from a spreadsheet document” folder contains a similar processing.

When launched, the form opens (Fig. 1):

As can be seen from the form, it allows you to condense the transferred information to the next configuration objects:

Dovidnik;
I will tabulate part of the document from the witness;
Register of statements.

Depending on the position in which the jumper is installed, the object selection field changes.

The tabular form of the processing will be filled in when you open the file that you want to download.

Typical processing encourages vivantage with:

Divided into files by 1C company, files in the formatmxl;
Lisztxls saved from formatExcel 97-2003;
Text filetxt;
Tablesdbf.

Excel files can take a long time to complete, so that, as they are transferred, the acquisition of data can be done in many stages, or you know for sure that you will have to open the original file many times, for economical ії nerves і hour, information can be saved better in mxl format. Earnings can be achieved directly from the form of production.

It is important to note that the output file will have groupings of rows, subheadings, and notes that will need to be removed manually.

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

Fig.2

Add frequently to your tables, place the header with additional details and data (name of the other form, details of the counterparty, date and number of the input document, column names, etc.) to disable their processing by the program on the form, at the details ізі “The first row of a spreadsheet document, the first row with information, what to bear

The vibor of the vibor of the metadan, on the jacket of health, the recording of іnformasy, the partle of the bookmark “Nalastuvannya” will be automatically commanded by the rice -in -laws, the inventory of the most important important іnformasyu. To view the columns of the tabular part of the “Customization” tab, dedicate the following section.

Columns "Nalastuvannya"

Icon – the proportions in the row are assigned and the corresponding details are remembered.

Submitted to the details – here a synonym (name) is written for the metadata details, as in the configurator.

Search field – if you put a check mark in this field, you will process the search elements for the relevant details and, if successful, change the obvious data, the additional function of this field is to protect the appearance duplicates.

Description of types – displays the type of data that contains other metadata details.

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

Fig.3

Shukati – there is a search for a similar element, at any time a new one can be created;
Install - a song of no significance is established in an orderly manner;
Calculate – at this entry in the field of the folded element there will be insertions the result of the calculation of the value indicated in the column “Umova vyazku/Viraz znachennya”.

In this case, activating the Viraz field will open the form (Fig. 4).

Fig.4

Column number is a field that is used for insertion, from which columns of the Essel table it is necessary to take data for filling.

Significance for registration - the situation often arises when the file that is being stored does not contain all the data required to record the data element, in which the information that must be placed in yes nomu field.

Mind link/Viraz significance – often we have already touched on this field, if we looked at the fields, we can also indicate in this field that the output data will be synchronized.

The axis, in principle, is all the information that is available on the “Customization” tab.

So as not to waste a lot of time on importing, registering types and expressions, developers have introduced the possibility of saving the option of customizing files with mxlz extensions.

You can check the correctness of the data by pressing the “Refill Control” button (Fig. 1). After this, you can start the acquisition process. You will be notified about the success of the procedure or non-standard situations.

There is another mechanism for collecting data from Excel in the “Management and Trade” information bases. This is a less universal, lower guidance method that does not require an ITS subscription and is located with a standard supplier.

This procedure can be found on the “Purchases” tab, in the “Service” menu, it is called “Purchase price reduction from files” (Mal.5)

Fig.5

Form of obrobka vengeance:

date selection field, indicating the time at which the price is valid;
Field for selecting a counterparty who has specified their price list;
A button that allows you to select the type of prices to be installed;
I’ll list the part, so you can fill it with tributes that you’ll be attracted to.

The Qiu form can be shown in Fig.6

Fig.6

The explanation at the top of the form explains how to peel the first part of the form.

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

When working via the web interface, in some browsers you may need to install a browser add-on (Mal.7). We need to press the “Start installation” button and restart our connection.

Fig.7

Then, using the clipboard, we can transfer information from one table to another. If the necessary columns (“Article”, “Name”, “Price”) are filled in, we press the “Next” button and go to the other side (Fig. 8)

Fig.8

The program automatically searches for similarities in the middle of the database and, if there are no such ones, sets up options for removing the request. The external appearance of the table part can be controlled by the additional pump. In addition, the user can independently enter elements into the file that will appear in the database.

You can make a comment in the text field, making entries in the document (Fig. 9):

Fig.9

After Vikonannya obrobki:

The “Postman's nomenclature” will have a unique element created (as if it didn’t exist);
You will be given the element of the document “Nomenclature”;
The document “Setting the prices of the item” will be created and posted based on the designations: postal owner, type of price and date of data recording.

The process “Retrieving products from external files” works in a similar way.

Do-it-yourself processing options

The main problem with extracting data from an Excel file lies in the fact that 1C does not have a unique mechanism for its generation. There are a number of options for connecting Excel to 1C:

Via Microsoft ADODB - use a simple method, which is usually used for both file and client-server options for saving the database;
Via Microsoft Office - a method that sometimes fails when working with SQL databases, as a rule, it works much better than the first method, before installing Office as required;
Via Libre Office - in addition to the advanced method, in addition to xls and xlsx formats, it supports the same table, but also requires the installed LibreOffice package and any preparation for the file that is required (the first row of the table guilty of misplacing column names).

Let's take a look at the different methods and options.

Via ADODB.Connection

In general, ADO stands for ActiveX Data Object and serves for programmatic access to various databases. The biggest problem when creating any connection to a third-party file (even to Eselevsky) is to correctly arrange the connection row.

For Excel files there are three options:

Connection row parameters:

Provider – the driver is indicated here;
Data Source – indicates the name of the file that we are creating;
Extended Properties – here you can specify: the required row-header for the table (HDR = YES to talk about those data that will be read from the first row, HDR = NO – which from the other), or the file to be opened only for reading (ReadOnly) and actions Other additional parameters.

Having created the connection row, we can connect to the file that we want (Fig. 13)

Fig.13

Now we can use a simple command (Fig. 14) to start selecting information from the file that is being imported.

In this case, the “Arkush” parameter indicates which sheet of the Excel book we will be working on.

The set of records that are saved on the archive can be read using the Recordset object. In this case, the first record of the sheet can be selected with the parameter BOF (start of the file), and the remaining record EOF (end of the file).

Via Excel add-on

The main difference in the previous method is that in addition to drivers, work with databases on the computer where the connection is made, there may be installations of Excel. In this case, we can initiate the program to read the data from the table (Fig. 16).

This COM object contains a number of child parameters, and the main one for us, for the detailed minds of the set, is the WorkBooks parameter (Fig. 17).

After initializing the book, it is necessary to identify the arch from which the data is read (Fig. 18).

Then you can cycle through the rows and columns of the table in the file that is being opened.

A few words about possible milks

The left part of the errors when connected to a file occurs through those that the file is already occupied by another program. Well, since in the task panel you can see that Excel is running on the computer, but if you or another user has opened it from the current process, you can visually determine it through the “Task Manager”, so don’t forget before finishing m transferred close connections:

What does the robot do via ADO (Fig. 19);

Fig.19

Sometimes the robots have an additional allowance (Mal. 20).

Small 20.

The connection and procedure for completing the work with the data is most quickly organized in the middle of the Test-Disconnect-EndTry design, resulting in the faulty situation of the description of the solution. If you want this to improve the work, we will also definitely forgive the significant reasons for the guilt of the amend and, therefore, the methods for its abrogation.

Addendum 1C has long been the most popular program among accountants, planners, economists and managers. It means a varied number of changes due to different types of activities, and localization under the standards of the environment in many parts of the world. In the future, more businesses will switch to the same form of this program. Otherwise, the procedure for transferring data manually from other programs to 1C is a long and tedious task that takes up a lot of hours. Since everything is done using Excel, the transfer process can be easily automated and accelerated.

Transferring data from Excel to 1C is required during the initial period of working with this program. Sometimes there is a need for something like this, when during the hour of activity it is necessary to enter certain lists that are saved at the bottom of the table processor. For example, if you need to transfer price lists or agreements to an online store. By the way, if the lists are small, they can be typed in manually, but what else can you do if they contain hundreds of names? In order to speed up the procedure, you can go into a number of additional possibilities.

Almost all types of documents are suitable for automatic acquisition:

List of nomenclature;
List of counterparties;
List of prices;
Perelik made a request;
Information about purchasing or sales etc.

It’s important to note that in 1C there are no built-in tools that would allow you to transfer data from Excel. For these purposes, you need to connect an external archiver, which is a file in the format epf.

Data preparation

We will need to prepare data from the Excel table itself.

In addition to these universal actions, before preparing data from the Excel book, it will be necessary to provide the document with relevance to the specific vendor we will be victorizing, but we will talk about this a little below.

Connection of an External Vendor

Connect your external device from extensions epf You can use the 1C add-on both before and after preparing the Excel file. Golovnya, so that before the beginning of the process of conquest, resentment and preparatory moments are in full swing.

There is a number of new innovative Excel tables for 1C, created by various vendors. Let's look at the example from the following tools for processing information “Obtaining data from a spreadsheet document” for version 1C 8.3.

One of the main databases used by 1C is a list of products and services. Therefore, to describe the procedure for importing Excel, we will use the application of transferring this type of data.

Let's turn to the processing window. Since we are focusing on the product range, then in the parameters the switcher is responsible for standing in the position "Dovidnik". After all, this is how it is established for the mind. It is not necessary to transfer this trace if you decide to transfer another type of data: a tabular part or a register of reports. Far from the field "The Look of a Conspirator" Click on the button that has a speck on it. A list of items that appear will appear. We need to select an item "Nomenclature".

After this, the searcher automatically arranges the fields that the vikorist program uses for your type of adviser. It is important to immediately note that you do not have to fill out all the fields.

Now we open the Excel document again to transfer. Yakshcho Numnivannya Yogo columns Vidniyu Nimennnya Poliv Dovidnik 1C, Yaki Mistya Vidpovіdnі, Treaselly Cross -Summaries of the Stovptzi in the Exeli so, Schobs Nameni Vonesta Zbigali. If the table contains columns for which the browser has no analogues, delete their trace. Our generation has such stipulations "Kilkist"і "Price". It should also be noted that the order of placing columns in a document can easily avoid the same as the presentation in the sample. If you don’t have enough data for certain items that appear in the sellers, then these columns can be left empty, otherwise the numbering of items, according to the data, must be avoided. For ease and speed of editing, you can install a special Excel feature to quickly move columns around.
After all these details have been collected, we will emboss it on the badge "Save it", which is represented by icons that represent a floppy disk in the upper left corner of the window. Then we close the file by pressing the standard close button.

Let's turn around at the 1C window. Embossed on a button "Vidkriti" as shown in the yellow colored folder.

The file opens in a window. Let's move on to the directory, decompressing the Excel document that we need. Removing image files from installations for expansion mxl. To show the file we need, we need to move it to position "Arkush Excel". After which the document to be transferred is displayed, it is embossed on the button "Vidkriti".

After which, instead, it appears in the book. To check that the data is filled in correctly, press the button "Reserve control".

As a matter of fact, the replenishment control tool informs us that no items have been found.

Now let's move to the tab "Nalastuvannya". U "Poshuku field" We put a tick in the row that everyone has, so that it will be added to the list of nomenclature and will be unique. Most often for whose vikoryst fields "Article" or else "Name". It is necessary to work so that when new items are added to the list, the data is not affected.

Once all the data has been entered and adjusted in Wikonian, you can proceed to the complete collection of information to the agent. For whom, click on the entry "Vanquilize the tributes".

The process of acquisition is underway. After this is completed, you can go to the nomenclature editor and re-convert so that all the required data is added there.

We have simplified the procedure for adding data to the nomenclature guide in the 1C 8.3 program. For other witnesses and documents, the acquisition of documents will be carried out according to this very principle, but with certain nuances that the clients can deal with independently. It is also necessary to respect that the procedure may be different for other third-party vendors, otherwise the advanced approach will be lost for everyone: initially, the sampler will collect information from the file at the right time, where it will be carried out. ї editing, and then it is added directly to the 1C database.

1C 8.3 has the ability to en masse import a list from a spreadsheet document, for example, from an Excel file.

For those who are interested in the vikoryst’s outer appearance Capturing Data from a Tabular Document.epf for ceramic molds (8.2 and 8.3), yaku can be engraved . It is universal and must be suitable for any configuration written under a hardware program.

If you are a programmer and want to develop “your” passion for Excel, report instructions from;
An example of the importance of the price list in 1C Trade Management can be found in .

To start the new processing, you need to go to the File menu, then Open and select this processing from the directory in which it was saved:

Processing of data from the spreadsheet document 1C 8.3 (keroved forms)

After the layout has been created in 1C, you can start working with it. Before we begin, we need to figure out where and what we want:

I want to point the butt at the witness.” I created a file with data in xls format.

The editing can also be done in the following file format:

In any other format, the data can be copied.

The axis looks like this in the Excel file with the output data, which is what we see in the “Nomenclature” browser:

Watch 267 video lessons from 1C free of charge:

I did not begin to register all the fields of the evidence, which is sufficient for understanding the principle of attraction. In addition, these data are available for the beginning of work with these positions.

These positions are not yet available in the 1C information database, and we are interested in them.

Click on the “Open” icon (on the small number of symbols like “Select a device”) and select a file from the data. You can simply copy the information. My file with the butt can be acquired. Dani becomes attracted to the outfit automatically. Now let’s check that we filled in our data correctly. To do this, press the “Refill Control” button.

Yak bachimo, we have some favors! Let's get used to it. Let’s go to the “Customization” tab:

First of all, I want to straighten out the peace, I want to increase my respect for one important detail. The program does not immediately know which field to use to select the browser element, since it is already there. So she needs to tell him. I am willing to joke about the code because it is, as a rule, unique. In the “Search field” column, put a tick in the “Code” row. Now, if a record with such a code is found, it will be replaced, or otherwise created.

Important! If you do not specify the search field, duplicate items may appear. Especially after another search for a similar file!

Now we wonder why “Odinitsya” is barking on the field. On the right is that units of data in 1C 8.3 are saved in the same browser, and standard processing searches for each unit by name. But in fact, the word “one” is written in the “Outside name” field.

Unfortunately, we can only search for “Names” and “Code” (for the programmer there is a wide range of possibilities). To increase respect for the little ones, please note that I have a code in the “One” column. And in the following example, indicate that the search needs to be followed by a code. We click on the column “Mental Link” opposite “Unit” and change to “Code”.

Now I wonder why we should talk about “Service” there on the list of pardons. Another important thing is respect. The columns in the file must be arranged strictly in the same order as the rows of the fields in the editor. And for us “” is at the very bottom, and for the file after the “Comment” column.

To move the “Type of nomenclature” row up, there will be blue arrows at the top of the form. Following the “Up” arrow, raise the required row and place it under the “Comment”.

It’s stamped “Zavantazhit danі”, and this time everything goes through without mercy:

Video tutorial on the acquisition of goods in 1C from Excel files:

This method is simple. Its essence lies in the fact that the object TabularDocument There are methods:

Write down (< ИмяФайла>, < ТипФайлаТаблицы >) to visualize data from the file;
Read (< ИмяФайла>, < СпособЧтенияЗначений >) to import data from the file.

Respect!

The Write() method is available both on the client and on the server. The Read() method is only available on the server side. It is necessary to remember about this
with planned client-server interaction.

Let's take a look at the example of saving a spreadsheet document file. There must be some way to create and store a TabularDocument object, and vivantagene The file has only one row:

TabDoc . Write(WayKFile, TabularDocumentFileType. XLSX);

Here TabDoc- Forming a tabular document, Way to file- I am in the file for visualization, TabularDocumentFileType.XLSX- Format of the created file. The following Excel formats are supported:

XLS95 - Excel 95 format;
XLS97 - Excel 97 format;
XLSX – Excel 2007 format.

TabDoc = New Tabular Document;
TabDoc . Read(WayKFile, MethodReadValueTabularDocument.Value);

Here Way to file— I go to the Excel file, I’m interested. Method of ReadingValue of Tabular Document.Value indicates how you need to interpret the data read from the output document. Available options:

significance;
Text.

Exchange via OLE

Exchange through OLE automation technology is perhaps the most advanced option for software work with Excel files. It allows you to use all the functionality that Excel provides, but provides greater flexibility than other methods. To exchange via OLE you need to install MS Excel:

On the computer of the end customer, since the exchange is carried out for the client;
On the computer of the 1C server: Enterprise, where exchange is paid for the server.

butt vivantagene:

// Create a COM object
Excel = New COMObject (“Excel.Application”);
// Switch off the power supply in advance
Excel . DisplayAlerts = Nonsense;
// Creation of a new book
Book = Excel. WorkBooks. Add();
// Position on the first arkush
Arkush = Book. Worksheets(1);

// Write the value to the account
Arkush . Cells(RowNumber, ColumnNumber). Value = Value in the middle;

// Save the file
Book . SaveAs(ImFile);

Excel . Quit();
Excel = 0;

Apply it reading room:

// -- OPTION 1 --

// Create a COM object
Excel = New COM Object (“Excel.Application”);
// Book view
Book = Excel. Workbooks. Open( Way to file);

Arkush = Book. Worksheets(1);

// Close the books
Book . Close(0);

// Close Excel and save memory
Excel . Quit();
Excel = 0;

// -- OPTION 2 --

// Book view
Book =OtrimatiCOMObject( Way to file);
// Position on the required arc
Arkush = Book. Worksheets(1);

// Read the meaning of the comrade, call here the cycle bypassing the middle is expanded
Significance of Komirki = Arkush. Cells(RowNumber, ColumnNumber). Value;

// Close the books
Book . Application. Qui t();

For going around All completed rows of the Excel arc can be analyzed using the following methods:

// -- OPTION 1 --
Quantity Row = Leaf. Cells (1, 1). SpecialCells(11). Row;
For NumberRows = 1 By NumberRow Cycle
Significance of Komirki = Arkush. Cells(RowNumber, ColumnNumber). Value;
KіnetsCycle;

// -- OPTION 2 --
RowNumber = 0;
Until the Truth Cycle
NumberRows = NumberRows + 1;
Significance of Komirki = Arkush. Cells(RowNumber, ColumnNumber). Value;
Yakshto NOT Significance Reserved (Value of Komirki) Todi
Interrupt;
KinetsYakscho;
KіnetsCycle;

Instead of sequentially traversing all rows of the sheet, you can animate all data into an array and work with him. This approach will be seen when reading the great obsessive data:

All Columns = Arkush. Cells (1, 1). SpecialCells(11). Column;
UsyogoRow = Arkush. Cells (1, 1). SpecialCells(11). Row;

Region = Arkush. Range (Sheet. Cells (1, 1), Sheet. Cells (All Row, All Columns));
Dani = Region. Value. Vivantazhity();

The table below shows the most required power and methods for working with Excel via OLE:

Diya	Code	Comment
Work with supplement
Setting the visibility of the program window	Excel . Visible= Nonsense;
Setting the output mode one after the other (input/not output)	Excel . DisplayAlerts= Nonsense;
Close the program	Excel . Quit();
Working with a book
Creation of a new book	Book = Excel. WorkBooks. Add();
From the original book	Book = Excel. WorkBooks. Open(ImFile);
Saving books	Book . SaveAs(ImFile);
Closed books	Book . Close(0);
Work with a sheet
Installation of flow sheet	Arkush = Book. WorkSheets(SheetNumber);
Setting the name	Arkush . Name = Name;
Installation of zakhist	Arkush . Protect();
Znyattya zakhistu	Arkush . UnProtect();
Setting the page orientation	Arkush . PageSetup. Orientation = 2;	1 - book, 2 - landscape
Installation of the left cordon	Arkush . PageSetup. LeftMargin = Excel. CentimetersToPoints(Centimeters);
Installation of the upper cordon	Arkush . PageSetup. TopMargin = Excel. CentimetersToPoints(Centimeters);
Installation of the right cordon	Arkush . PageSetup. RightMargin = Excel. CentimetersToPoints(Centimeters);
Installation of the lower cordon	Arkush . PageSetup. BottomMargin = Excel. CentimetersToPoints(Centimeters);
Work with rows, columns, middles
Setting the column width	Arkush . Columns(ColumnNumber). ColumnWidth = Width;
View of the row	Arkush . Rows(RowNumber). Delete();
Column view	Arkush . Columns(ColumnNumber). Delete();
Vidalennya in the middle	Arkush . Cells(RowNumber, ColumnNumber). Delete();
Set value	Arkush . Cells(RowNumber, ColumnNumber). Value = Value;
Eating middles	Arkush . Range(Sheet. Cells(RowNumber, ColumnNumber), Sheet. Cells(RowNumber1, ColumnNumber1)). Merge();
Setting the font	Arkush . Cells(RowNumber, ColumnNumber). Font. Name = Font Name;
Set font size	Arkush . Cells(RowNumber, ColumnNumber). Font. Size = Font Size;
Set bold font	Arkush . Cells(RowNumber, ColumnNumber). Font. Bold = 1 ;	1 - bold font, 0 - normal
Set to italics	Arkush . Cells(RowNumber, ColumnNumber). Font. Italic = 1 ;	1 - italic, 0 - normal
Installation of the underlined font	Arkush . Cells(RowNumber, ColumnNumber). Font. Underline = 2 ;	2 - underchairs, 1 - without

In order to find out what kind of power it is necessary to change or what type of call can be used quickly macros Excel. Once you record a macro with the necessary actions, you can then view the VBA code of the recorded macro.

Wikoristannya COMSafeArray

When importing large amounts of data from 1C Excel, you can quickly select the object COMSafeArray. Based on the definitions of the syntax helper, COMSafeArray is an object wrapper over a rich array. SafeArray From COM. Allows you to create and use SafeArrays to exchange data between COM objects. In simpler terms, this is the value that can be exchanged between applications using OLE technology.

// Creation COMSafeArray
ArrayCom = New COMSafeArray("VT_Variant" , AllColumns, AllRows);
// Reserved COMSafeArray
For Stor = 0 By AllStor - 1 Cycle
For Number = 0 Total Columns - 1 Cycle
MasivKom . SetValue(Quality, Stor., Value);
KіnetsCycle;
KіnetsCycle;
// Assigned area Excel value COMSafeArray
Arkush . Range (Sheet. Cells (1, 1), Sheet. Cells (All Row, All Columns)). Value = MasivKom;

Exchange via ADO

The Excel file is exchanged via ADO with a database that can be accessed for additional SQL queries. Installation of MS Excel is not required, but it is obligatory to have an ODBC driver, for additional access. The ODBC driver that is being tested is indicated when specifying the connection order to the file. Make sure the required driver is already installed on your computer.

Exchange via ADO is similar to exchange via OLE, but at the same time it is not possible to use the Excel functionality for designing middles, laying out sides, entering formulas, etc.

butt vivantagene:

Connection = New COMObject("ADODB.Connection");

Z'ednannya . ConnectionString = "

|Data Source=" + file name + “;
;
Z'ednannya . Open(); // Vidkrity connection

// Create a COM object for the command
Command = New COMObject("ADODB.Command");
Team

// Assigned to the command text before creating the table
Team . CommandText = "CREATE TABLE [Sheet1] (Column1 char(255), Column2 date, Column3 int, Column4 float)";
Team . Execute(); // Vikonannya command

// Assigned to the text of the command to add a row to the table
Team . CommandText = "INSERT INTO [Arkush1] (Column1, Column2, Column3, Column4) values ('abvwhere', '8/11/2017', '12345', '12345,6789')";
Command.Execute(); // Vikonannya command

// Visible command and closed connection
Command = Undefined;
Z'ednannya . Close();
Z'ednannya = Unspecified;

To create a new arch, the formation of its structure can be quickly achieved with objects ADOX.Catalogі ADOX.Table. I can see the code below:

// Create a COM object to work with a book
Book = New COMObject("ADOX.Catalog");
Book . ActiveConnection = Connection;

// Creation of a COM object to work with the data structure on Arkusha
Table = New COMObject("ADOX.Table");
Table . Name = "Sheet1";
Table . Columns. Append("Column1", 202);
Table . Columns. Append("Column2", 7);
Table . Columns. Append("Column3", 5);
Table . Columns. Append("Column4", 5);

// Creation at the bottom of the sheet with the described structure
Book . Tables. Append(Table);
Table = Undefined;
Book = Unspecified;

The applied method

Table . Columns. Append("Column1", 202);

Another parameter specifies the column type. The parameter is optional, depending on the column type:

5 - adDouble;
6 - adCurrency;
7 - adDate;
11 - adBoolean;
202 - adVarWChar;
203 - adLongVarWChar.

butt reading room:

// Create a COM object for connection
Connection = New COMObject("ADODB.Connection");

// Set up a row of connections
Z'ednannya . ConnectionString = "
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=" + file name + “;
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
Z'ednannya . Open(); // Vidkrity connection

// Create a COM object and select a selection
Vibir = New COMObject("ADODB.Recordset");
Text Zapitu = "SELECT * FROM [Letter1$]";

// Vikonannya will ask
Vibirka . Open(Text Zapitu, Z'ednannya);

// Bypass the selection result
Still NOT Vibirka. EOF() Loop
ColumnValue1 = Vibrator. Fields. Item("Column1"). Value ; // Change to column names
Column2 value = Vibrator. Fields. Item(0). Value; // Index by column index
Vibirka . MoveNext();
KіnetsCycle;

Vibirka . Close();
Vibirka = Undefined;
Z'ednannya . Close();
Z'ednannya = Undefined;

Row connected parameter HDR This means how the first row on the arcus is to be selected. Possible options:

YES - the first row is taken as the name of the columns. The value can be accessed by column name and index.
NO - the first row is treated as data. The value can be found only by the column index.

At the pointed butts, more than a few ADO objects were examined. The ADO object model consists of the following objects:

connection;
Command;
recordset;
Record;
Fields;
Stream;
Errors;
Parameters;
Properties.

Vivantage without programming

To save data with 1C Excel, do not go too far into programming. In the Business mode, you can display the data you need for data extraction, which can be saved in Excel without programming.

To save a spreadsheet document (for example, a result), you can use the command Save or else Save it... smut menu.

In the window you need to select the directory, name and file format to be saved.

To save data from dynamic lists (for example, a list of items) you need to:

Enter data into a spreadsheet document using additional commands More ⇒ Enter list…;
Save the spreadsheet document in the required format.