Excel编程(2)----自动填充
话说懒惰是进步的动力源泉。 现在有这样一个需求:一个主表数据文件(MainData.xls), 内部有5个sheet(sheet1,sheet2,sheet3,sheet4,sheet5),另外有5个数据源文件
(SrcData1.xls, SrcData2.xls, SrcData3.xls, SrcData4.xls, SrcData5.xls),以上5个Sheet和 5个数据源文件是一一对应的, 任务是把5个数据源文件的中的数据分别填充到5个sheet指定的单元格中(匹配信息吻合)。来看代码:
Function FillDataAuto() Dim TotalRow As Integer Dim CurRow As Integer Dim CurColumn As Integer Dim SheetIndex&, RowIndex1&, RowIndex2& Dim cls, cls1, cls2, cls3, CurreSheet As String Dim ColumnC, ColumnJ, ColumnF, ColumnL As Integer Dim SheetNameArr(5) Dim SrcFileArr(5) ColumnB = 2 ColumnC = 3 ColumnE = 5 ColumnF = 6 ColumnH = 8 ColumnJ = 10 ColumnK = 11 ColumnL = 12 SheetNameArr(1) = "Sheet1" SheetNameArr(2) = "Sheet2" SheetNameArr(3) = "Sheet3" SheetNameArr(4) = "Sheet4" SheetNameArr(5) = "Sheet5" SrcFileArr(1) = "SrcData1.xls" SrcFileArr(2) = "SrcData2.xls" SrcFileArr(3) = "SrcData3.xls" SrcFileArr(4) = "SrcData4.xls" SrcFileArr(5) = "SrcData5.xls" TotalRow = [a65536].End(xlUp).Row 'Scan every Sheet For SheetIndex = 1 To 5 Step 1 'Scan every row(从第8行开始) For RowIndex1 = 8 To TotalRow Step 1 CurreSheet = SheetNameArr(SheetIndex) '获取MainData.xls中的标识码(保存在各个Sheet的第三列(ColumnC)) cls = ActiveWorkbook.Sheets(CurreSheet).Cells(RowIndex1, ColumnC).Value For RowIndex2 = 1 To TotalRow '获取SrcData.xls文件中的标识码(保存在sheet为SrcData,第二列(ColumnB)的单元格中) cls1 = Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnB) '判断是否为空以及相等与否 If cls1 <> "" And cls = cls1 Then '读取二重标识码 cls2 = Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnC) cls3 = ActiveWorkbook.Sheets(CurreSheet).Cells(RowIndex1, ColumnE).Value If cls2 <> cls3 Then MsgBox "row(" & RowIndex2 & ")has error!" Else '二重标识码匹配的话,将SrcData.xls文件中第ColumnK列的数据填充到MainData.xls中对应的sheet中的ColumnF列中 ActiveWorkbook.Sheets(SheetNameArr(SheetIndex)).Cells(RowIndex1, ColumnF) = _ Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnK) End If End If Next RowIndex2 Next RowIndex1 Next SheetIndex MsgBox "Data updated!" End Function
手动输入的话工作量大且容易出错,用代码来实现的话只要半分钟即可完成任务。