|
- Sub 读取sku库存()
- Dim strPath As String, strFileName As String
- Dim shWISH As Worksheet, shSFO As Worksheet, objWB As Object
- Dim arrWISH As Variant, arrSFO As Variant
- Dim objDic As Object, strKey As String
- Dim lngRows As Long, lngID As Long
-
- strPath = ThisWorkbook.Path & ""
- strFileName = "sfo库存.xls"
-
- Set objDic = CreateObject("scripting.dictionary")
-
- Set shWISH = Sheets("WISH 分仓模板")
- lngRows = shWISH.Range("E" & Rows.Count).End(xlUp).Row
- arrWISH = shWISH.Range("E2:E" & lngRows)
- For lngID = LBound(arrWISH) To UBound(arrWISH)
- strKey = arrWISH(lngID, 1)
- objDic(strKey) = ""
- Next
-
- Set objWB = GetObject(strPath & strFileName)
- Set shSFO = objWB.Worksheets(1)
- lngRows = shSFO.UsedRange.Rows.Count
- arrSFO = shSFO.Range("A2:F" & lngRows)
- objWB.Close False
-
- For lngID = LBound(arrSFO) To UBound(arrSFO)
- strKey = arrSFO(lngID, 1)
- If objDic.exists(strKey) Then
- objDic(strKey) = Val(objDic(strKey)) + Val(arrSFO(lngID, 6))
- End If
- Next
-
- For lngID = LBound(arrWISH) To UBound(arrWISH)
- strKey = arrWISH(lngID, 1)
- If objDic(strKey) <> "" Then
- arrWISH(lngID, 1) = objDic(strKey)
- Else
- arrWISH(lngID, 1) = ""
- End If
- Next
-
- shWISH.Range("W2").Resize(UBound(arrWISH), 1) = arrWISH
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|