|
- Sub 导入日期()
- Dim wb As Workbook
- Dim sPath As String
- Dim sFilename As String
- Dim sWorkbookname As String
- Dim arr, dic, arrRst
- Dim irow, i, j, k, sKey As String
- Dim arrCol
- Dim test1, test2
- On Error Resume Next
- Application.ScreenUpdating = False
- sFilename = "生产计划明细表.xls"
- sPath = ThisWorkbook.Path & "" & sFilename
- sWorkbookname = "生产计划明细表"
- If Dir(sPath) = "" Then
- MsgBox sFilename & "文件不存在"
- Exit Sub
- End If
- Set wb = Workbooks.Item(sFilename)
- If wb Is Nothing Then
- Workbooks.Open Filename:=sPath, ReadOnly:=True
- Worksheets(1).Activate
- Else
- Workbooks(sFilename).Activate
- Worksheets(1).Activate
- End If
- 'arr = Range("a3").CurrentRegion '方法一取当前区域
- irow = Range("a3").End(xlDown).Row '方法二,先旧最后一行数据行,然后再整行
- arr = Range("a4:q" & irow)
- ReDim arrRst(1 To UBound(arr), 1 To 1) '根据行数重新定义数组大小
- Set dic = CreateObject("scripting.dictionary")
- arrCol = Array(1, 2, 3, 7, 9, 10)
- For i = 1 To UBound(arr)
- sKey = ""
- For j = 0 To UBound(arrCol)
- sKey = sKey & arr(i, arrCol(j)) & "|"
- Next
- dic(sKey) = arr(i, 15)
- 'Debug.Print sKey & " " & dic(sKey)
- Next
- Erase arr
- ActiveWorkbook.Close SaveChanges:=False
- ThisWorkbook.Activate
- Worksheets("12345").Select
- 'arr = Range("a3").CurrentRegion '方法一取当前区域
- irow = Range("a3").End(xlDown).Row '方法二,先旧最后一行数据行,然后再整行
- arr = Range("a4:l" & irow)
- arrCol = Array(6, 7, 8, 1, 2, 10)
- For i = 1 To UBound(arr)
- sKey = ""
- For j = 0 To UBound(arrCol)
- sKey = sKey & arr(i, arrCol(j)) & "|"
- Next
- arrRst(i, 1) = Format(dic(sKey), "M月d日")
- 'Debug.Print sKey & " " & Format(dic(sKey), "M月d日")
- Next
-
- Range("k4").Resize(dic.Count, 1) = arrRst
- End Sub
复制代码 |
|