求助：如何用Excel自动比对文件树与BOM清单查找缺失CAD文件

Absolutely! Manual checking 8000+ parts sounds brutal—automation is exactly what you need here. I’ve got two straightforward solutions for you, depending on whether you want to use Python (super flexible for future tweaks) or VBA (built right into Excel, no extra software needed):

Python is perfect if you want to handle complex filename matching or scale this task later. Here’s how to do it:

Step 1: Prep #

• Install pandas (for Excel handling) if you haven’t: open command prompt and run pip install pandas
• Note your Excel BOM path, CAD folder path, and the column name where your part numbers are stored.

Step 2: Run the Script #

Copy this code into a .py file, adjust the paths/column names to match your setup, and run it:

import pandas as pd
import os

# 1. Load your BOM Excel file
df = pd.read_excel("你的BOM文件路径.xlsx", sheet_name="Sheet1")
# Replace "零件号" with your actual part number column name
part_num_col = "零件号"
part_numbers = df[part_num_col].tolist()

# 2. Scan all CAD files in your target folder (including subfolders)
cad_root_folder = r"C:\你的CAD文件根目录路径"
existing_parts = set()

# Define which CAD file extensions to look for (add/remove as needed)
cad_extensions = (".dwg", ".prt", ".sldprt", ".step", ".iges")

for root, _, files in os.walk(cad_root_folder):
    for file in files:
        if file.lower().endswith(cad_extensions):
            # Extract the part number from the filename (remove file extension)
            part_name = os.path.splitext(file)[0]
            existing_parts.add(part_name)

# 3. Mark if each part has a CAD file in Excel
df["CAD文件存在"] = df[part_num_col].apply(lambda x: "是" if x in existing_parts else "否")

# 4. Save the updated BOM
df.to_excel("带检查结果的BOM.xlsx", index=False)

Tips for This Script #

• If part numbers in Excel have extra characters (like prefixes/suffixes) that aren’t in filenames, add string cleaning logic (e.g., x.strip() or x.replace("-", "")) in the apply function.
• The script scans all subfolders automatically—no need to manually check each directory.

If you don’t want to mess with Python, use VBA directly in Excel. Here’s how:

Step 1: Open VBA Editor #

• Open your BOM Excel file, press Alt + F11 to open the VBA editor.
• Right-click your workbook in the Project Explorer > Insert > Module.

Step 2: Paste the Code #

Copy this code into the module, adjust the folder path and column references:

Sub CheckCADFilesAutomatically()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim partNumber As String
    Dim cadBaseFolder As String
    
    ' --- Adjust these settings to match your file ---
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Your sheet name
    cadBaseFolder = "C:\你的CAD文件根目录路径\" ' End with a backslash
    ' ---
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Assume part numbers are in column A
    
    ' Loop through each part number (skip header row, start at row 2)
    For i = 2 To lastRow
        partNumber = ws.Cells(i, "A").Value
        ' Check if the part exists in any subfolder with common CAD extensions
        If FileExistsInSubfolders(cadBaseFolder, partNumber) Then
            ws.Cells(i, "B").Value = "是"
        Else
            ws.Cells(i, "B").Value = "否"
        End If
    Next i
    
    MsgBox "CAD文件检查完成！结果已写入B列。", vbInformation
End Sub

' Helper function to scan subfolders for the part file
Function FileExistsInSubfolders(folderPath As String, partNumber As String) As Boolean
    Dim subFolder As String
    Dim fileName As String
    
    ' Check current folder for any file starting with the part number (matches any extension)
    fileName = Dir(folderPath & partNumber & ".*")
    If fileName <> "" Then
        FileExistsInSubfolders = True
        Exit Function
    End If
    
    ' Recursively check all subfolders
    subFolder = Dir(folderPath, vbDirectory)
    Do While subFolder <> ""
        If subFolder <> "." And subFolder <> ".." Then
            If (GetAttr(folderPath & subFolder) And vbDirectory) = vbDirectory Then
                If FileExistsInSubfolders(folderPath & subFolder & "\", partNumber) Then
                    FileExistsInSubfolders = True
                    Exit Function
                End If
            End If
        End If
        subFolder = Dir
    Loop
    
    FileExistsInSubfolders = False
End Function

Step 3: Run the Macro #

• Press F5 while in the VBA editor, or go back to Excel and run the macro via the Developer tab > Macros > Select CheckCADFilesAutomatically > Run.

Tips for VBA #

• If you only need to check the top-level folder (not subfolders), you can simplify the code by removing the helper function and using Dir(cadBaseFolder & partNumber & ".dwg") directly.
• The helper function matches any file extension starting with your part number—if you need to limit to specific extensions, modify the fileName = Dir(...) line to list them explicitly (e.g., Dir(folderPath & partNumber & ".dwg") then check .prt, etc.).

Either solution will cut your 8000-part check down to minutes instead of days. Pick the one that feels more comfortable for your setup!

内容的提问来源于stack exchange，提问作者Astarngo