A one-click management method and system for the entire process of steel structure materials based on Tekla and VBA

Abstract

This invention discloses a one-click management method and system for the entire process of steel structure materials based on Tekla and VBA, belonging to the field of steel structure information technology. The method establishes a maintainable material baseline database in Excel; exports a parts list containing "usage location, drawing number, manufacturer, net weight, and gross weight" via a Tekla plugin; utilizes six sequentially executed VBA programs to automatically translate material codes, match and deduct planned quantities from delivered quantities, summarize by system / manufacturer dimensions, provide material shortage / overconsumption warnings, calculate area from budget orders, and statistically analyze delivery rates; a single refresh forms a closed-loop management system of "design quantity - planned quantity - delivered quantity - cost quantity," eliminating duplicate purchases, reducing steel waste, and achieving automation, refinement, cost reduction, and efficiency improvement in steel structure material management.

Description

Technical Field

[0001] This invention relates to the field of information technology in steel structure manufacturing, and in particular to a one-click management method and system for the entire process of steel structure materials, including "design quantity, planned quantity, delivery quantity, and cost quantity," based on Tekla structure and VBA macro driver. Background Technology

[0002] In steel structure projects with a capacity of tens of thousands of tons, material costs typically account for over 60% of the total project cost. Currently, traditional management methods suffer from the following pain points: 1) The detailed design stage relies on manual reading of drawings and manual calculation of engineering quantities, which is inefficient and prone to omissions; When submitting material plans, the latest drawing or model quantities need to be manually deducted from the historical plans and delivered materials. This often involves matching each row using Excel functions, which takes 3-5 working days for 100,000 rows of data and has a high error rate. 2) After the materials arrive, the quality inspector manually compares the delivery note with the purchase plan again, which makes it impossible to know in real time whether there is a shortage of materials or excessive consumption. If project management needs to view loss rates from multiple dimensions such as "system-manufacturer-plan number", they must reassemble the tables, which takes several days, leading to delays in procurement decisions, inventory backlogs, or work stoppages due to material shortages.

[0003] As the accuracy of steel structure models improves, Tekla Structures can now export a complete parts list containing fields such as "part number, section, material, net weight, gross weight, usage location, drawing number, and manufacturer" in one go. However, current technology still relies on a "export-manual translation-re-entry" process, lacking an automated solution that integrates with procurement and delivery data in real time. Therefore, the industry urgently needs a management system that can self-maintain a material baseline library, complete complex deduction logic with a single click, and output multi-dimensional comparison results in a short time to achieve cost reduction and efficiency improvement. Summary of the Invention

[0004] This invention addresses the shortcomings of existing technologies in steel structure material management, such as excessive manual intervention, broken data chains, low efficiency in cost reduction, high error rates, and the inability to track the logical relationship between "design quantity - planned quantity - delivery quantity - cost quantity" in real time. It provides a one-click management method and system for the entire steel structure material management process based on Tekla and VBA, aiming to: 1) Complete the automatic translation, matching, reduction, and multi-dimensional summarization of tens of thousands of tons and hundreds of thousands of rows of data in a short period of time; 2) Improve the accuracy of material planning to over 99% and eliminate duplicate purchases; 3) Real-time output of material shortage / overconsumption warnings, providing quantitative basis for project procurement and cost decisions; 4) By maintaining a material benchmark library and a theoretical weight library, the system can continuously self-optimize as projects accumulate, reducing reliance on highly skilled cost estimators.

[0005] To achieve the above objectives, the present invention is implemented as follows: A one-click management method for the entire process of steel structure materials based on Tekla and VBA includes the following steps: S1. Establish a maintainable material reference database, which includes at least a flat steel specification database, a profile name database, a plate name database, and a corresponding theoretical weight table; S2. Use the Tekla plugin interface to export a parts list from the Tekla structural model in one go. The parts list shall include at least the parts number, material specifications, material, net weight, gross weight, usage location, drawing number and manufacturer field. S3. Import the parts list into the Excel master control file, and automatically translate the material codes and assign theoretical weights by referring to the material reference database using the first set of VBA macro instructions, and generate a standard model quantity data table; S4. Enter or update the planned quantity data table and the delivered quantity data table in the Excel master control file respectively; S5. The VBA macro instructions executed sequentially from the second to the sixth group are used to complete the following: i. Match and deduct planned quantities from delivered quantities line by line to generate a summary of material shortage / overconsumption results by planned order number; ii. The net weight and gross weight of the model are deducted from the planned quantity and the delivered quantity, respectively, and a comparison table summarized by system is generated; iii. A comparison table compiled by the manufacturer; iv. Automatically generate budget sheets for sheet materials and profiles, and calculate the area or length accordingly; v. Statistics on the overall delivery rate and weight deviation rate of the project; S6. The six sets of VBA macro instructions are executed continuously by triggering the one-click run button, realizing one-click refresh of the entire process of "design quantity - planned quantity - arrival quantity - cost quantity".

[0006] Furthermore, the material benchmark database is stored in the form of an Excel worksheet, with rows that can be added or deleted at will, and columns that must include at least "material code, specification description, theoretical weight, and standard number". In this way, on-site personnel can add non-standard specifications at any time without the need for database software, and the system self-optimizes as projects accumulate data, reducing reliance on highly skilled cost estimators.

[0007] Furthermore, the parts list output by the Tekla plugin interface is in CSV or Excel format, and the plugin automatically adds the fields "Usage Location, Drawing Number, and Manufacturer" during export. Missing fields are left blank and can be added by the user later. This ensures that the field names are consistent during subsequent VBA deductions, avoiding matching failures caused by inconsistent column names.

[0008] Furthermore, the six groups of VBA macro instructions employ a sequential calling mechanism, with the output table of the previous group serving as the input table for the next group, and a "executed" flag is written to the status area after each group is completed. In this way, 100,000 rows of data are loaded in stages, reducing peak memory usage from 1.5 GB to 600 MB, preventing 32-bit Office software from crashing, and eliminating the risk of referencing empty tables due to users skipping execution sequences.

[0009] Furthermore, during the deduction process, the VBA macro automatically marks unmatched rows as "unrecognized" and prompts the user to add the corresponding material code from the material reference database before running the command again. This embeds the error detection mechanism into the workflow, ensuring a 100% translation success rate and preventing calculation distortions due to material shortages or excessive consumption.

[0010] Furthermore, the comparison table summarized by system includes at least the fields of "material category, system number, specifications, material, net model weight, gross model weight, planned quantity, delivered quantity, net model weight minus delivered quantity, and gross model weight minus delivered quantity," which are highlighted in bold red when positive values ​​are displayed. In this way, project managers can easily locate material shortages or over-consumption rows without needing to understand complex formulas, guiding immediate replenishment or adjustment of subsequent plans.

[0011] Furthermore, the formula for calculating area or length on the budget form is A = G / (ρ×t), where G is the tonnage, ρ is the density, and t is the plate thickness. The result is automatically converted into units commonly used in procurement, such as square meters, meters, sheets, and pieces. In this way, procurement staff can directly connect with the steel mill's order template, reducing errors from secondary manual conversions and improving order placement efficiency.

[0012] Furthermore, the one-click run button is bound to an ActiveX control. Clicking it first locks the screen refresh and event triggering, and after the macro ends, it automatically saves a timestamped copy with the filename format "project abbreviation_YYYYMMDD_HHMM.xlsm". In this way, each refresh is traceable, meeting the ISO-9001 record control requirements, while preventing the original template from being overwritten and ensuring a clean and consistent running environment for the next run.

[0013] This invention also discloses a system completely corresponding to the above method, including: a material benchmark database module, a Tekla plug-in interface module, a planned quantity / delivery quantity entry module, an Excel master control file with six embedded VBA macros, and a one-click run button. Each module can be integrated into the same workbook, or multiple users can concurrently enter data on a local area network via a shared folder. The calculation unit remains centralized in the local Excel file, balancing lightweight deployment and data security.

[0014] This invention utilizes a chain-like architecture consisting of a "maintainable material benchmark library, Tekla plugin, and six sets of sequential VBA macros" to form a lightweight computing engine within Excel. This engine can complete translation, matching, deduction, and alerts in a short time, while reducing memory spikes. One-click timestamp copying ensures ISO traceability, significantly improving refresh efficiency compared to manual spreadsheet assembly, and maintaining a stable planning accuracy of ≥99%. It also completely eliminates systemic errors such as column name misalignment and formula drift.

[0015] Meanwhile, the closed-loop logic formed by this invention compresses the real-time linkage of "design quantity, planned quantity, delivery quantity, and cost quantity" to the minute level, reducing the lag in procurement decisions from "days" to "minutes," thus lowering the risk of work stoppages due to material shortages and reducing steel loss rates. In this way, a 10,000-ton project can directly save more than 100 tons of materials. Furthermore, the on-site training time is short, the benchmark library can be reused continuously with the project, and subsequent projects are easy to replicate, realizing the leap from "experience-driven" to "data-driven" steel structure material management.

[0016] In summary, this invention has achieved a "one-click refresh" function for the closed loop of "design, planning, delivery, and cost" in a 10,000-ton steel structure project, solving the technical problems of low efficiency, high error rate, and inability to track in real time in the traditional manual method, thus achieving the goal of reducing costs and increasing efficiency. Attached Figure Description

[0017] Figure 1 This is the logic diagram of the "One-Click Management System for the Entire Process of Steel Structure Materials" of the present invention. Detailed Implementation

[0018] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the following detailed description of the specific implementation methods, structures, features, and effects of the present invention, in conjunction with the accompanying drawings and preferred embodiments, is provided below.

[0019] Example 1 like Figure 1As shown, a one-click management system for the entire steel structure material process based on Tekla and VBA is implemented. Starting with exporting the parts list from the Tekla plugin, it sequentially completes the data flow and table calls through six sets of VBA macros, including material code translation, planned / arrival deduction, system- and manufacturer-based aggregation, budget recalculation, and arrival rate statistics. This achieves a closed-loop one-click refresh process for "design quantity - planned quantity - arrival quantity - cost quantity". Its specific principle is as follows: 1. Establish a material reference database: Create a new "Material Baseline" worksheet in the Excel workbook, and pre-set commonly used items according to the format of Table 1. The column fields include: material code, specification description, and theoretical weight (kg / m³ or kg / m³). 2 The table should include the standard number and remarks. For non-standard flat steel or new cross-sections, on-site personnel can simply add the relevant information at the end without modifying the code. Once saved, this table will become the sole basis for subsequent "translation" and weight calculation.

[0020] 2. Export parts list using the Tekla plugin: In Tekla Structures, select the entire model and run the custom plugin "ExportForSteelMgr". The plugin outputs a CSV file in the format shown in Table 3, with the following fields forced: Part Number, Section (PROFILE), Material, Length, Quantity, Net Weight, Gross Weight, Usage Location, Drawing Number, and Manufacturer. If the model does not have a "Manufacturer" value assigned, leave this column blank and have the user complete it before importing to ensure 100% alignment of subsequent VBA field mappings.

[0021] 3. Import and "translate" to generate the model quantity data table: Open the Excel master control file, click the "1-Translate" button, and the first group of VBA macros will execute automatically (see code appendix ① for details): a) Read the CSV into a memory array and read the "section + material" string line by line; b) Perform a fuzzy match on the field with the same name in the "Material Baseline" worksheet. If a match is found, return the corresponding "Material Code" and "Theoretical Weight". c) If a line is not matched, the entire line will be highlighted in yellow and a pop-up message will appear prompting "Please provide the material code". The user can then run the program again after providing the code. d) Successfully matched records are written to "Table 5 - Model Quantity Data Table", and the theoretical tonnage is calculated by "theoretical weight × quantity" to provide a benchmark for subsequent reduction. This step, involving 15,000 rows of data, takes approximately 90 seconds to complete.

[0022] 4. Enter planned quantity and arrival quantity: In the "Table 7 - Planned Quantity Entry" and "Table 6 - Arrival Quantity Entry" worksheets, fill in the following information row by row according to the delivery note or purchase contract: material code, specifications, material type, quantity (sheets / pieces), unit weight, arrival date, delivery note number, vehicle license plate, and supplier. Field names correspond to the macro's hard-coded field names; users cannot arbitrarily add or delete columns, but custom remarks can be added to the last column. "Table 6" can be empty upon initial submission without affecting macro operation.

[0023] 6. Six sets of macro sequential operations After clicking the "One-Click Refresh" button, the macro will... Figure 1 Logical chained calls: The second set of macros reads "Table 6" and "Table 7", matches each row according to the four-dimensional keywords of "material code, specification, material, and manufacturer", and generates "Table 8 - Planned / Arrived Comparison List". At the same time, "Matched" is written in the "Statistical Results" column of "Table 6". When there is an over-delivery or under-delivery, the "Theoretical Shortage" column is automatically calculated and highlighted in red (see Code Appendix ② for details).

[0024] The third macro group: Based on "Table 8", it summarizes by "Plan Order Number" and outputs "Table 10 - Summarized by Plan Order Number" to obtain the tonnage of material shortage under each purchase contract (see code appendix ③ for details).

[0025] The fourth macro group: introduces the net weight and gross weight of the model from "Table 5", and subtracts them from "Table 10" again to generate "Table 12 - Summary by System". You can intuitively view the positive values ​​in the "Model Net Weight - Arrival Quantity" and "Model Gross Weight - Arrival Quantity" columns, which are warnings of material shortage or overconsumption (see code appendix ④ for details).

[0026] The fifth macro group: identifies rows in "Table 12" where "Model Gross Weight - Arrival Quantity" has a positive value, automatically writes the data into "Table 14 - Budget Sheet," and calculates the area using A=G / (ρ×t), where ρ=7.85 g / cm³. 3 t is the plate thickness; the result is converted to m. 2 For units with purchasing habits such as sheet size, meter size, etc., the budget officer can directly use it to place orders with the steel mill (see Code Appendix ⑤ for details).

[0027] The sixth macro group: summarizes all planned order numbers and generates "Table 28 - Delivery Rate" and "Table 29 - Weight Deviation Rate" to provide negative tolerance analysis data for project cost accounting (see Code Appendix ⑥ for details).

[0028] 7. Copy protection and traceability: Before the macro ends, it automatically saves a timestamped copy with the same path as the original file, named "Project Abbreviation_YYYYMMDD_HHMM.xlsm", and then refreshes the screen. This copy serves as a snapshot of the data for the day, meeting ISO-9001 record control requirements and preventing the original template from being destroyed by subsequent manual operations.

[0029] After completing the current project, the new specifications are added to the "Material Baseline" table and can then be used for the next project. As the number of records in the library increases, the initial "translation" hit rate gradually improves, and the preparation time for operation gradually shortens, thus forming a digital asset that can be continuously iterated.

[0030] Through the above steps, this invention can reduce the traditional 7-day manual data compilation work to within 20 minutes without additional hardware or software investment, reducing the error rate from 1.2% to 0.02%, saving an average of 3% of steel in projects with a capacity of 10,000 tons, and generating direct economic benefits of 500,000 to 700,000 yuan, truly realizing a "one-click closed loop" for steel structure material management.

[0031] Example 2 Example 2 is a reference for the 6 sets of VBA programs applied in Example 1, as follows: First set of VBA programs (translation); For i = 2 To lastRow 'Traverse the Tekla exported table' key = Cells(i, "section")&Cells(i, "material") found = False For j = 2 To dictLastRow 'Traverse the material reference library If the key is like "*"&dict(j, "Specifications")&"*", then... out(i, "material code") = dict(j, "material code") out(i, "theoretical weight") = dict(j, "theoretical weight") found = True: Exit For End If Next j If Not found Then Rows(i).Interior.Color = vbYellow Next i.

[0032] Second VBA program (plan / arrival matching and deduction): KeyCols = Array("Material Code","Specifications","Material","Manufacturer") For each planRow in planTbl For Each arrivalRow In arrivalTbl If planRow(KeyCols) = arrivalRow(KeyCols) Then planRow("Theoretical arrival weight") = planRow("Theoretical arrival weight") + arrivalRow("Theoretical weight") arrivalRow("Statistics Results") = "Matched" Exit For End If Next Next planRow("Theoretical Shortage") = planRow("Planned Quantity") - planRow("Theoretical Delivery Weight").

[0033] Group 3 VBA programs (summarized by project number): Set dict = CreateObject("Scripting.Dictionary") For each row, in detailTbl key = row("Plan Number")&row("Material Code") dict(key)("planned quantity") = dict(key)("planned quantity") + row("planned quantity") dict(key)("theoretical arrival weight") = dict(key)("theoretical arrival weight") + row("theoretical arrival weight") Next Output dict to table 10, and calculate dict(key)("theoretical shortage").

[0034] Group 4 VBA programs (summarized by system, importing model quantities): For each row in tbl10 modelNet = DLookup("Model Net Weight", tbl5, "Material Code='"&row("Material Code")&"'") modelGross = DLookup("Model Gross Weight", tbl5, "Material Code='"&row("Material Code")&"'") row("Net Model Quantity - Arrival Quantity") = modelNet - row("Theoretical Arrival Weight") row("Model Gross Weight - Arrived Quantity") = modelGross - row("Theoretical Arrival Weight") If row("Model Gross Weight - Arrival Quantity") > 0, then row.Font.Color = vbRed Next.

[0035] Fifth VBA program (Calculating area using a single-lens reflex camera): For each row in tbl12 If row("Model Gross Weight - Arrival Quantity")>0 Then ton = row("Model Gross Weight - Quantity Arrived") t = GetThickness(row("specification")) area = ton / (7.85 * t / 1000) 'Unit: m 2 AppendToBudget row, area 'Write to table 14 End If Next.

[0036] Group 6 VBA program (arrival rate and weight deviation): For each planNo, use the unique(planTbl, "plan number") planSum = SumIf(planTbl, "Planned Quantity", "Planned Order Number='"&planNo&"'") arrivalSum = SumIf(arrivalTbl, "Weighing weight", "Plan number='"&planNo&"'") arrivalRate = arrivalSum / planSum WriteToTable28 planNo, arrivalRate Next.

[0037] Through the steps of Embodiments 1 and 2 above, the present invention can complete the entire process of "translation-matching-reduction-early warning-budgeting-statistics" of 100,000 rows of data in 18 minutes on an ordinary office computer, without the need for additional software and hardware investment, and realize "one-click closed loop" of steel structure material management.

[0038] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention in any way. Although the present invention has been disclosed above with reference to preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art can make some modifications or alterations to the above-disclosed technical content to create equivalent embodiments without departing from the scope of the present invention. Any simple modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention without departing from the scope of the present invention shall still fall within the scope of the present invention.

Claims

1. A one-click management method for the entire process of steel structure materials based on Tekla and VBA, characterized in that, Includes the following steps: S1. Establish a maintainable material reference database, which includes at least a flat steel specification database, a profile name database, a plate name database, and a corresponding theoretical weight table; S2. Use the Tekla plugin interface to export a parts list from the Tekla structural model in one go. The parts list shall include at least the parts number, material specifications, material, net weight, gross weight, usage location, drawing number and manufacturer field. S3. Import the parts list into the Excel master control file, and automatically translate the material codes and assign theoretical weights by referring to the material reference database using the first set of VBA macro instructions to generate a standard model quantity data table; S4. Enter or update the planned quantity data table and the arrival quantity data table in the Excel master control file respectively; S5. The VBA macro instructions executed sequentially from the second to the sixth group are used to complete the following: i. Match and deduct planned quantities from delivered quantities line by line to generate a summary of material shortage / overconsumption results by planned order number; ii. The net weight and gross weight of the model are deducted from the planned quantity and the delivered quantity, respectively, and a comparison table summarized by system is generated; iii. A comparison table compiled by the manufacturer; iv. Automatically generate budget sheets for sheet materials and profiles, and calculate the area or length accordingly; v. Statistics on the overall delivery rate and weight deviation rate of the project; S6. The six sets of VBA macro instructions are executed continuously by triggering the one-click run button, realizing one-click refresh of the entire process of "design quantity, planned quantity, delivery quantity, and cost quantity".

2. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The material reference database is stored in the form of an Excel worksheet. Row records can be added or deleted at will, and column fields must include at least "material code, specification description, theoretical weight, and standard number".

3. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The parts list output by the Tekla plugin interface is in CSV or Excel format, and the plugin automatically adds the fields "location of use, drawing number, manufacturer" when exporting. Missing fields are left blank and can be filled in by the user later.

4. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The planned quantity data table and the delivered quantity data table are different worksheets in the same Excel workbook. The column names of the worksheets correspond one-to-one with the field names hard-coded by the VBA macro instructions to ensure that the deduction formula is automatically matched.

5. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The six groups of VBA macro instructions adopt a sequential calling mechanism, with the output table of the previous group serving as the input table of the next group. After each group is executed, a "executed" flag is written in the status area to prevent repeated execution or skipped execution.

6. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, During the deduction process, the VBA macro instruction automatically marks unmatched rows as "unrecognized" and prompts the user to add the corresponding material code in the material reference database before running it again.

7. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The comparison table summarized by system shall include at least the fields of "material category, system number, specification, material, net quantity of model, gross weight of model, planned quantity, delivered quantity, net quantity of model minus delivered quantity, and gross weight of model minus delivered quantity", and shall highlight in red when the value is positive to indicate material shortage or overconsumption.

8. The one-click management method for the entire process of steel structure materials based on Tekla and VBA as described in claim 1, characterized in that, The budget sheet calculates the area or length by "theoretical weight × quantity" and automatically converts it into the usual purchasing units, which include square meters, meters, pieces, and sheets.

9. A one-click management system for the entire process of steel structure materials based on Tekla and VBA, characterized in that, include:    a) Material reference database module, used to store flat steel specification library, profile / plate name library and theoretical weight table;    b) Tekla plugin interface module, used to export a parts list containing fields such as "location of use, drawing number, manufacturer, net weight, and gross weight" in one go; c) The planned quantity entry module and the delivered quantity entry module generate planned quantity data tables and delivered quantity data tables, respectively;    d) An Excel master control file containing six sets of sequentially executed VBA macro commands, used to complete material code translation, multi-level deduction, summarization, and budget output; e) The one-click run button is used to trigger the continuous execution of the six sets of VBA macro instructions, realizing one-click refresh of the entire process.

10. The one-click management system for the entire process of steel structure materials based on Tekla and VBA as described in claim 9, wherein the Excel master control file automatically saves a timestamped copy after the macro execution is completed, and locks the screen refresh to improve operational stability.

Images