A VBA-Powered Solution for Weekly Initial Measuring Document Creation

Enhancing Efficiency: A VBA-Powered Solution for Weekly Initial Measuring Documents Creation

SBAP-581829-GMFI-MP-001

February 2024

*****

Background

There is a routine weekly task to insert measuring documents with 0 Flying Hours and 0 Flight Cycles as of 01 January 1900 at each measuring point of TSN Flying Hours and TSN Flight Cycles for all equipment with material types classified as rotable and serialized repairable in SAP.

Method

[1] Execute t-code SE16N to search for a list of equipment created within a specific period using the following inputs:

• Table = EQUI
• Maximum number of hits = 1000
• Create on = 04.02.2024 (start date) and 10.02.2024 (end date)—example
• EquipCategory = R; C; U; V—R for GA Rotables, C for Non-GA Customer Rotables, U for GA Serialized Repairable, and V for Non-GA Customer Serialized Repairable

Figure 1 SE16N

[2] Download the SE16N result data to obtain a list of equipment created during the period from 04 February 2024 to 10 February 2024, then copy and paste it into Sheet 01 EQUI.

Figure 2 Sheet 01 EQUI

[3] Run ZCOMPSTAT, use the SE16N result equipment list (column A from Figure 2) as input in the Equipment section, fill A/C Fit Life Characteristic with "FLYING_HOURS," and select AVL Grid Output.

Figure 3 ZCOMPSTAT

[4] Download the ZCOMPSTAT result data to obtain TSN Flying Hours and TSN Flight Cycles measuring point data for each equipment, then copy and paste it into Sheet 02 ZCOMPSTAT.

Figure 4 Sheet 02 ZCOMPSTAT

### Before Improvement

• Break (filter, copy, and paste) TSN Flying Hours and TSN Flight Cycles measuring points data from Sheet 02 ZCOMPSTAT to respective sheets TSN Flying Hours and TSN Flight Cycles.
• Use the VLOOKUP function to retrieve measuring points data from sheets TSN Flying Hours and TSN Flight Cycles to Sheet LSMW to fill columns C (MeasPoint TSN Flying Hours) and H (MeasPoint TSN Flight Cycles) using equipment as the lookup_value.
• Fill columns D, E, F, I, J, and K with 01.01.1900, 00:00:00, and 0 respectively as shown in Figure 5 below.

Figure 5 Sheet LSMW

### Improvement

Considering that this task is routinely performed every week, and the number of equipment created during a one-week period varies between 500 - 1000, it would be more efficient if:

• The copy-paste process from Sheet 02 ZCOMPSTAT to sheets TSN Flying Hours and TSN Flight Cycles is eliminated.
• The adjustment process of the VLOOKUP function and filling columns D, E, F, I, J, and K in Sheet LSMW can be eliminated.

Since the "create initial measuring documents" task has officially become a weekly routine and I am currently learning Visual Basic for Applications (VBA), to automate and streamline the two processes mentioned above, I created a macro as depicted in Figure 6 below.

Figure 6 Macro InitialMeasDoc()

After Improvement

[5] Press Ctrl + Shift + G to run the macro, resulting in the automatic generation of data structure as shown in Figure 5.

[6] Copy data from Sheet LSMW to Notepad.

• Columns C - F for TSN Flying Hours
• Columns H - K for TSN Flight Cycles

[7] Run LSMW with the respective Project - Sub Project - Object as follows:

• MRO - CONFIG - IK11_UP_TSN_CNT for TSN Flying Hours
• MRO - CONFIG - IK11_UP_CSN_CNT for TSN Flight Cycles