Windows 10 and Office Excel

**DocAElstein** · 02-08-2018, 12:53 AM

Coding idea to tie down / compare a bit more precisely the actual files

A basic code idea.
I am comparing the files found from Device Manager Driver details properties to those in the full list obtained from the Double Driver software.
I will try to find the each of the files in the files found from Device Manager Driver details properties in the full list obtained from the Double Driver software

I will make a selection in the worksheet , DeviceManagerProperties , to include all the files of interest, so this would typically be a selection to include all the files listed
Comparing Device Drivers Selection.JPG : https://imgur.com/GSjjdod
Comparing Device Drivers Selection.JPG

_____ Workbook: ExplorerViewDriversBefore.xlsm ( Using Excel 2007 32 bit )

Audio, Video und Gamecontroller
	High definition Audio-Gerät
			C:\Windows\system32\DRIVERS\dmk.sys
			C:\Windows\system32\DRIVERS\HdAudio.sys
			C:\Windows\system32\drivers\ksthunk.sys
			C:\Windows\system32\DRIVERS\portcls.sys
		C:\Windows\system32\SysFxUl.dll
		C:\Windows\system32\WMALFXGFXDSP.dll

Worksheet: DeviceManagerProperties

The macro should then take each cell in the selection, check for a file in the cell, then if found it will look for that file in all the columns containing files in the Double Driver All Devices worksheet, DDAllBefore

I have the coding for convenience in the Worksheets code module of worksheet DeviceManagerProperties
So any undefined Range references will refer to that worksheet. But I will try to avoid relying on that in case I copy the code , or parts thereof, elsewhere later
I will not make any attempt to do an efficient macro: I will select and color matching pairs in the two worksheets as I go along. If it goes a bit slow that can be helpful for seeing where things are
Here is a coding example:

Code:

Sub CompareDriverFilesDeviceManagerInDoubleDriverAllList()
Rem 0
    If ActiveSheet.Name <> "DeviceManagerProperties" Then
     MsgBox prompt:="Oops": Exit Sub
    Else
    End If
Rem 1 Worksheets info
Dim WsDMP As Worksheet, WsDDA As Worksheet
 Set WsDMP = Worksheets("DeviceManagerProperties"): Set WsDDA = Worksheets("DDAllBefore")

Rem 2 Looking at each cell in the selection
' Random number between 3 and 56 to get color index for any matching file names (1 is black, 2 is white , up to 56 is other colors:  3 to 56   is like  (0 to 53)+3  Rnd gives like  0-.99999  so (Int(Rnd*54))+3  is what we want
Dim ClrIdx As Long
 Randomize: Let ClrIdx = (Int(Rnd * 54)) + 3

Dim SrchForCel As Range
    For Each SrchForCel In Selection ' Take each cell in selected range
    Dim CelVl As String: Let CelVl = SrchForCel.Value
        If CelVl <> "" And Left(CelVl, 3) = "C:\" And InStr(4, CelVl, ".", vbBinaryCompare) > 1 Then ' use some criteria to check we have a file path
        Dim FileNmeSrchFor As String
         Let FileNmeSrchFor = Right(CelVl, (Len(CelVl)) - (InStrRev(CelVl, "\", -1, vbBinaryCompare))) ' Determine the file name as that looking from the right as many characters as (the total character number) - (the position looking from the right of a "\")
        Rem 3 We now should have a file name, so we look for it in worksheet  DDAllBefore
        Dim SrchRng As Range: Set SrchRng = Application.Range("=DDAllBefore!F5:DDAllBefore!G670")    ' WsDDA.Range("=F5:G670")
        Dim FndCel As Range
         Set FndCel = SrchRng.Find(what:=FileNmeSrchFor, After:=Application.Range("=DDAllBefore!F5"), LookAt:=xlPart, searchorder:=xlNext, MatchCase:=False) '
            If Not FndCel Is Nothing Then ' the range is set, so the file string has been found in a cell in  DDAllBefore
            Rem 4 we have two matching cells
             'Debug.Print FndCel.Value
            '4b) color matching file names in each worksheet, we do the unecerssary activating and selecting so we can see what is going in
             WsDMP.Activate: SrchForCel.Select
             Let SrchForCel.Characters(((InStrRev(CelVl, "\", -1, vbBinaryCompare)) + 1), (Len(CelVl)) - (InStrRev(CelVl, "\", -1, vbBinaryCompare))).Font.ColorIndex = ClrIdx
             WsDDA.Activate: FndCel.Select
             Let FndCel.Font.ColorIndex = ClrIdx
            Else ' No match was found - the thing in the cell in
            End If
        Else ' case no file path string in cell
        End If
    Next SrchForCel
End Sub

In the next posts are the screenshots for running the code on the selection
Comparing Device Drivers Selection.JPG : https://imgur.com/GSjjdod
Comparing Device Drivers Selection.JPG

_____ Workbook: ExplorerViewDriversBefore.xlsm ( Using Excel 2007 32 bit )

Audio, Video und Gamecontroller
	High definition Audio-Gerät
			C:\Windows\system32\DRIVERS\dmk.sys
			C:\Windows\system32\DRIVERS\HdAudio.sys
			C:\Windows\system32\drivers\ksthunk.sys
			C:\Windows\system32\DRIVERS\portcls.sys
		C:\Windows\system32\SysFxUl.dll
		C:\Windows\system32\WMALFXGFXDSP.dll