Matt
Fri Dec 15 08:23:47 CST 2006
Excellent. That worked very well. Thank you Richard.
Richard Mueller wrote:
> No, you don't need the array. If you read values from the MSI file, as you
> read each value you can check for duplicates in the dictionary object
> (before you write to the spreadsheet). Or, if you already have populated a
> spreadsheet, you can read the values from the spreadsheet. I assume you
> already have code to read or write to a spreadsheet. If not, I have sample
> VBScript programs demonstrating how it can be done linked here:
>
>
http://www.rlmueller.net/Write%20to%20Excel.htm
>
>
http://www.rlmueller.net/Read%20from%20Excel.htm
>
> For example, to check for duplicates in any column in row 4 of a
> spreadsheet:
> =============
> Set objExcel = CreateObject("Excel.Application")
> strExcelPath = "c:\MyFolder\MyData.xls"
>
> Set objList = CreateObject("Scripting.Dictionary")
> objList.CompareMode = vbTextCompare
>
> ' Open specified spreadsheet and select the first worksheet.
> objExcel.WorkBooks.Open strExcelPath
> Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
>
> ' Iterate through the columns in row 4 of the spreadsheet.
> ' Stop when first empty cell found in row 4.
> intCol = 1
> Do While objSheet.Cells(4, intCol).Value <> ""
> strGuid = objSheet.Cells(4, intCol).Value
> If Not objList.Exists(strGuid) Then
> ' New value, add to dictionary object.
> ' Keep track of column number.
> objList.Add strGuid, intCol
> Else
> ' Duplicate value.
> Wscript.Echo "Duplicate GUID: " & strGuid
> Wscript.Echo "-- repeated in columns " _
> & objList(strGuid) & " and " & CStr(intCol)
> End If
> intCol = intCol + 1
> Loop
>
> ' Close workbook and quit Excel.
> objExcel.ActiveWorkbook.Close
> objExcel.Application.Quit
>
> --
> Richard
> Microsoft MVP Scripting and ADSI
> Hilltop Lab -
http://www.rlmueller.net
>
> "Matt Reid" <megabucket@hotmail.com> wrote in message
> news:1166118292.327107.193490@80g2000cwy.googlegroups.com...
> > Thanks Richard.
> >
> > Do I have to build the arrGuids array? How are the column values
> > referenced?
> >
> > Matt
> >
> > Richard Mueller wrote:
> >> Matt Reid wrote:
> >>
> >> > I have a script which opens up MSI packages in a directory and
> >> > subdirectories. It extracts all the component GUID information and
> >> > writes one column for each package to an Excel spreadsheet.
> >> > For each column:
> >> > Row 1 is the package name.
> >> > Row 2 is the productcode of the package
> >> > Row 3 is blank
> >> > Row 4 is the start of the component GUIDs
> >> >
> >> > Each column may be a different length.
> >> >
> >> > I have to check for duplicate component GUIDs between the columns, and
> >> > report back which component GUIDS are duplicated in which columns. What
> >> > is the best way to do this?
> >>
> >> Most efficient would be to use a dictionary object. This has Add, Remove,
> >> and Exists methods. A quick snippet:
> >> ==============
> >> Set objList = CreateObject("Scripting.Dictionary")
> >> ' Use case insensitive comparisons.
> >> objList.CompareMode = vbTextCompare
> >>
> >> ' Loop through array of Guids.
> >> ' This demo assumes arrGuids is array of Guids.
> >> For j = 0 To 20
> >> ' Check if this value seen before.
> >> If Not objList.Exists(arrGuids(j)) Then
> >> ' New value, add to dictionary object.
> >> ' Keep track of index, j.
> >> objList.Add arrGuids(j), j
> >> Else
> >> ' Duplicate value. The original value has index arrGuids(j).
> >> Wscript.Echo "arrGuids(" & j & ") = arrGuids(" _
> >> & objList(arrGuids(j)) & ")"
> >> End If
> >> Next
> >>
> >> --
> >> Richard
> >> Microsoft MVP Scripting and ADSI
> >> Hilltop Lab -
http://www.rlmueller.net
> >