Using vb.net and Excel to arrange production plan--one of the discussion of Enterprise informatization

Excel uses vb.net and Excel to schedule production

-A study of enterprise informatization

Banhai (Sunhai)

Development tools: Microsoft Visual Studio. NET 2003
Operating system: Windows XP


Discussion on the reasons of enterprise informatization failure

The success rate of ERP in Chinese enterprises is not high. What is this for?
The practical ERP system has two characteristics:
1. Require a high degree of customization
2. To be able to modify the ERP system at any time according to the actual enterprise

And now the enterprise's ERP system development is based on outsourcing mode, developers can also penetrate the company's various departments, master all aspects of information, and then carry out custom development. However, after all, developers are not practical practitioners, the breadth of information, depth is limited, so, under normal circumstances, the development of the ERP system is difficult to ensure the first feature: the requirement of a high degree of customization.
　　
On the other hand, the actual situation of the enterprise may change at any time, the ERP system must be modified at any time, can meet the needs of the enterprise at any time. If the first, ERP system is to meet the needs of enterprises, then a year later? Of course, developers can also track the needs of enterprises, at any time to modify the ERP system, but it is not very convenient.

　　
The ideal Enterprise informatization mode

Personally believe that the ideal enterprise information model is this:
1. Enterprise Information system development should be carried out by internal personnel, rather than outsourcing
Only in this way, the practical ERP system requires two features: a high degree of customization requirements; To be able to modify the ERP system at any time according to the actual enterprise is likely to have both.
2. Enterprise information should be from the actual work needs, and gradually promote
Company practitioners (and developers) from the actual work needs, the development of the corresponding information modules, functions, and gradually improve.
In reality, many enterprises develop the information system functions a lot, but how many people will use it, and ultimately is showy just.
As the author of the unit, said big not small, but to start the implementation of information management, is impossible. The quality of personnel in various departments is not universally accepted.

What is the biggest difficulty in realizing the ideal enterprise informatization model described by the author?
The biggest difficulty is Sanking, the ability of a specific manager to have two abilities: management ability and development ability. This kind of talent is not every enterprise can get.
Although the author is not what talent, but in recent years engaged in enterprise management work, and the development of information management system is also inkling. The author will be able to personally practice the "ideal enterprise Information Model", and the relevant experience to share with you.


The actual situation of the company

Since 2004, I have been in charge of the company's planning work, including the development and release of production plans, homemade parts production plan, outsourcing procurement plan. The original company has been using handwritten way, because of the company's product models more, if I am also a manual fill, plan a lot, all day busy with mechanical writing calculation, do not do anything. So I thought about using a computer instead of some mechanical work, and I was able to spare my energy for other things. What kind of mechanical work can the computer assist and replace me?
For example, each finished product is assembled by parts, parts are made of homemade parts and external components. Each product is made up of which parts are fixed. Each month's production plan, must decompose into the homemade piece production plan and the outsourcing part purchase plan, this decomposition work I decided to hand over to the computer to complete.


Analysis of program Development mode
　　　　　　　　　　　　　　　　　
What development tools are used and what development patterns are used to achieve the required functionality? The company temporarily only I use, to achieve the function is not much, in the beginning it is not necessary to use a complex large-scale database system. , to facilitate information sharing, I decided to use vb.net to invoke Excel to implement the functional modules.
Why use vb.net instead of C # because there is a VBA in Excel whose syntax and vb.net are approximate. Moreover, VB. The difference between NET and C # is mainly syntax, not functionality.
I now only want to implement the automatic scheduling of "Homemade parts production plan", this function is not vb.net, VBA can be easily implemented. However, in view of the possible future function expansion, upgrade, or use vb.net. Vb. NET is a generation-oriented development tool that is powerful and convenient to call Excel.
It is not recommended to use VB6,VB. NET entry and primary application is not difficult, why to use the earlier version, rather than the latest version, master the latest technology!
　　

Scheduling production with vb.net and Excel

The idea of software is this:
First of all in Excel customization "Homemade pieces Production Plan" sample (template), save as a homemade production plan. xls, which has only one sheet: the sample table. Set up a variety of formats in the sample table, fill in the fixed item. Create a new blank workbook, save for 2004 homemade parts production plan. xls.
Create a new vb.net application named Production Schedule report. Put a few controls on a form, such as a few text boxes, to enter the quantity of a product; a checkbox is used to select whether it is a formal production plan or a supplementary production plan, and a two text box to enter the plan time and plan number.
Click the button to open a homemade production plan. xls and 2004 homemade parts production plan. xls, copy the sample form to 2004 homemade production plan. xls, and automatically fill in the target sheet of the respective parts required to produce the quantity.
　　
Here is the implementation code and detailed comments. Note that to add the COM reference to the Microsoft Excel object Library First, I am Office 2003, referencing the Microsoft Excel 11.0 Object Library.

　

Private Sub Produceplan ()
Call Killexcel () ' calls kill the Excel process process
' The following code determines whether the user fills in the complete information, if not complete, prompts the information and exits.
If chkformal.checked = False and chksubjoin.checked = False Then
MsgBox ("Is it a formal plan or a supplementary plan??", Msgboxstyle.critical, "Please choose the nature of the plan first")
Exit Sub
End If
If Txtday.text = "2004 months" Then
MsgBox ("What month is the production plan???", msgboxstyle.critical, "Please fill in the plan Time")
Exit Sub
End If
If txt703. Text = Nothing Or txt909. Text = Nothing Or txt931. Text = Nothing Or txt932. Text = Nothing Then
MsgBox ("Please fill out the number of plans!", msgboxstyle.critical, "not complete the number of scheduled units")
Exit Sub
End If
If chkformal.checked = True and chksubjoin.checked = True Then
MsgBox ("Both formal and supplementary can only be selected!", msgboxstyle.critical, "Please select a plan nature")
Exit Sub
End If

The following code is to calculate the number of the respective parts, in Chinese name is exempt from code comments
Dim coated Teflon panel 703 as Integer = CType (txt703. Text, Integer)
Dim Titanium Panel 909 as Integer = CType (txt909. Text, Integer)
Dim Oil mill stainless steel panel 931 as Integer = CType (txt931. Text, Integer)
Dim Oil mill stainless steel panel 932 as Integer = CType (txt932. Text, Integer)
Dim chassis as Integer = coating Teflon Panel 703
Dim Chassis as Integer = Titanium Panel 909
Dim Chassis 41A as Integer = Oil grinder Stainless Steel panel 931
Dim Chassis 41B as Integer = Oil grinder Stainless Steel panel 931
DIM Water Plate as Integer = Teflon Panel 703
Dim water pan as Integer = Teflon Panel 703
Dim water pan as Integer = Titanium Panel 909 * 2
Dim Center Bracket 2 as Integer = coating Teflon panel 703 + Titanium Panel 909
Dim Long bracket 931 as Integer = (oil mill stainless steel panel 931 + oil Grinder Stainless steel panel 932) * 2
Dim bracket 931U as Integer = Oil grinder Stainless STEEL panel 931 * 2
Dim bracket 932U as Integer = Oil grinder Stainless STEEL panel 932 * 2
Dim head hold climb as Integer = (Titanium panel 909 + oil mill Stainless steel panel 931 + oil Grinder Stainless steel panel 932) * 2
Dim battery hold as Integer = (Teflon panel 703 + Titanium Panel 909 + oil mill Stainless steel panel 931 + oil Grinder Stainless steel panel 932) * 2
Dim tee hold climb as Integer = battery hold climb/2
Dim Furnace Head GASKET as Integer = Battery hold climb * 3

' Define an array to facilitate looping through the numbers in Excel, or it can be implemented in Excel VBA
Dim Allnum () as Integer = _
{Teflon panel 703, titanium Gold Panel 909, oil mill stainless steel panel 931, oil mill stainless steel panel 932, _
Chassis 24, chassis 22, chassis 41A, chassis 41B, _
Water tray 25, water Pan 24, water Pan 22, _
Center bracket 2, long bracket 931, bracket 931U, bracket 932U, _
Head hold climb, battery hold climb, tee up, stove head GASKET}


Dim Excelapp as New excel.application
Dim Excelbook as Excel.Workbook ' homemade pieces production plan. xls
Dim excelbook2004 as Excel.Workbook ' 2004 homemade pieces production plan. xls
Dim Excelworksheet as Excel.Worksheet
Dim Planproperty as String ' The nature of the plan is a formal plan or an update plan

Try ' recommends catching errors in a try way, handling errors

Excelbook = ExcelApp.Workbooks.Open (Application.startuppath & "production plan for homemade parts. xls")

excelbook2004 = ExcelApp.Workbooks.Open (Application.startuppath & "\2004 Annual production plan. xls")
Excelworksheet = CType (excelbook.worksheets ("Sample Table"), Excel.Worksheet)
Excelworksheet.copy (after:=excelbook2004. Sheets ("Sheet1"))
' Copy the sample form to <2004 's homemade production plan >workbook the back of Sheet1

excelapp.visible = True ' Set Workbook to Visual

If chkformal.checked = True Then
Planproperty = "formal"
ElseIf chksubjoin.checked = True Then
Planproperty = "Supplement"
End If

With excelbook2004. ActiveSheet ' use with to simplify code
. Range ("D1"). Value = Txtday.text ' scheduled time
. Range ("C2"). Value = "Laoban Company" & Txtday.text & planproperty & "Purchase Plan" ' plan basis
. Range ("C25"). Value = Now.Date.Today.ToShortDateString ' This is the tabulation date
. Range ("F2"). Value = Txtno.text ' plan number

End With
For I as an Integer = 0 to 18 ' a total of 19 homemade pieces
excelbook2004. ActiveSheet.Cells (4 + i, 4) = Allnum (i) ' 4+i is line number, second 4 is column number
Next ' cycle to fill in the respective parts of the &LT;2004 annual production plan > The corresponding position of the active sheet

Catch ex As Exception ' catches errors and reclaims resources, displaying errors
Excelbook = Nothing
excelbook2004 = Nothing
Excelworksheet = Nothing
Excelapp = Nothing
Gc. Collect (0)
MsgBox (ex. ToString) ' Displays an error message to find the location
Exit Sub ' Out of error
Finally ' The code here is bound to be executed to
Excelbook = Nothing
excelbook2004 = Nothing
Excelworksheet = Nothing
Excelapp = Nothing
Gc. Collect (0)
End Try
MsgBox ("Ready-made production plan, please check")

Excelbook = Nothing
excelbook2004 = Nothing
Excelworksheet = Nothing
Excelapp = Nothing
Gc. Collect (0)

End Sub


　
Here is the process that kills the Excel process:

Private Sub killexcel () ' avoids conflicts for a process, killing an existing Excel process before calling Excel.
Dim pprocess () as Process
Pprocess = Process.getprocesses ()
Dim I as Integer
For i = 0 to Pprocess.length ()-1
If (Pprocess (i). ProcessName = "EXCEL") Then
Pprocess (i). Kill () ' Close process
End If
Next
End Sub

The above code is very simple, the function is also very limited, but very practical, used to be manually filled out and calculated, now all automatically realized. It was supposed to be half an hour's work and now it can be done in 5 minutes. If the product changes, I can modify the program at any time, so that the program always meet business needs. Enterprise information should be the case, starting from a very small function.
Maybe Mister little do not know what for enterprise information, you can show to the boss, see, used to spend half an hour scheduling plan, now 5 minutes to solve, this is also a business information.
Yesterday spent a night to write the above code, one night just one night, how much time can save later? Mister is the economy first, want him to hire professional developers, or outsourcing software company development, always let Mister Cut Blood, is also unlikely. I am a management staff, I also know a little programming, information from their own work, not to spend a penny, but can improve efficiency. Moreover, the original company only rarely understand the product of the people have scheduling ability, now use the program to achieve, everyone will.
China's enterprise information, or flashy, more companies are simply not know and do not need. I am in the company to carry out enterprise information exploration is limited by many factors. In any case, I will share my experience with you, we are together to explore the Chinese characteristics of the enterprise information Road.