Scheduling production with vb.net and Excel

Excel

Copyright NOTICE: You can reprint, reprint, please be sure to hyperlink form to indicate the original source of the article and author information and this statement
http://www.Tuenhai.com/

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

From 2004 onwards, Tuenhai responsible for the company's planning work, including the development and issuance of product production plans, homemade production plans, outsourcing procurement plans. The original company has been using handwritten way, because the company's products more models, if the Tuenhai is also a manual fill, plan a lot, all day busy with mechanical writing calculation, do not do anything. So Tuenhai consider using a computer to replace some of the mechanical work, Tuenhai can free up the energy to catch other things. What mechanical work can the computer do to assist and replace Tuenhai?
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 Tuenhai decided to give the computer to complete.


Program Development mode Analysis

What development tools do you use to implement the required functionality with what development model? The company temporarily only Tuenhai their own use, to achieve the function is not much, in the beginning there is no need to use a complex large-scale database system. To facilitate information sharing, Tuenhai decided to use vb.net to invoke Excel to implement 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.
Tuenhai now only requires the implementation of automatic production planning program, this feature 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. The
is not recommended for use with 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 COM references to Microsoft Excel object Library,tuenhai is 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 out the information intact, prompts for information, and exits if incomplete.
If chkformal.checked = False and chksubjoin.checked = False Then
MsgBox ("Is it a formal plan or an update plan???", Msgboxstyle.critical , "Select the schedule Nature")
Exit Sub
End If
If Txtday.text = "2004 months" Then
MsgBox ("What month of production plan???", MSGBOXSTYLE.CR itical, "Please fill in the scheduled 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 schedules!", msgboxstyle.critical, "complete the number of scheduled units")
Exit Sub
End If
if CHKFO Rmal.　　　　Checked = True and chksubjoin.checked = True Then
MsgBox ("You can only choose one for both formal and supplemental!", msgboxstyle.critical, "Please select a schedule nature")
Exit Sub
End If

' The following code is the number of individual parts, in Chinese name is exempt from code comments
Dim Teflon Panel 703 as Integer = CType (txt703. Text, Integer)
Dim Titanium Panel 909 as Integer = CType (txt909. Text, Integer)
Dim Oil Grinder Stainless steel panel 931 as Integer = CType (txt931. Text, Integer)
Dim Oil Grinder stainless steel panel 932 as Integer = CType (txt932. Text, Integer
Dim chassis As Integer = Teflon Panel 703
Dim chassis As Integer = Titanium Panel 909
Dim chassis 41A As Integer = oil mill Stainless Steel Panel 9
Dim chassis 41B As Integer = oil mill stainless Steel panel 931
Dim water Pan As Integer = Teflon panel 703
Dim water Disk As Integer = Teflon Panel 703
Dim Water pan As Integer = Titanium Panel 909 * 2
Dim Center bracket 2 As Integer = Teflon panel 703 + titanium Panel 909
Dim long bracket 931 As Integer = (oil mill stainless steel panel 931 + oil Grinding Stainless Steel panel 932) * 2
Dim bracket 931U As Integer = oil mill Stainless Steel panel 931 * 2
Dim bracket 932U As Integer = oil mill Stainless Steel Panel 932 * 2
Dim head hold as in Teger = (Titanium panel 909 + oil mill Stainless steel panel 931 + oil mill Stainless steel panel 932) * 2
Dim battery hold as Integer = (Teflon panel 703 + Titanium Gold Panel 909 + oil mill Stainless steel panel 931 + oil Grinder Stainless steel panel 932) * 2
Dim tee With Hold As Integer = Battery hold/2
Dim furnace head gasket As Integer = Battery hold * 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 <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

More content in http://www.Tuenhai.com/.

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, Tuenhai 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. Tuenhai is the management staff, itself also understand 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. Tuenhai in the company's enterprise Informatization Exploration is limited by many factors. In any case, Tuenhai will Tuenhai experience with you to share, we jointly explore the Chinese characteristics of the enterprise information Road.