Apply Excel VBA to FANUC NC data processing to diagnose NC parameters

For CNC machine tools, the most common and difficult problem to solve is the machine tool abnormality caused by incorrect setting of NC parameters. This kind of error is usually caused by misoperation. It is difficult to detect that the machine does not have any alarm, but it will run. Abnormal performance. The NC parameters of the CNC system are usually as high as 4,000 to 8,000. How to find the wrong parameters from such a large number of parameters becomes very difficult. Based on this situation, you can use Excel's powerful data processing capabilities to diagnose NC parameters.

1. Import NC parameters

To diagnose the NC parameters, first import the output file of the NC parameters into Exce. You can use the "Import Data" function to select the data source as text.

The corresponding program code is as follows:

Filename2 = Application.GetOpen Filename("(i5)NC parameter file (text file), *.txt")

If filename2 = False Then Exit Sub

If filename1 = filename2 Then

MsgBox "Select the same file", vbInformation, "Hint"

Exit Sub

End If

i = 1

Dim TextObj

Application.ScreenUpdating = False

Set fs = CreateObject("Scripting. _FileSystemObject")

Set TextObj = fs.OpenTextFile (filename2)

Do While Not TextObj.AtEndOfLine

Txtline = Trim(TextObj.ReadLine)

If InStr(1, txtline, “:”) > 0 Then

Cells(i, 3) = Mid(txtline, 1, InStr(1, txtline, ":") - 1)

Cells(i, 4) = Mid(txtline, InStr(1, txtline, ":") + 1, Len(txtline))

Else

Cells(i, 3) = txtline

End If

i = i + 1

Loop

Set fs = Nothing

Application.ScreenUpdating = True

Some parameters of Excel after importing the NC parameter file are shown in Table 1.

2. Parameter diagnosis

In order to diagnose the parameters more conveniently, all the axis parameters, spindle parameters and channel parameters need to be extracted separately and placed in different columns for easy classification and diagnosis. Here, Excel's own function function can be used.

First, extract the parameters of each axis and channel, such as extracting the X-axis data as

=IFERROR(IF(SEARCH(" A",A3,1)>1, MID (A3,SEARCH("A1",A3)+3,SEARCH("A2",A3)-3-SEARCH("A1", A3)), ""), "")

The parameters after extracting the parameters are shown in Table 2.

The extracted parameters are summarized and compared, and the axis parameters are classified into X-axis parameters, Y-axis parameters, Z-axis parameters, four-axis parameters, spindle parameters, and channel parameters. Here, the channel parameters and the spindle parameters are summarized into the B-axis parameter list. X-axis parameter summary formula

IF(A2=””,””, IF(LOOKUP(A2, Sheet1!C:C, Sheet1!D:D)<>””, LOOKU

P(A2, Sheet1!C:C, Sheet1!D:D), "non-axis parameters"))

Y-axis parameter summary formula

=IF(A2=””,””, IF(LOOKUP(A2,Sheet1!C:C,Sheet1!E:E)<>””

LOOKUP (A2, Sheet1!C:C, Sheet1!E:E), IF (ISNUMBER(MATCH(A2,Sheet7)

!D:D,0)),"M parameter", IF(ISNUMBER(MATCH(A2,Sheet7!E:E,0)),"Spindle parameter", "General parameter"))))

Z-axis parameter summary formula

=IF(A2=””,””, IF(LOOKUP(A2, Sheet1!C:C, Sheet1!F:F)<>””, LOOKUP

(A2, Sheet1!C:C, Sheet1!F:F), "→"))

Four-axis, spindle and channel parameter summary formula

=IF(A2=””,””, IF(B2=”non-axis parameter”, MID(LOOKUP(A2,Sheet1!C:C,Sh

Eet1!H:H),2,15),LOOKUP(A2,Sheet1!C:C,Sheet1!G:G)))

The parameters are summarized as shown in Table 3.

When comparing parameters, the following problems are encountered: different types of machine tools have different motors, which means different parameters. It is not easy to compare parameters. It is necessary to extract, fuzzy match, inject and re-calculate the motor parameters of the NC parameter file to be diagnosed. That is to say, the real motor code in the NC parameter file to be diagnosed is called by fuzzy matching, and then the corresponding motor parameters are recalled from the standard library and injected into the standard NC parameter list, and then compared with the NC parameter file to be diagnosed, the motor After the parameters are consistent, the interference received during diagnosis is greatly reduced.

The inherent parameters of the motor, as the name implies, are the characteristic parameters of the motor, and most of them are read-only parameters. The specific model of the motor can be determined based on the parameter values ​​of the inherent parameters of the motor. The author has done statistics, the servo axis parameters are 101, and the spindle parameters are 69.

In many cases, the motor code parameters are not consistent with the actual motor, so the fuzzy parameters of the motor's inherent parameters are used for matching. The highest matching is the real motor code.

Since a small part of the inherent parameters of the motor can be modified, the fuzzy matching method is used here to find the real code of the motor in the NC parameter file to be diagnosed. The fuzzy matching used in this paper belongs to one-dimensional fuzzy matching. The method is very simple. It is to summarize all the motor parameter files, then call them in groups and compare them with the inherent parameters of the motor in the NC parameter file to be diagnosed, and then the matching rate is the highest. The motor code is returned to the standard NC parameter file.

The real motor code of the NC parameter file to be diagnosed is obtained by the fuzzy matching method, and then the summarized motor parameters are called and injected into the list of standard NC parameters that have been extracted. In this way, the parameters of the NC parameter file to be diagnosed are in accordance with the parameters of the motor of the standard NC parameter file. If the NC parameter file to be diagnosed is inconsistent with the standard motor parameter, the parameter is marked with purple, and the special attention is given as a special reminder. .

After that, the problematic NC parameter file is compared with the standard parameter file, and the abnormal parameter can be found. The different parameters are marked as the number 255, 255 is the red digital code, and then separately extracted. Such as X-axis parameter comparison formula

=IF(B2=F2,"", IF(OR(F2="", F2="No such parameter"), IFERROR(IF (VALUE(B2)=0," ",255),""),255 ))

The parameters are compared as shown in Table 4.

Finally, the extracted abnormal parameters are classified according to the importance of the parameters and the colors are marked for convenient identification on the interface. The formula for color identification is:

IF(N2=49152,"", IF(ISNUMBER(MATCH(A2,Sheet8!N:N,0)),49152,IF(ISNUMBER(MATCH(A2,Sheet8!O:O0)),65535,IF(ISNUMBER (MATCH(A2Sheet8!P:P,0)),16776960,IF(ISNUMBER(MATCH(A2,Sheet8!Q:Q,0)),16711935,IF(ISNUMBER(MATCH(A2,Sheet8!R:R,0) )),255,"")))))))

The anomaly parameters are defined as different values ​​according to the importance of the parameters, which are 49152, 65535, 16711935, and 16776960, respectively. According to different axis numbers and different NC models, the corresponding standard NC parameter library is established. When the NC parameter file is diagnosed, the corresponding selection can be made and the same NC parameters can be called for comparison diagnosis.

3. Diagnostic interface

The VB interface is provided in the development options of Excel, which makes it easy to display the data in an interface. In the Excel interface, press Alt+F11 to display the VBA programming page. Page design and function definition can be based on actual functions. The design function interface button is shown in Figure 1.

After designing the diagnostic interface, the data in Excel is imported into the interface, and the color distinction is displayed according to the importance of the parameters as shown in Table 5.

Since the importance of the abnormal parameters is determined according to the color, when searching for the problem parameters, it can be performed according to the color. Here, the parameter number of the green mark is defined as no influence parameter on the machine tool, yellow is the optimization parameter, blue Color is an influential parameter and purple is an inherent parameter of the motor. The order of priority is purple, blue, yellow, and green is not considered. as shown in picture 2.

To make it easier to find problems, you can filter the results. When there is a problem with the spindle, only the abnormal parameters of the spindle part are observed. When there is a problem with the servo axis, only the servo axis part parameters are seen, which is more convenient and quick. To verify the time required for Excel to process the data, add the time function to the first and last lines of the program. The results are shown in Figure 3.

It takes only 7s to easily diagnose abnormal NC parameters, which is much more efficient than the manual query efficiency of the operator.

4. Conclusion

This article applies Excel VBA to FANUC's NC data processing, which makes machine tool debugging more convenient and faster, greatly improving efficiency. Due to the different versions of the CNC system and the settings of the machine tool manufacturer, the import method and function formula in the hard copy package should be based on the specific requirements of the machine tool manual to export the data table, and use the automatic processing function of Excel to diagnose the NC parameters.