In 1C, there are 2 ways to work with MS Excel files, through a COM object and built-in 1C tools using a spreadsheet document object. Let's examine both of these methods in more detail.
1. Work through COM-object.
This method requires MS Excel to be installed, and if you are working with a file on the server, then MS Excel must be installed on the server, if on the client, then MS Excel is also required on the client side.
An example (at the end of the article can be viewed in text form):
It should be noted that all objects, methods and properties provided by the "Excel.Application" COM object are objects, methods and properties of VBA, the MS Office programming language.
Note:
Sometimes you need to work with an existing template. Then we need to store this template somewhere so that all the necessary users have access to it. However, there is an easier solution, we save the template as a binary layout.
2. Work through Spreadsheet Document 1C.
The 1C spreadsheet document supports the MS Excel format for recording starting from platform 8, but for opening only starting from platform 8.3.6. Moreover, when opening, all sheets are loaded into one spreadsheet document. Starting from version 8.3.10, when loading, different sheets are loaded as different areas.
The recording example is very simple and does not require much attention:
However, here we have a problem. When recording from 1C to Excel workbook By default, the display of sheet names is disabled.
This problem can be solved in 2 ways, 1 - enable the display of sheets in the book itself in the settings (not all users will agree to do this), 2 - do it through a COM object (we again need MS Excel installed).
After this small addition, sheet labels will be visible in the MS Excel file.
Reading MS Excel is not so simple task, since the read method is only available on the server or thick client. To do this, we need to transfer the MS Excel file to the server.
&AtClient
CreateDocument() Procedure
An exception
End of Attempt;
Book = Excel.WorkBooks.Add(); //Create a new MS Excel workbook
Sheet = Book.WorkSheets.Add(); //Add a sheet
Sheet.Name = "Example from 1C"; //Set the sheet name
Sheet.Cells (1,1).Value = "(!LANG:Let's create a formula";!}
Sheet.Cells(2,1).Value = 1;
Sheet.Cells(2,2).Value = 2;
Sheet.Cells(2,3).Formula = "=A2+B2";
Sheet.Cells(3,1).Value = CurrentDate();
Book.SaveAs("C:\1\Test.xlsx");
Book.Close();
EndProcedure
&On server
GetLayoutServer() function
Return FormAttributeToValue("Object").GetLayout("Upload Template"); //This is how we get the external processing layout;
EndFunctions
&AtClient
Procedure Work()
Excel = New COMObject("Excel.Application");
An exception
Show("Unsuccessful attempt to connect Excel component. Excel program not installed on this computer!");
End of Attempt;
Layout = GetLayoutServer();
Layout.Write(TemporaryFileName);
Do you have a question, do you need the help of a consultant?
Book = Excel.WorkBooks.Open(TempFileName);
SheetTemplate = Book.WorkSheets(1);
SheetTemplate.Cells (6,1).Value = "(!LANG:Date:";!}
SheetTemplate.Cells(6,2).Value = CurrentDate();
SheetTemplate.NumberFormat = "dd/mm/yy;@"; // Let's give the date format, this format is obtained by recording a macro in MS Excel
SheetTemplate.Columns("B:B").EntireColumn.AutoFit; // Stretch the column to fit the date exactly
Book.SaveAs(FileName);
Book.Close();
EndProcedure
&AtClient
EndProcedure
&AtClient
Procedure WriteSpreadsheetDocument()
SpreadsheetDocument = New SpreadsheetDocument();
SpreadsheetDocument.Area("R1C1").Text = "Example of an entry in MS Excel from 1C";
SpreadDocument.Write("C:\1\Test2.xls",SpreadDocumentFileType.XLSX);
Excel = New COMObject("Excel.Application");
Excel.WorkBooks.Open("C:\1\Test2.xls");
Excel.Visible = 0;
Excel.ActiveWindow.DisplayWorkbookTabs = 1;
Excel.ActiveWindow.TabRatio = 0.6;
Excel.ActiveWorkbook.Save();
Excel.Application.Quit()
EndProcedure
BinaryData = New BinaryData("C:\1\test2.xlsx");
Address=PutToTempStorage(BinaryData,ThisForm.UniqueIdentifier) ;
SpreadsheetDocument = DownloadOnServer(Address);
SpreadsheetDocument.Show();
EndProcedure
&On server
Function LoadOnServer(Address)
TempFileName = GetTemporaryFileName("xlsx");
FileData = GetFromTempStorage(Address);
FileData.Write(TemporaryFileName);
SpreadsheetDocument = New SpreadsheetDocument();
Return SpreadsheetDocument;
Quite often, when communicating with potential clients, I hear that they have enough table functionality from the office suite, and they do not see the point in 1C programs. All because some still associate this decision only with programs for an accountant, which is not true.
Excel is a universal and mass product, you can't argue with that. Almost everyone can use this program, unlike the developments of "1C", which are aimed at narrow specialists. Using "green" tables, you can arbitrarily implement the necessary functionality that will be convenient: here is the financial model of the organization with all mutual settlements, management accounting, even regulated, big choice data visualization, the ability to personally expand the capabilities of the program using various add-ons - everything is in your hands, everything from scratch ...
It’s good if you are just starting your business, gradually taking into account the necessary data in the table on relationships with customers, suppliers, regulatory authorities, building your arbitrary structure, but what to do next when the database becomes voluminous, and the staff will be in the tens? All the same to consider according to their own parameters? I am in favor of not confusing convenience with habit, which often interferes with the systematization of a business, and, consequently, its optimization.
For clarity, let's imagine what needs may arise in the automation of accounting, for example, in trade:
1. Analysis of sales within a certain period.
The head of the sales department can analyze sales data. After all, it is important for him to understand whether the plan is being implemented, how effective his strategies are, in order to make timely adjustments in case of inefficiency.
2. Warehouse accounting.
Do I need to explain that it is also important for sales to understand the availability of goods in stock, which goods can be reserved and which are out of stock, and what needs to be ordered? I think everything is clear here.
3. Maintaining a database of counterparties.
Even if the owner sells only what can be bought here and now, which is typical for the B2C sector, then he has slightly different relations with the supplier base - contracts, primary ... Imagine how convenient it is when everything connected with the supplier is automatically immediately displayed in a single database? The manager responsible for the warehouse immediately sees the availability of goods in the warehouse, another employee responsible for the documents monitors information on their availability, whether all obligations under contractual relations are being fulfilled, who owes whom and how much. And in case of discrepancies, you can check the data for certain period, generating results in less than a minute.
4. Profit of the organization.
It will not be difficult to generate a profit report, since all data on expenses and incomes are already present in the database. Provided that the information is entered in a timely manner, at least by the required date. Here the fundamental factor is the personal responsibility of the user.
If you do not have many clients and suppliers, Excel will really suit you, since there is plenty of time to design your own schemes in the table and fill them in, with the subsequent distribution of documents into various folders on your computer: contracts, suppliers, clients, clients in the neighborhood , clients in development, clients to be deleted - the endless and exciting process of creating folders within a folder. And if there is a lot of information, is it convenient to keep a record of your database in this case? Of course, there are always exceptions, sometimes there are organizations with large turnovers, whose financial data is maintained only in Excel. How much time does it take them to exchange and combine data between departments? Answer: a lot.
I have no idea how to quickly find the right one in a large number of Excel documents organized by an arbitrary system. For example, when submitting documents for tax requirements. How will an accountant submit reports to regulatory authorities when the information is all scattered? Accounting, of course, can be outsourced and some business owners, unfortunately, may not care what processes a given specialist may have, as long as he balances. But even if so, how then is the process of document exchange organized? How quickly will an employee on the side understand the impromptu information structure?
I can’t say the same about 1C, because all the information there is structured and interconnected:
- A single database for both remote and full-time employees, in which you can work with all standardized and arbitrary documents;
Reporting to the regulatory authorities is based on the data that is already reflected in the program, there is no need to open many files, reduce several values into one table to get the final results.
In defense of Excel
Probably, it seemed to you that I am against building data in "green" tables. Not at all. In fact, I myself often do some business in Excel: if I need arbitrary calculations, if I need to segment data by clients, conducting analytics according to those parameters that are not in 1C - yes, this happens - I go to " excel. Simply put, in tables I do calculations, non-standard analysis, but then I systematize everything later anyway in 1C.
And then, to view the uploaded files from 1C, you still need Excel, since the data in 1C is stored in spreadsheets. Do you think it's a vicious circle? I will put it differently: "1C" and "Excel" go side by side like two partners, but each of them has its own purpose, they complement each other well, but do not interchange.
If you need to systematize your affairs, taking into account the legislative system of our state, taking into account the specifics and direction of business, especially when it comes to large amounts of information, then you need 1C. If you need arbitrary calculations, building a strategy from scratch, visualization of non-standard analytics data, then Excel is at your service. But it is much more convenient to work with these solutions simultaneously.
What is the result - "1C" or "Excel"?
At the very start, if the question is precisely in finance for automation, although basic programs"1C" does not cost that much money, I would use "Excel". A huge plus is that the data from the "green" tables can be loaded into 1C without loss. But as you scale up, I would advise you to pay attention to business process automation programs. There are many of them, and it may not necessarily be "1C" ...
The transition to 1C can be made when you understand that it is necessary to optimize your resources, including automating issues related to relationships with customers and suppliers, with supervisory authorities. In general, adjust your work processes to improve business efficiency when information processing will take a large number of time, and Excel files will no longer be able to cope with the volume of data entered.
However, not every "1C" can suit you, you need to take into account various factors: the specifics and scope of business, scale, the need for daily tasks, the routine that needs to be minimized. Everything is individual. The truth, as I said, is somewhere between "1C" and "Excel" - it is in addition to each other.
On this, perhaps, everything. But if you have any questions, please contact us and we will try to help you. Successful business processes, colleagues!
Automation solution:
IMPLEMENTATION MONITOR
ITAN specialists have successfully completed the project on setting up and automating the financial management system in ACTION-DEVELOPMENT and launched the systems in commercial operation.ITAN specialists have successfully completed the project on setting up and automating the financial management system in ACTION-DEVELOPMENT and launched the systems in the industry
ITAN specialists completed work on setting up a cash management model for the specifics of HOMAX GROUP. As part of the model setup, the following work was carried out: DDS analytics and payment priorities were set up. The DDS budget model has been set up. The types of payment transactions and applications are distinguished. Org set up. structure and routes of approval of requests for payments. The levels of access to applications and sections of the DDS budget have been determined. On the
The ITAN company has begun work on the introduction of a standard model management accounting subsystem "ITAN: Management balance" for the configuration "1C: Trade Management 11.1" in the company AMARE. The company "ITAN" began work on the implementation of a standard model of management accounting for the subsystem "ITAN:
The following functional blocks have been introduced within the framework of the project: Cash flow budgeting, Treasury, Approval of documents. Client: V.I.P. Service" / "V.I.P. Service" Project: Automation of cash management on the configuration "ITAN: Management balance" and "1C: Management
The ITAN company won the tender for the development and implementation information system corporate financial management in Terra Auri Group of Companies. The purpose of creating and implementing an information system for corporate financial management is to automate the process
JSC “V.I.P. Service" has increased the efficiency of financial management at the enterprise by introducing modern technologies automation of management accounting on the basis of PP "ITAN: Management balance". The implementation was carried out by the Customer's own IT service, with the consulting support of ITAN specialists. Client: V.I.P. Service" / "V.I.P.
The ITAN company won the tender for the automation of the financial module in the Vipservice holding. The ITAN company won the tender for the automation of the financial module in the Vipservice holding. The following functional blocks will be introduced as part of the Financial Module project: Management accounting Budgeting
The ITAN company and the Baltis company signed an agreement on the implementation of management accounting based on 1C: Trade Management and ITAN: Management Balance. The main implementation work has been completed, the system is being trial operation. "Baltis" is a Latvian canned food supplier and wholesaler.
"TatSotsBank" held a tender for the automation of the bank's treasury. The bank needed a modern tool for solving problems. More. "TatSotsBank" held a tender for the automation of the bank's treasury. The bank needed a modern tool for solving problems: Budgetary control of BDDS by limits. Formation and approval of applications for payments and their verification for limits. Building a payment calendar. Control
The ITAN company and the BI Partner company have entered into a cooperation and partnership agreement. Within the framework of cooperation, the BI Partner company will promote the ITAN: Management Balance software product. On the this moment, negotiations are underway on joint participation in projects to automate management accounting in several companies, based on software
NPFs of Sberbank use ITAN: Management Balance for budgeting, contract management and treasury purposes. The accounting service needed a tool to record the location of contracts. more details NPFs of Sberbank use ITAN: Management Balance for budgeting, contract management and treasury purposes. The accounting department needed a tool
Automation of budgeting and treasury in the consulting company "Redl and Partners" on the basis of "ITAN: PROF Management Balance" Implemented a budgeting subsystem for planning and controlling income and expenses. The cash management subsystem is undergoing trial operation. Redl & Partners is a diversified consulting company specializing in providing professional
The ITAN company completed a project on setting up financial accounting and reporting in accordance with IFRS in the branch of Alpen Pharma - Alpen Pharma Ukraine.
The Avtobau company turned to ITAN specialists on a recommendation to solve the problems of generating accurate and prompt management reporting. The Avtobau company turned to ITAN specialists on a recommendation to solve the problems of generating accurate and operational management
The company "Design-Fashion" contacted us in September 2014. The company faced the task of automating management accounting for a group of companies. The management of the company decided to automate the management accounting system based on software
To automate accounting in production, the subsystem "ITAN: Production Accounting" was recommended, which is built into the existing "Texttime" configuration "1C: Trade Management 10.3 + ITAN: Management Balance", and solves the problems of production accounting and costing. Texttime company "works successfully with the configuration "ITAN: UP
TEL improves the efficiency of financial management by using the ITAN: PROF management balance system. The implementation will be carried out by the IT department of TEL. Today, the TEL group has its own fiber optic network, which covers the whole of Moscow and the nearest suburbs, with a total length of over
The company "Ethan" has completed the stage of trial operation automated system cash management in CJSC Enterprise Ostek. The system has been put into commercial operation and is functioning stably. All cash flows are reflected in the system, regular input and approval of applications for payments is carried out. Forecasting payments and creating a payment calendar is carried out
At the beginning of 2013, the Megalex group of companies decided to automate the management accounting system based on software product"ITAN: Management Balance". The main tasks are automation of management accounting, cash management and budgeting. management system
The ITAN project team completed a project to automate budgeting using a complex economic planning model in the Podruzhka retail chain. The implementation project was carried out according to the standard project methodology and was completed in 6 months. As a result, the budgeting model has been tested and Podruzhka has formed a budget for 2013 in the new system. In the future, work is planned to introduce the subsystem "Cash management
In July 2016, the NPF of Sberbank carried out a planned transition to a new edition of the accounting program: 1C: Accounting 3.0 + 1C: Management of NPF 4.0, in which the ITAN: Management Balance subsystem is built in, this system used for budgeting
The Mirkon company previously worked on the ITAN: Wholesale Trade House 7.7 program, which, in a complex, automated the operational and management accounting of a trading enterprise. moreThe Mirkon company previously worked on the ITAN: Wholesale Trade House 7.7 program, which, in a complex, automated the operational and management
The management of the company "Kholodilnik.ru" decided to introduce budgeting and cash management subsystems based on the "ITAN: Management Balance" system. Implementation will be carried out by Kholodilnik.ru specialists based on standard ITAN models. Kholodilnik.RU is a Russian online store specializing in the sale of all types of household appliances domestic and foreign production. project open
INTRODUCTION OF MANAGEMENT ACCOUNTING AND "ITAN: MANAGEMENT BALANCE" IN "SUMOTORI GK" Successfully completed independent implementation of the "ITAN: Management Balance" system in "SUMOTORI GK". The tasks of automating the financial accounting of Sumotori Group: Automation of the process of preparing individual and consolidated financial statements on the basis
The implementation of an automated accounting and reporting system in accordance with IFRS will be carried out by ITAN specialists according to the standard project methodology based on the ITAN: Management Balance software product. Read moreCompany "PARTER.RU" turned to us on the recommendation of our customers. The company is faced with the task of automating accounting and reporting in accordance with IFRS. Implementation of an automated accounting and reporting system for
NPF Sberbank has been fruitfully working with the ITAN: Management Balance system since 2013. Implemented and successfully used "ITAN: Management Balance" for the purposes of budgeting, contract management, treasury, accounting for the location of contracts. "NPF Sberbank" has been fruitfully working with the "ITAN: Management Balance" system since 2013. Implemented and successfully used "ITAN: Management Balance" for budget purposes
In 2012, the Lendor company acquired the ITAN: Management Balance software product in order to automate the accounting and reporting system in accordance with IFRS. In 2012, the Lendor company acquired the ITAN: Management Balance software product in order to automate the system
The ITAN project team completed a project to automate the generation of management reporting in the Podruzhka retail chain. The implementation project was carried out according to the standard project methodology and was completed in 4 months. As a result, the management reporting system based on ITAN: PROF Management Balance has passed trial operation, and allows you to quickly receive such reports as: OBDR, OBDDS, Report
ITAN specialists completed work on the automation of cash management and transfer accounting to a single base for the VIKIMART company. During the implementation project, the following works were performed: Terms of reference were written according to the rules for converting 4 databases "1C: Accounting
ACCOR contacted us at the beginning of 2016. The main task was to automate the accounting and reporting system in accordance with IFRS. read moreACCOR contacted us at the beginning of 2016. The main task was to automate the accounting and reporting system in accordance with IFRS. The company's management decided to automate accounting in accordance with IFRS based on the ITAN: Management Balance configurations. The ITAN: Management Balance system improves the accuracy and timeliness of financial plans, budgets
The ITAN company has completed work on setting up a management accounting system for the Museum company. The implementation project lasted two months, and as a result, a management accounting model was customized to the needs of the Customer. The ITAN company completed work on setting up a management accounting system for the Museum company. The implementation project lasted two months, and as a result, according to the needs of the Customer, we
The design department of the ITAN company completed the first stage of setting up a management accounting system at the Nevsky Transformer Plant Volkhov.
The European legal service improves the efficiency of financial resource management by implementing the ITAN: PROF Management Balance system. The European Legal Service is a significant player in the Russian legal services market and is today recognized as one of the most
ITAN specialists automated cash management in Aktion media group. As a result of the “Standard Project”, the following cash management business processes were automated: 1. Setting budget limits for the Central Federal District, budget items and projects; 2. Formation, budgetary control and electronic approval of applications for payments; 3. Formation of the register of payments; 4. Build
The implementation of a management accounting system based on 1C trade management 11 and management balance sheet in kpi has been completed. The implementation by Ethan specialists was completed in 4 months. As a result, KPI received a modern tool for management accounting and management reporting. Coil Products International
The accounting department of the NPF of Sberbank turned to ITAN to solve the problems of generating a complex balance sheet “Calculation of own funds”. Read more. The accounting department of the NPF of Sberbank turned to ITAN to solve the problems of generating a complex balance sheet “Calculation of own funds”. To the report was
01/20/2016. Standard implementation of management accounting in "Maguros" More. Cooperation with the company "Maguros" began with the implementation of a test case by ITAN specialists according to the Customer's data. After the implementation of the test case, the management of the company "Maguros" made the final decision on the implementation of software "ITAN: Management Balance". The Maguros company will solve problems
ITAN specialists are implementing a standard management accounting model for the ITAN: Management Balance subsystem for the 1C: Trade Management 10.3 configuration at TelecomInvest. Specialists of the ITAN company began to carry out joint work with the Customer on the implementation of a standard model of management accounting for the subsystem "ITAN: Management database
Specialists of the ITAN company completed work on setting up the ITAN: Management Balance system in terms of management accounting in accordance with accounting policy HOMAX GROUP. Product "ITAN: Management balance is integrated into working base"1C: Production Enterprise Management". As part of setting up the management model
The ITAN company has completed work on the development of the "Contract Management" subsystem for the tasks of "NPF Sberbank" for accounting business contracts.
Millhouse has already implemented a standard IFRS model for generating IFRS statements in USD. Millhouse has already implemented a standard IFRS model for generating IFRS statements in USD. Due to different IFRS from the regulated functional currency, discrepancies arose in the accounting for the application of the provisions of IFRS. To solve this problem
As part of the ITAN financial management automation project, the first stage has been completed - automation of mutual settlements in management accounting. Further, it is planned to finalize operational accounting, comprehensive implementation of management accounting, budgeting and treasury. "Ali
The implementation department of the ITAN company completed a project to implement and configure the "Budgeting" subsystem of the "ITAN: Management balance" configuration to automate budgeting PL and generate reports Plan-fact for "STS Eventim.Ru". implementation and configuration of the "Budgeting" subsystem of the "ITAN: Management Balance" configuration to automate budgeting PL and form
Specialists of "ITAN" company have completed work on the implementation and customization of the standard model "Data Consolidation" of the system "ITAN: Management Balance" and the standard model "Data Consolidation" for 11 information bases companies included in the banking group of CB "Energotransbank" (JSC). Specialists of the company "ITAN" completed the implementation and configuration of the standard model "Data Consolidation" of the system "ITAN: Management Balance" and the standard model "Data Consolidation" for 11 information databases of companies , entering
The Digimarket company acquired the ITAN: Management balance software product in 2008 in order to automate management accounting on 1C: Trade Management. Read moreThe company "Digimarket" acquired the software product ITAN: Management Balance in 2008 in order to automate management
The ITAN company won a tender for the creation of a property management information system, consolidated management accounting and budgeting for the Voentorg group. The ITAN company won a tender for the creation of a property management information system, consolidated management accounting and business
The ITAN company and the Regent holding are launching a joint project to automate management accounting, budgeting and cash management. The implementation will be carried out mainly by the IT department of the Regent holding with the participation of ITAN consultants for training and
ITAN specialists implemented a test example of accounting for business contracts in the ITAN: Management Balance system with integration with existing system budgeting, management accounting and cash management in the Aktion group of companies. As a result of the test analysis, work is planned to introduce the "Contract Management" subsystem. Action-Development is a dynamically developing company in the commercial real estate market. She owns several
Specialists of the ITAN company completed work on the translation and adaptation of the management accounting and budgeting model performed on ITAN: Management Balance and 1C: Accounting 2.0 under edition 3.0 of the 1C: Accounting configuration in the Taber Trade company (chain of stores " Girlfriend"). The Podruzhka chain of stores is an active and successful Russian chain of stores
The ITAN company completed the setup and refinement of the cash management system for the Terra Auri holding. The following settings have been made in the projects: The ITAN: Management Balance system in the Customer's 1C:Accounting 3.0. Set up a cash flow budget model. The document “Monthly payment plan of the Central Federal District” has been finalized for the business processes of the Customer. Types of applications and routes for their approval have been configured. Improved reports on payment
Launch of a joint project to automate management accounting in the Museum company based on ITAN: Management Balance. The integration of the management system is planned to be carried out with "1C: Trade and Warehouse 7.7". The main activity of the Museum company is tea and coffee for the enterprises of the HoReCa segment.
In just 2 months, literally from scratch, our ITAN specialists wrote a subsystem for the 1C configuration: Salary and personnel management. Now the system allows the correct allocation of accounting items, with convenient scenario planning of the budget for the year. Additionally, we have included a double check method for the reliability of the correct calculation, therefore, for the effectiveness of financial management. Employees of STS Eventim ru are already successfully working
The ITAN project department completed the development and implementation of the contract management system for the specifics of Terra Auri. In the process of settings, the following works were performed: The system "ITAN: Management Balance" in "1C: Accounting 3.0" of the Customer. The contract management model has been set up. Completion of accounting documents from contracts was completed. Accounting for primary documents under contracts is set up. Expanded accounting and planning analytics
The Ethan company has begun work on the implementation of a standard management accounting model for the ITAN: Management Balance subsystem for the 1C: Trade Management configuration in the Krasny Triangle trading house. Trading House "Red Triangle" offers a wide range of rubber-fabric conveyor belts (conveyor belt), as well as other rubber products (sleeves,
The ITAN company won the tender for the automation of the management accounting system in the Yellow, Black and White holding. Read more. ITAN Company won the tender for the automation of the management accounting system in the Yellow, Black and White holding. The management of the Yellow, Black and White Group of Companies was looking for a solution on the market that would be able to solve the following tasks in a short time: Load accounting data from current 1C systems. Implement complex meta
In October 2015, the management of NTZ Volkhov decided to introduce an automated system from ITAN. Read more. The financial department of NTZ Volkhov has long considered the ITAN: Management Balance system as a good option problem solving
The ITAN company has completed the first stage of work on setting up a management accounting system and developing a property management unit for JSC Voentorg. The ITAN company has completed the first stage of work on setting up a management accounting system and developing a property management unit
The ITAN company has completed the implementation of the standard model according to IFRS of the ITAN: Management Balance subsystem in the QUEENGROUP company. The IFRS model has been installed in the working base "1C: Accounting 8", user training has been carried out, initial balances have been entered. QUEENGROUP is successful Russian company, operating in the field of wholesale sales of cars, transport services, car parts and accessories.
Omsan Logistic started cooperating with us in the middle of 2011. The main task was to automate the accounting and reporting system in accordance with IFRS.More detailsOmsan Logistic started cooperating with us in mid-2011. The main task was to automate the accounting and reporting system in accordance with IFRS. The company's management decided to automate IFRS based on the ITAN: Management Balance software product, using
The company faced the task of automating management accounting and budgeting. To implement these tasks, the company's management decided to purchase the ITAN: Management Balance software product. Cooperation with MIR GAZ started in November 2014. The company faced the task of automating management accounting and budgeting. To achieve these goals, the leadership
"Ochakovsky plant of reinforced concrete products" introduces modern technologies for automation of management accounting on the basis of "ITAN: PROF Management Balance". The implementation is planned by our own IT department. The history of the Ochakovsky Concrete Concrete Plant began in 1990, when an independent enterprise was formed on the basis of workshop No.
For a long time, the 1C application has become the most popular program among accountants, planners, economists and managers. It has not only a diverse number of configurations for various types of activities, but also localization for accounting standards in several countries of the world. More and more enterprises are switching to accounting in this program. But the procedure for manually transferring data from other accounting programs to 1C is a rather long and boring task that takes a lot of time. If the company kept records with using Excel, then the transfer process can be significantly automated and accelerated.
Transferring data from Excel to 1C is required not only in the initial period of working with this program. Sometimes there is a need for this when, in the course of activities, you need to enter some lists stored in the spreadsheet book. For example, if you want to transfer price lists or orders from an online store. In the case when the lists are small, they can be entered manually, but what if they contain hundreds of items? In order to speed up the procedure, you can resort to some additional features.
For automatic download suitable for almost all types of documents:
- Nomenclature list;
- List of contractors;
- List of prices;
- List of orders;
- Information about purchases or sales, etc.
It should be noted right away that 1C does not have built-in tools that would allow you to transfer data from Excel. For these purposes, you need to connect an external loader, which is a file in the format epf.
Data preparation
We will need to prepare the data in the Excel spreadsheet itself.
In addition to these universal actions for preparing data in an Excel book, you will also need to bring the document in line with the requirements of the specific loader that we will use, but we will talk about this a little later.
Connecting an external bootloader
Connect external bootloader with extension epf to the 1C application is possible, both before preparing the Excel file, and after. The main thing is that by the beginning of the download process, both of these preparatory points have been resolved.
There are several external Excel spreadsheet loaders for 1C, which are created by various developers. We will consider an example using an information processing tool "Loading data from a spreadsheet document" for version 1C 8.3.
One of the main databases that 1C works with is a list of goods and services. Therefore, to describe the procedure for loading from Excel, we will focus on the example of transferring this particular type of data.
- We return to the processing window. Since we will be loading the product range, the switch in the parameter should be in the position "Directory". However, it is set that way by default. You should switch it only when you are going to transfer another type of data: a tabular part or a register of information. Further into the field "Directory View" click on the button with the ellipsis. A drop-down list opens. In it, we should select the item "Nomenclature".
- After that, the handler automatically arranges the fields that the program uses in this type of directory. It should be noted right away that it is not necessary to fill in all the fields.
- Now open the portable again Excel document. If the name of its columns differs from the names of the fields of the 1C directory that contain the corresponding ones, then you need to rename these columns in Excel so that the names completely match. If the table contains columns for which there are no analogues in the directory, then they should be deleted. In our case, these columns are "Quantity" and "Price". It should also be added that the order of the columns in the document must strictly match the one presented in the processing. If for some columns that are displayed in the loader, you do not have data, then these columns can be left empty, but the numbering of those columns where there is data must match. For convenience and speed of editing, you can use special opportunity Excel to quickly move columns around.
After these actions are performed, click on the icon "Save", which is represented as a floppy disk icon in the upper left corner of the window. Then close the file by clicking on the standard close button.
- We return to the processing window 1C. Click on the button "Open", which is shown as a yellow folder.
- The file open window is launched. We go to the directory where the Excel document that we need is located. Default file display switcher set to extension mxl. In order to show the file we need, it needs to be rearranged to the position « Excel sheet» . After that, select the transferred document and click on the button "Open".
- The content is then opened in the handler. To check the correctness of filling in the data, click on the button "Fill control".
- As you can see, the fill control tool tells us that no errors were found.
- Now let's go to the tab. "Setting". V "Search field" put a tick in the line that will be unique for all items entered in the nomenclature reference book. Most often, fields are used for this. "Vendor code" or "Name". This must be done so that when adding new positions to the list, the data is not duplicated.
- After all the data is entered and the settings are made, you can proceed to the direct loading of information into the directory. To do this, click on the inscription "Load data".
- The download process is in progress. After its completion, you can go to the nomenclature reference book and make sure that all the necessary data is added there.
We followed the procedure for adding data to the nomenclature reference book in the 1C 8.3 program. For other directories and documents, the download will be carried out according to the same principle, but with some nuances that the user can figure out on their own. It should also be noted that the procedure may differ for various third-party loaders, but the general approach remains the same for everyone: first, the handler loads information from the file into the window where it is edited, and only then it is added directly to the 1C database.
