请求协助编写Excel脚本：提取表单信息至文件目录工作表A列

I’ve put together a VBA script that pulls the main folder name from your form’s A9 cell and lists it alongside your specified subfolders in column A of the "文件目录" worksheet. Here’s the solution tailored to your needs:

Sub ExtractFolderInfo()
    Dim formSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim mainFolderName As String
    Dim subFolders As Variant
    Dim i As Integer
    
    ' Set references to your form and target sheets (update names if needed)
    On Error Resume Next
    Set formSheet = ThisWorkbook.Worksheets("表单") ' Replace with your actual form sheet name
    Set targetSheet = ThisWorkbook.Worksheets("文件目录")
    On Error GoTo 0
    
    ' Check if both sheets exist
    If formSheet Is Nothing Or targetSheet Is Nothing Then
        MsgBox "One or more required worksheets not found. Please verify sheet names!", vbExclamation
        Exit Sub
    End If
    
    ' Get main folder name from A9
    mainFolderName = formSheet.Range("A9").Value
    If mainFolderName = "" Then
        MsgBox "Main folder name (cell A9) is empty!", vbExclamation
        Exit Sub
    End If
    
    ' Define your subfolder list (add/remove items as needed)
    subFolders = Array("产品数据", "施工图", "项目文档") ' Customize this array to match your subfolder types
    
    ' Clear existing data in column A (optional, remove if you want to keep old entries)
    targetSheet.Range("A:A").ClearContents
    
    ' Write main folder name to first row of target column
    targetSheet.Range("A1").Value = mainFolderName
    
    ' Write subfolders to subsequent rows
    For i = LBound(subFolders) To UBound(subFolders)
        ' Option 1: Full path format (e.g., MainFolder/产品数据)
        ' targetSheet.Range("A" & i + 2).Value = mainFolderName & "/" & subFolders(i)
        
        ' Option 2: Just subfolder names (uncomment below if this is what you need)
        targetSheet.Range("A" & i + 2).Value = subFolders(i)
    Next i
    
    MsgBox "Folder information extracted successfully!", vbInformation
End Sub

Key Customizations to Make: #

• Sheet Names: Update "表单" to the actual name of your form worksheet if it’s different.
• Subfolder List: Modify the subFolders array to include all the subfolder types you require (add or remove entries as necessary).
• Path Format: Choose between full paths or just subfolder names by commenting/uncommenting the relevant line in the loop.

How to Use: #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module).
4. Paste the code above into the module.
5. Adjust the customizations mentioned above.
6. Run the macro (Press F5 while in the module, or assign it to a button in Excel for easier access).

This script will populate your "文件目录" sheet with the exact folder structure you need, ready to feed into your existing directory creation script.

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