|
本帖最后由 盼哥 于 2019-11-9 08:08 编辑
- Option Explicit
- Dim i&
- Sub 导入()
-
- Dim maxRow&, p$, fName$
- With ThisWorkbook
- p = .Path
- With .ActiveSheet
- maxRow = .Cells(.Rows.Count, 4).End(xlUp).Row 'D列单元格的最大行
- End With
- End With
- Dim sBook As Workbook
- Dim fullName$, fileName$
- p = ThisWorkbook.Path
- fileName = Dir(p & "\*.xls*")
- Do While fileName <> ""
- If fileName <> ThisWorkbook.Name Then
- fullName = p & "\" & fileName
- Set sBook = Workbooks.Open(fullName)
- Call readExBook(sBook, maxRow)
- maxRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row 'D列单元格的最大行
- End If
- fileName = Dir
- Loop
- End Sub
- Sub readExBook(sBook, maxRow)
- Dim dataRowsCount&
- Dim arr, sArr, j&, k&
- With sBook.ActiveSheet
- dataRowsCount = WorksheetFunction.Max(.Range("B13:B26"))
- sArr = Range(sBook.ActiveSheet.Cells(13, "A"), sBook.ActiveSheet.Cells(13 + dataRowsCount - 1, "N"))
- ReDim arr(1 To dataRowsCount, 1 To [Z1].Column)
- For i = 1 To UBound(arr)
- arr(i, 1) = sArr(i, 1) '编号
- arr(i, 2) = .Range("B1") '订单号
- arr(i, 3) = .Range("K2") '日期
- arr(i, 4) = .Range("B5") '单位名称
- arr(i, 5) = .Range("B6") '单位地址
- arr(i, 6) = .Range("E5") '联系人
- arr(i, 7) = .Range("E6") '电话
- arr(i, [H1].Column) = .Range("E2") '服务单位名称
- arr(i, [I1].Column) = .Range("I6") '服务单位地址
- arr(i, [J1].Column) = .Range("N5") '服务单位业务员
-
- '明细表
- For j = 3 To UBound(sArr, 2)
- arr(i, [O1].Column + j - 3) = sArr(i, j)
- Next j
- Next i
-
- End With
- sBook.Close False
- ThisWorkbook.Sheets("数据库").Cells(maxRow + 1, 1).Resize(UBound(arr), UBound(arr, 2)) = arr
- End Sub
复制代码 |
|