VBA 从一个excel读取特定行，列到指定excel

最新推荐文章于 2025-06-19 12:46:35 发布

原创

最新推荐文章于 2025-06-19 12:46:35 发布 · 1.5w 阅读

11 ·

CC 4.0 BY-SA版权

这个VBA代码段实现了从Excel文件中读取任务ID、子任务ID、任务所有者和子任务所有者信息。它首先根据文件名解析信息，然后在工作簿的"Get Task Time"工作表中填充这些数据。接下来，代码会查找"Name list"工作表中匹配的名字，用实际显示名称替换。根据文件类型，它会从"Task Summary"或"SubTask Summary"工作表复制数据到"Get Task Time"。最后，通过遍历指定文件夹中的所有.xlsx文件，将数据逐行写入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Private Function splitExcelName(row, myFileName) As String
'This function will get task ID, subtask ID, task owner or subtask owner from the excel file name
Dim colTaskID, colSubtaskID, colTaskOwner, colSubtaskOwner
Dim substring, namestring

    'init column
    colTaskID = 1
    colSubtaskID = 2
    colTaskOwner = 3
    colSubtaskOwner = 4

    rawName = 2 'the start row for the same name in excel file
    colName = 1 'the column for the same name in excel file
    colActualName = 2 'the column for the actual name show out

    substring = Split(myFileName, "-")

    Worksheets("Get Task Time").Cells(row, colTaskID) = substring(1) 'get task ID
    Worksheets("Get Task Time").Cells(row, colSubtaskID) = substring(3) 'get subtask ID
    namestring = Split(substring(4), ".") 'Get task owner or subtask owner name
    subowner = namestring(0)

    'get the line number from Name list sheet
    nameList = Worksheets("Name list").Cell