|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
我的OFFICE是64位,所以感觉代码很长,主要是要处理一下64位环境下ScriptControl 的调用问题
- Option Explicit
- Dim sh As Worksheet
- Dim arrRule As Variant
- Dim arrSource As Variant
- Sub Test()
- Dim oSC As Object
- Dim lngRow As Long, lngRow2 As Long
- Dim strTemp As String, strResult As String, arrResult As Variant
- InicData
- ReDim arrResult(LBound(arrSource) To UBound(arrSource), 1 To 2)
- Set oSC = CreateObjectx86("MSScriptControl.ScriptControl")
- oSC.Language = "VBScript"
-
- For lngRow = LBound(arrSource) To UBound(arrSource)
- strResult = ""
- For lngRow2 = LBound(arrRule) To UBound(arrRule)
- arrResult(lngRow, 1) = arrSource(lngRow, 1)
- strTemp = arrRule(lngRow2, 2)
- strTemp = Replace(strTemp, "@A@", arrSource(lngRow, 2))
- strTemp = Replace(strTemp, "@B@", arrSource(lngRow, 3))
- strTemp = Replace(strTemp, "@C@", arrSource(lngRow, 4))
- strTemp = Replace(strTemp, "@D@", arrSource(lngRow, 5))
- If oSC.Eval(strTemp) = True Then strResult = strResult & "," & arrRule(lngRow2, 1)
- Next
- arrResult(lngRow, 2) = Mid(strResult, 2)
- Next
-
- CreateObjectx86 , True
-
- sh.Range("B30").Resize(UBound(arrResult), 2) = arrResult
- End Sub
- Sub InicData()
- Dim strVal As String
- Dim lngRow As Long, lngCol As Long
-
- Set sh = Sheets("数据")
- arrRule = sh.Range("A3:B9")
- arrSource = sh.Range("E3:I7")
-
- For lngRow = LBound(arrRule) To UBound(arrRule)
- strVal = arrRule(lngRow, 2)
- strVal = UCase(Trim(strVal))
- strVal = Replace(strVal, "A", "@A@")
- strVal = Replace(strVal, "B", "@B@")
- strVal = Replace(strVal, "C", "@C@")
- strVal = Replace(strVal, "D", "@D@")
- strVal = Replace(strVal, "/", " Or ")
- strVal = Replace(strVal, "&", " And ")
- strVal = Replace(strVal, "|", " Or ")
- strVal = Replace(strVal, "-", " Not ")
- arrRule(lngRow, 2) = strVal
- Next
-
- For lngRow = LBound(arrSource) To UBound(arrSource)
- For lngCol = LBound(arrSource, 2) + 1 To UBound(arrSource, 2)
- strVal = arrSource(lngRow, lngCol)
- strVal = UCase(Trim(strVal))
- If strVal = "X" Then
- strVal = "True"
- Else
- strVal = "False"
- End If
- arrSource(lngRow, lngCol) = strVal
- Next
- Next
-
- sh.Range("K2").Resize(UBound(arrRule), UBound(arrRule, 2)) = arrRule
- sh.Range("N2").Resize(UBound(arrSource), UBound(arrSource, 2)) = arrSource
-
- End Sub
- Function CreateObjectx86(Optional sProgID, Optional bClose = False)
- Static oWnd As Object
- Dim bRunning As Boolean
- #If Win64 Then
- bRunning = InStr(TypeName(oWnd), "HTMLWindow") > 0
- If bClose Then
- If bRunning Then oWnd.Close
- Exit Function
- End If
- If Not bRunning Then
- Set oWnd = CreateWindow()
- oWnd.execScript "Function CreateObjectx86(sProgID): Set CreateObjectx86 = CreateObject(sProgID): End Function", "VBScript"
- End If
- Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID)
- #Else
- Set CreateObjectx86 = CreateObject("MSScriptControl.ScriptControl")
- #End If
- End Function
- Function CreateWindow()
- Dim sSignature, oShellWnd, oProc
- On Error Resume Next
- sSignature = Left(CreateObject("Scriptlet.TypeLib").GUID, 38)
- CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""about:<head><script>moveTo(-32000,-32000);document.title='x86Host'</script><hta:application showintaskbar=no /><object id='shell' classid='clsid:8856F961-340A-11D0-A96B-00C04FD705A2'><param name=RegisterAsBrowser value=1></object><script>shell.putproperty('" & sSignature & "',document.parentWindow);</script></head>""", 0, False
- Do
- For Each oShellWnd In CreateObject("Shell.Application").Windows
- Set CreateWindow = oShellWnd.GetProperty(sSignature)
- If Err.Number = 0 Then Exit Function
- Err.Clear
- Next
- Loop
- End Function
复制代码 |
|