基于Excel物料号（p/n's）清单自动收集.dxf/.pdf文件的技术咨询

Absolutely! You can totally automate searching and collecting those .dxf and .pdf files based on your Excel part number list—this will cut out all that tedious manual work. Here are two practical, straightforward solutions you can implement right now:

1. Excel VBA Macro (No Extra Tools Required) #

Since you're already working in Excel, a VBA macro is the most direct way to get this done. It runs right inside your spreadsheet, no need to install anything else. Here's how:

Step-by-Step Setup: #

• Open your Excel part list, assuming your part numbers are in column A (starting at row 2, with row 1 as the header).
• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste one of the code snippets below, then tweak the folder paths to match your system.

Basic Version (Searches Top-Level Folder Only)

This works if all your .dxf/.pdf files are in a single folder (no subfolders):

Sub CollectPartFiles()
    Dim sourceFolder As String, targetFolder As String
    Dim partNum As String, fileName As String
    Dim lastRow As Long, i As Long
    
    ' Update these paths to match your system
    sourceFolder = "C:\Your\Source\Files\Folder\"
    targetFolder = "C:\Your\Target\Collection\Folder\"
    
    ' Create target folder if it doesn't exist
    If Dir(targetFolder, vbDirectory) = "" Then
        MkDir targetFolder
    End If
    
    ' Get the last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each part number
    For i = 2 To lastRow
        partNum = Trim(Cells(i, "A").Value)
        If partNum <> "" Then
            ' Search for matching .dxf files
            fileName = Dir(sourceFolder & "*" & partNum & "*.dxf", vbDirectory)
            Do While fileName <> ""
                FileCopy sourceFolder & fileName, targetFolder & fileName
                fileName = Dir() ' Check next matching file
            Loop
            
            ' Search for matching .pdf files
            fileName = Dir(sourceFolder & "*" & partNum & "*.pdf", vbDirectory)
            Do While fileName <> ""
                FileCopy sourceFolder & fileName, targetFolder & fileName
                fileName = Dir()
            Loop
        End If
    Next i
    
    MsgBox "File collection complete!", vbInformation
End Sub

Advanced Version (Searches Subfolders Too)

Use this if your files are scattered across nested subfolders:

Sub CollectPartFilesWithSubfolders()
    Dim sourceFolder As String, targetFolder As String
    Dim lastRow As Long, i As Long
    
    ' Update these paths
    sourceFolder = "C:\Your\Source\Files\Folder\"
    targetFolder = "C:\Your\Target\Collection\Folder\"
    
    If Dir(targetFolder, vbDirectory) = "" Then MkDir targetFolder
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        partNum = Trim(Cells(i, "A").Value)
        If partNum <> "" Then
            ' Recursively search subfolders
            SearchAndCopy sourceFolder, partNum, targetFolder
        End If
    Next i
    
    MsgBox "File collection complete!", vbInformation
End Sub

Private Sub SearchAndCopy(folderPath As String, partNum As String, targetPath As String)
    Dim fileName As String, subFolder As String
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Search current folder for matches
    fileName = Dir(folderPath & "*" & partNum & "*.dxf")
    Do While fileName <> ""
        FileCopy folderPath & fileName, targetPath & fileName
        fileName = Dir()
    Loop
    
    fileName = Dir(folderPath & "*" & partNum & "*.pdf")
    Do While fileName <> ""
        FileCopy folderPath & fileName, targetPath & fileName
        fileName = Dir()
    Loop
    
    ' Recurse into subfolders
    subFolder = Dir(folderPath, vbDirectory)
    Do While subFolder <> ""
        If subFolder <> "." And subFolder <> ".." Then
            If fso.FolderExists(folderPath & subFolder) Then
                SearchAndCopy folderPath & subFolder & "\", partNum, targetPath
            End If
        End If
        subFolder = Dir()
    Loop
End Sub

2. Python Script (Flexible & Scalable) #

If you have a bit of Python experience, this method offers more flexibility for complex matching rules (like case-insensitive searches or exact filename matches). Here's a simple script:

Setup: #

• Install pandas if you haven't already: run pip install pandas in your command prompt.
• Create a new .py file and paste the code below, adjusting the paths to fit your system.

import os
import shutil
import pandas as pd

def collect_part_files(excel_path, source_dir, target_dir):
    # Read Excel file (part numbers in column A, header in row 1)
    df = pd.read_excel(excel_path)
    part_numbers = df.iloc[:, 0].dropna().str.strip().tolist()
    
    # Create target folder if it doesn't exist
    os.makedirs(target_dir, exist_ok=True)
    
    # Walk through all files and subfolders
    for root, _, files in os.walk(source_dir):
        for file in files:
            # Check if file is .dxf/.pdf and contains a part number
            if file.lower().endswith(('.dxf', '.pdf')):
                for part in part_numbers:
                    if part in file:
                        source_file_path = os.path.join(root, file)
                        target_file_path = os.path.join(target_dir, file)
                        # Skip if file already exists in target
                        if not os.path.exists(target_file_path):
                            shutil.copy2(source_file_path, target_file_path)
                        break  # Stop checking other parts once a match is found

# Configure your paths here
EXCEL_FILE = "C:\\Your\\Part\\List\\File.xlsx"
SOURCE_FOLDER = "C:\\Your\\Source\\Files\\Folder\\"
TARGET_FOLDER = "C:\\Your\\Target\\Collection\\Folder\\"

# Run the function
collect_part_files(EXCEL_FILE, SOURCE_FOLDER, TARGET_FOLDER)
print("File collection complete!")

Quick Tweaks for Your Use Case: #

• Exact Match: If you need the filename to start with the part number, replace if part in file: with if file.startswith(part):.
• Case Insensitivity: Use if part.lower() in file.lower(): to ignore uppercase/lowercase differences.

Important Notes: #

• Filename Clarity: Make sure your part numbers are clearly present in the .dxf/.pdf filenames (e.g., ABC123_drawing.dxf) to avoid false matches.
• Permissions: Ensure you have read access to the source folder and write access to the target folder.
• Duplicate Files: Both solutions skip copying files that already exist in the target folder. If you need to handle duplicates (e.g., rename them), you can add logic to append a number to the filename.

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