?
Solved

Retrieving Software List using the Registry

Posted on 2012-09-13
70
Medium Priority
?
926 Views
Last Modified: 2012-09-21
I'm using the below code to generate a software list in excel.  It works fine but for some reason it skips folders on a windows 7 machine in the registry folder specified.  I would appreciate any help.

Thanks.
 
Option Explicit
Const HKEY_LOCAL_MACHINE As Long = &H80000002
Const HKLM = HKEY_LOCAL_MACHINE
Const KEY_QUERY_VALUE = &H1

Sub GetSoftwareFromReg()

Dim temp As Object
Dim readValue As String
Dim strComputer As String
Dim rPath As String, Count As Double
Dim arrSubKeys()
Dim strAsk
Dim strValueName
Dim strValue

On Error Resume Next
strComputer = "."
Set temp = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")

rPath = "Software\Microsoft\Windows\CurrentVersion\Uninstall\"

temp.EnumKey HKLM, rPath, arrSubKeys
Count = 1

For Each strAsk In arrSubKeys

strValueName = "DisplayName"
temp.GetStringValue HKLM, rPath & "\" & strAsk, strValueName, strValue
Range("A" & Count) = strValue

strValueName = "DisplayVersion"
temp.GetStringValue HKLM, rPath & "\" & strAsk, strValueName, strValue
Range("B" & Count) = strValue

If strValue = "Null" Then
Else
Count = Count + 1
End If

Next

rPath = "Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"

temp.EnumKey HKLM, rPath, arrSubKeys

For Each strAsk In arrSubKeys

strValueName = "DisplayName"
temp.GetStringValue HKLM, rPath & "\" & strAsk, strValueName, strValue
Range("A" & Count) = strValue

strValueName = "DisplayVersion"
temp.GetStringValue HKLM, rPath & "\" & strAsk, strValueName, strValue
Range("B" & Count) = strValue

If strValue = "Null" Then
Else
Count = Count + 1
End If

Next

Range("A1:B" & Count).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:C" & Count)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

End Sub
0
Comment
Question by:jbirkku
  • 35
  • 35
70 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38397880
Hi, jbirkku.

You have a couple of issues...
(1) A number of entries have no Display Name (in my case, for example, AddressBook, MPlayer2, SchedulingAgent etc.). This is the cause of the blank entries.
(2) You appear to be trying to drop entries whose Display Version returns a strValue equal to "Null". However, rather than checking for a string you should instead be checking for Null, e.g.
If IsNull(strValue) then etc.

What do you want to happen when an entry has no Display Name? No Display Version?

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38398378
When the entry is Null I would like it to not add to the count to avoid the blanks.  The blanks are not the big of an issue because the sort at the end eliminates the blanks.  The issue I'm having is on a Windows 7 machine it will skip folders that do have a DisplayName and a DisplayVersion and I can't figure out how to fix it.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38398600
jbirkku,

Please export a handful of the missing entries and post them here.

Edit: Please see below. The macro now ignores entries which don't have a DisplayName. The code is...
Option Explicit

Const HKEY_LOCAL_MACHINE As Long = &H80000002
Const HKLM = HKEY_LOCAL_MACHINE
Const KEY_QUERY_VALUE = &H1

Dim Count As Long
Dim temp As Object

Sub GetSoftwareFromReg()
Dim strComputer As String

On Error Resume Next

strComputer = "."
Set temp = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
                 strComputer & "\root\default:StdRegProv")

Count = 1

Call GetSoftwareFromReg_Detail("Software\Microsoft\Windows\CurrentVersion\Uninstall\")

Call GetSoftwareFromReg_Detail("Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall")

Range("A1:B" & Count).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:C" & Count)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

End Sub

Sub GetSoftwareFromReg_Detail(rpath As String)
Dim arrSubKeys()
Dim strAsk
Dim strValueName
Dim strValue

temp.EnumKey HKLM, rpath, arrSubKeys

For Each strAsk In arrSubKeys

    strValueName = "DisplayName"
    temp.GetStringValue HKLM, rpath & "\" & strAsk, strValueName, strValue
    
    ' Only write entries with a DisplayName...
    If Not IsNull(strValue) Then
        Range("A" & Count) = strValue
        
        strValueName = "DisplayVersion"
        temp.GetStringValue HKLM, rpath & "\" & strAsk, strValueName, strValue
        Range("B" & Count) = strValue
        
        Count = Count + 1
    End If

Next

End Sub

Open in new window


Regards,
Brian.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jbirkku
ID: 38399588
Thanks!  I have attached two .reg files that don't show up in the list.
Revit-2011.txt
Revit-2012.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38399743
Thanks, jbirkku.

Unfortunately they both worked fine for me.

Couple of suggestions, please...

(1) Please run the macro in my previous post, but with the "On Error Resume Next" removed.

(2) I know that Registry entries can, under some circumstances, be hidden. Would you feel comfortable about deleting those two entries from the Registry and then reloading them from the files you posted?
Messing with the Registry can be dangerous, so please don't do this unless you both have a current backup of the Registry and are comfortable with doing this.

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38399788
Thanks! I will give that a try.  I don't feel comfortable deleting any entries from the registry.  Are you using Windows 7?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38399826
jbirkku,

Yes (32-bit, I'm guessing you're 64?). I'm also using Excel 2010.

Let me know how the macro works out. In the meantime I'll see if I can dig out anything on the hidden entries.

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38399870
Brian,

I'm using a 64-bit with excel 2007. Tried it with the revised code and they still don't show up.  Do you think its an issue with the version of excel?

Thanks for your help,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38399963
jbirkku,

Hmm, it feels more Windows than Excel to me.

OK, good news. The last post here has VB Script to do the same job. If you get the same result from this as from the macro then we can exclude Excel. (It creates a files called SoftwareList.txt in the same directory as the script file.

Let me know if you need any assistance in running VB Script.

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38400153
Brain,

I got the code to work until it gets to the end.  Is there a reference library I need to add to get the "WriteLine" function to work?

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38400231
Josh,

I did nothing special.

What was the error?

Did you use cscript or wscript?

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38400247
Brian,

I get an "invalid procedure call or argument" error when it reaches the "objFile.WriteLine (objItem)" line.  I haven't added any references to the code in vb.

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38400331
Josh,

(1) And had you used csscript or wscript?

(2) Could you also try changing the "CreateTextFile" line as follows....
Set objFile = objFso.CreateTextFile("SoftwareList.csv", True, -1)
(i.e. just add the ", -1")

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38400569
Brian,

I got it to work.  Thanks for your help.  I don't have access to the Windows 7 64-bit machine now.  I prob won't be able to test out the revised code till Monday.  I will let you know the if it pulls in all of the software titles.  Thanks again for all of your help.

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38400577
Josh,

Thanks for the update - I'll talk to you then.

(BTW, what was the problem?)

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38400585
Brian,

I needed to add a reference to "Microsoft Scripting Runtime".  Then i ran into the issue of the WriteLine() function not being able to read certain characters.  So, I added "Resume Next on Error" just before that part of the code.

Regards,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38400666
Thanks Josh.

I'm not sure how/where/when I referenced the Runtime.

Your problem with funny characters may be significant. Perhaps on Monday you might replace the WriteLine() line by....
wscript.echo objItem
... to dump your data on the screen. That would allow you to identify the entry causing the problem (well, it might be necessary to comment out the sort as well).

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38401845
Brian,

Figured which line is causing the error "?[?;;g;C:\WINDOWS\system32\WBEM\Repository"  the ; is actually a square in the output.  Not sure which character is causing the error but it's on the last line of the output in the text file.  

Regards,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38401908
Josh,

The attached should give us a clear idea of what's going on - it outputs the file in UniCode, doesn't sort and includes the registry key.

Please post the output here.

Thanks,
Brian.List-Unistall-III.vbs
0
 

Author Comment

by:jbirkku
ID: 38402029
Brian,

Attached is the output.

Regards,
Josh
SoftwareList-III.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38402148
Josh,

Very interesting, thanks!

(1) On the minor issue, the "Repository" entry is...
{90120000-0030-0000-0000-0000000FF1CE}_ENTERPRISE_{A4A4EF95-DBFB-491F-AC8C-35E5A27270D9}
It's just the display name that's bad so, if this is bothering you, you may want to manually correct it. (While you're there have a look at the remaining entries - are they corrupt as well?) On seconds thought, please see (2) below!

(2) The two AutoDesk programs you mentioned are not here so we can exclude Excel as a possible source of the problem.
This is increasing looking like a Windows issue and I'm afraid I won't bring too much to the party. Could I suggest that you close this question by allocating 0 points to one of your posts. Then open a new question in the appropriate Windows area linking in to this one. (I'd include both issues (missing entries and "Repository").)

Apologies that I couldn't put this to bed for you.

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38402388
Brian,

Sorry, I forgot to mention that I didn't run that on the 64-bit machine that has those two programs.  I won't be able to test I on that computer till Monday.  I will let you know if it works on a windows 7 64-bit computer.



Thanks for your help,
Josh
0
 

Author Comment

by:jbirkku
ID: 38406119
Brian,

I tried the "vbs" program on a couple computers and it worked.  It pulled in all of the sofftware keys listed in those two folders.  The excel version still does not pull in all of the software titles in that folder.  However, when I compare the list to the uninstall list generated from windows going through the control panel it still does not grab all of the software loaded.  So, they must be hidden in other uninstall folder in the registry.
Do you have any other ideas on the location in the registry?

Thanks for you help,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406410
Josh,


Edit: Darn, I think I misread your post. Am I correct that VBS agrees to the two "Uninstall" paths, but that Control Panel has entries that are not in the two paths? If that's so, please still do the the two steps below, but also give me an example of entries which are unique to the Control Panel. I'll look at my own PC and see if I can see similar mismatches.


As I understand it, you have the Control Panel list which has more entries than the VBS output which has more entries than the Excel output. So, let's identify exactly which entries are appearing where...

(1) Please run the attached and post the output here. It doesn't exclude entries without a DisplayName. List-Unistall-IV.vbs(2) Please export and post here the the two "Uninstall" paths...
"Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"
"Software\Microsoft\Windows\CurrentVersion\Uninstall\"

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38406599
Brian,

Attached are: an excel file with software titles that do not show up in the registry but show up in windows uninstall/remove program list and the txt file output.

Regards,
Josh
SoftwareList-IV.txt
Titles.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406681
Thanks, Josh.

I just need the "Path" exports now, please!

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38406750
Brian,

Sorry I missed that.  See attached files.

Regards,
Josh
6432nodeexport.txt
MicroWinexport.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406887
Josh,

Thanks.

The output from "IV", but this is probably my fault - I thought that I had excluded blanks in "III" but I hadn't. So, please run List-Unistall-III.vbs and post the result.

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38406941
Brian,

See attached.

Thanks,
Josh
SoftwareList-III.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38406981
Josh,

Thanks. This is a bit weird - both of the VBS outputs you gave me today are only a fraction of the size they should be. I'll dig into them and see if an entry is causing the VBS to terminate prematurely.

Edit:
(A) Oops, it got the 64Bit details but didn't output them. Please try the attached...List-Unistall-V.vbs(B) Were today's VBS's definitely run on the same PC? For eaxmple, "IV" has Autocad 2008 whereas "III" has Autocad 2010.

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38410323
Brian,

Sorry for the delayed reply.  I didn't receive an email letting me know there was an updated response.  I have attached three files: output from softwarelist-III output (just in case I gave you the wrong version), output from softwarelist-V, output from softwarelist-IV (just in case I gave you the wrong output), and a zip file with the software registry folder under HKEY_Local_Machine.  Thanks for all of the time you have taken to help me out.  I appreciate it!

Regards,
Josh
SoftwareList-III.txt
SoftwareList-V.txt
software.zip
SoftwareList-IV.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38414333
Josh,

I'm back in harness again. I'm wading through the Registry entries at the moment (also trying to puzzle out why SoftwareList-V.txt has so few entries <sigh>).

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38414424
Brian,

Thanks, I appreciate your help.  I decided to add the key name to entries in excel and ended up with more results but I didn't get all of the entries.  I noticed that the number of entries are equal in both folders.  Not sure if this is the case or if windows is reaching a max on the range and eliminating folders as a result.  I have attached the excel spreadsheet I used.

Thanks,
Josh
Computer-Software-Inventory-Regi.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38414583
Josh,

Well, I've got all the data extracted from software.zip with the only missing being "pcaSlab 3.5" - can you confirm that it is on the PC?

The three text files are all missing the 64Bit entries. I understand that for III and IV but not V. I have attached VII (!) which only lists 64Bit. Please run that and post the result here.

I'll have a look at your previous post now.

Regards,
Brian.List-Unistall-VII.vbs
0
 

Author Comment

by:jbirkku
ID: 38414648
Brian,

Thanks!  See attached file.  pcaSlab 3.5 is not installed on that computer.  Sorry for the confusion.

Thanks,
Josh
SoftwareList-VII.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38414787
Josh,

OK, now were getting places!

Your latest spreadsheet has lots of duplicates (often with different Versions) and is missing 32Bit programs.

As expected, SoftwareList-VII.txt lists all of the 64Bit programs, while III, IV and V just list the 32Bit.

There's a lot of work needed to tidy up the Excel Macro, while the VBScript just has one problem (32Bit and 64Bit separate). My preference would be to get the VBScript working and move it to Excel. Assuming you're happy with this, I need a few things from you...
(A) Besides displaying the Key, DisplayName andDisplayVersion, I think that you should also show whether it's 32Bit or 64Bit. There are a number of duplicates between the two sections and it's likely to cause confusion if you can't tell them apart.
(B) Now that you're displaying the Key, do you still want to drop entries with no DisplayName? I'd strongly suggest that you keep them in, otherwise you'll miss entries like "AutoCAD Civil 3D 2010 Version 2".

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38414816
Brian,

I think that's a great idea to show whether it's a 64-bit or 32-bit software and I agree with keeping all the key names instead of dropping entries with no name.  I'm also okay with getting the VBscript to work first then transfer to excel.  Thanks for all of your help!

Regards,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38414955
Josh,

Edit: Just a sec - I think I see the problem, so don't bother with this.


OK, let's take a step back. I still don't understand why V isn't working so let's try a baby step back - the attached does 32Bit twice.

Regards,
Brian.SoftwareList-VI.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38415017
Josh,

OK, nearly there. Please give it a go and post the results.

Thanks,
Brian.List-Unistall-VIII.vbs
0
 

Author Comment

by:jbirkku
ID: 38415498
Brian,

Can you repost the vbs file?  For some reason I can't download it.

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38415783
Josh,

Sure, please see attached. I can see why you couldn't download it - it's showing as zero bytes!

Regards,
Brian.List-Unistall-VIII.vbs
0
 

Author Comment

by:jbirkku
ID: 38416157
Brian,

Thanks, I will try it out tomorrow morning.

Regards,
Josh
0
 

Author Comment

by:jbirkku
ID: 38417705
Brian,

Attached is the output from VIII.  It looks like all of the software is listed.

Regards,
Josh
SoftwareList-VIII.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38418284
Josh,

Thanks! Looks good as the no. of Registry and VBS entries agree as do their content - except for Version...
(1) "Microsoft Office Communicator 2007 R2" - the version no.'s disagree. The Registry says 3.5.6907.236, the VBS  3.5.6907.253.
(2) A leading blank is being output.
(3) Some Registry entries have a Version of "dword:00000001". The VBS is showing blank. Please fire up your favourite Registry entry and see what that displays.

Please check (1) and (3). (2) is utterly trivial so I'll get on to the Excel version.

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38418594
Brian,

Awesome! The version for Office Communicator shows up as 3.5.6907.253.  The programs that have a type "REG_DWORD" with a "DisplayVersion" value of 0000001 are: Autodesk Revit MEP 2011 x86 Update 2; Autodesk Revit Structure 2011 x86 Update 2;      AutoCAD Structural Detailing 2011 SP2; Autodesk Revit Architecture 2012 UR2;       Autodesk Revit MEP 2012 UR2; Autodesk Revit Structure 2012 UR2.  It looks like they are all updates.

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38418832
Josh,

I don't know what to say about the "DWords".  Let's see how they look with the attached.

The code is shown below. Please be careful of Column D - I've set its format to Text so things like "4.60" display correctly. The downside is that formulas don't run in such cells...
Option Explicit

Dim xRow As Long
 
Sub List_Registry_Uninstall()
Dim strComputer     As String
Dim objArray        As Variant
Dim objReg          As Variant

Sheets.Add
Range("A1:D1") = Array("Type", "Key", "Description", "Version")
Columns("D:D").NumberFormat = "@"
xRow = 2

strComputer = "."
Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
    
Application.ScreenUpdating = False
        
    Call GetInstalledPrograms_Detail(objReg, "64Bit", "SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\")
    Call GetInstalledPrograms_Detail(objReg, "32Bit", "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\")
    
    Range("A1:D1").Font.Bold = True
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True

End Sub

Sub GetInstalledPrograms_Detail(objReg As Variant, StrBit As String, strBaseKey As String)
Const HKLM = &H80000002
Dim strRegistry As String
Dim strKey      As Variant
Dim strValue    As String
Dim strVersion  As String
Dim arrSubKeys  As Variant

strRegistry = objReg.EnumKey(HKLM, strBaseKey, arrSubKeys)

If StrBit = "64Bit" And IsNull(arrSubKeys) Then Exit Sub

For Each strKey In arrSubKeys
    On Error Resume Next
        strValue = ""
        strVersion = ""
        Call objReg.GetStringValue(HKLM, strBaseKey & strKey, "DisplayName", strValue)
        If strValue = "" Then _
            Call objReg.GetStringValue(HKLM, strBaseKey & strKey, "QuietDisplayName", strValue)
        Call objReg.GetStringValue(HKLM, strBaseKey & strKey, "DisplayVersion", strVersion)
        Range("A" & xRow & ":" & "D" & xRow) = Array(StrBit, strKey, strValue, strVersion)
        xRow = xRow + 1
    On Error GoTo 0
Next

End Sub

Open in new window

Regards,
Brian.
List-Registry-Uninstall.xlsm
0
 

Author Comment

by:jbirkku
ID: 38419336
Brian,

Attached is the output from the code.  It looks like it's still missing the AutoCAD MEP program.  Not sure why this won't show up in the list.

Regards,
Josh
Computer-Software-Inventory-Regi.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38419404
Josh,

This wasn't run on the same PC as the Registry dump. Also, it's been changed sunsequent to the run.

Please run the macro against the "original" PC and post the unmodified output.

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38419481
Brian,

Not sure why it pulled in a different registry on the last version.  It was ran on the same computer.  I copied of our network and to the local drive this time, then ran the macro and left it unsorted.

Regards,
Josh
Computer-Software-Inventory-Regi.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38419832
Josh,

Weird, but it appears that the 64Bit entries are being output twice.

Please try the attached. It sets the various Objects to Nothing after processing the 64Bit entries.

If the output from that is also 655 rows please comment out the "Wow6432Node" an run it again.

Thanks,
Brian.List-Registry-Uninstall-V3.xlsm
0
 

Author Comment

by:jbirkku
ID: 38419916
Brian,

I didn't notice that.  I ran the revised version with the "Wow6432Node" commented out and it's still just pulling in the software from the 64bit folder.  Would it be easier just to create an excel file with a "vbs" file?

Regards,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38420026
Josh,

OK. We're kind of at the Last Chance saloon now!
Option Explicit
Dim strComputer, SoftwareResult, strFilePath, count
Dim objArray, objFso, objFile, objItem
Dim objReg
Dim objXL, objWorkBook, objSheet, row

Set objXL       = CreateObject("Excel.Application")
objXL.Visible   = True
set objWorkBook = objXL.Workbooks.Add()
set objSheet    = objXL.Worksheets(1)
objXL.Worksheets(1).Select()

objXL.Cells(2,1).Value = "Type"
objXL.Cells(2,2).Value = "Key"
objXL.Cells(2,3).Value = "Description"
objXL.Cells(2,4).Value = "Version"

row = 3
 
strComputer    = "."
set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "/root/default:StdRegProv")
objXL.Cells(1,1).Value = objReg.path_.server

if CheckWinArchitecture = "64-bit" then
 
    GetInstalledPrograms_Detail "SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\", "64Bit"

end if

GetInstalledPrograms_Detail "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\", "32Bit"

wscript.echo "Finished."

'=========================================================================================
Function GetInstalledPrograms_Detail(strBaseKey, StrBit)
Const HKLM = &H80000002
Dim strRegistry, strKey, strValue, sVersion, arrSubKeys

strRegistry = objReg.EnumKey(HKLM, strBaseKey, arrSubKeys)

For Each strKey In arrSubKeys
    strRegistry = objReg.GetStringValue(HKLM, strBaseKey & strKey _
                  , "DisplayName", strValue)
    If strRegistry <> 0 Then
        objReg.GetStringValue HKLM, strBaseKey & strKey, "QuietDisplayName", strValue
    End If
    strRegistry = objReg.GetStringValue(HKLM, strBaseKey & strKey _
                  , "DisplayVersion", sVersion)

    objXL.Cells(row,1).Value = strBit
    objXL.Cells(row,2).Value = strKey
    objXL.Cells(row,3).Value = strValue
    objXL.Cells(row,4).Value = sVersion

    row = row + 1
Next

End Function
'=========================================================================================
Function ListSort(strTmp)
'Turn string into an array, sort it, remove duplicates and turn it back into a string.
Dim Tmp, i, j, temp
Tmp = Split(strTmp, vbcrlf)  

For i = UBound(Tmp) - 1 To 0 Step -1
    For j = 0 to i - 1
        'Remove Duplicates
        If LCase(Tmp(j)) = LCase(Tmp(j+1)) Then
          Tmp(j)=""
        end if
        'Sort Items
        If LCase(Tmp(j)) > LCase(Tmp(j+1)) Then
            temp = Tmp(j + 1)
            Tmp(j + 1) = Tmp(j)
            Tmp(j) = temp
        End if
    Next
Next

ListSort = Join(Tmp, vbcrlf)
End Function
'=========================================================================================
Function CheckWinArchitecture()
Dim strArchitecture
Dim objWMIService
Dim colOS
Dim intArchitecture
        
    strArchitecture = ""
    
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 
    Set colOS = objWMIService.ExecQuery _
        ("Select * from Win32_OperatingSystem")
    For Each objItem In colOS
        strArchitecture = objItem.OSArchitecture
    Next
    
    If strArchitecture = "" Then            
        Set colProcessor = objWMIService.ExecQuery _
            ("Select * from Win32_Processor")
        For Each objItem In colProcessor
            intArchitecture = objItem.Architecture
            Select Case intArchitecture
                Case 0
                    strArchitecture = "32-bit"
                Case 1
                    strArchitecture = "MIPS"
                Case 2
                    strArchitecture = "Alpha"
                Case 3
                    strArchitecture = "PowerPC"
                Case 6
                    strArchitecture = "IPF"
                Case 9
                    strArchitecture = "64-bit"
            End Select
        Next
    End If
    
    CheckWinArchitecture = strArchitecture

End Function
'=========================================================================================

Open in new window

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38421306
Josh,

I have included my last VBA code below. It contains everything in...
 - The Control Panel "Uninstall or change a program". (It actually holds more as it includes some of the Updates, none of which are listed in the main CP screen.)
 - HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\".
 - HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\".

I have three concerns about it...
(1) I don't know will it handle 64-Bit programs.
(2) I don't know will it handle programs installed for a user other than the logged on user.
(3) Where and how Windows keeps its uninstall information is a minefield.

So, my suggestion is...
(1) Try out the program, I suspect that the use of HKCU will bring in some of your missing programs. (Don't mind all the extra columns - I was using them in an attempt to understand why some programs weren't showing all the entries I was picking up.)
(2) Try out the VBS in my previous post. It didn't include HKCU (asily changed) but leet's see how it handles 64-Bit.
(3) Post the results of (1) and (2). If they're not essentially there then it's time to close this and ask a question in the appropriate Windows topic(s).

Regards,
Brian.
Option Explicit

Dim xRow As Long
 
Sub List_Registry_Uninstall()
Const HKU = &H80000003
Const HKLM = &H80000002
Const HKCU = &H80000001
Dim StrComputer As String
Dim objReg      As Variant

StrComputer = "."
Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & StrComputer & "/root/default:StdRegProv")

Sheets.Add
Range("A1") = objReg.path_.server
Range("A2:I2") = Array("Type", "Key", "Description", "Version", "Root", "System Comp.?", "Uninstall String", "Windows Installer?", "No Remove?")
Columns("D:D").NumberFormat = "@"
xRow = 3

Application.ScreenUpdating = False
    
    Call GetInstalledPrograms_Detail(objReg, "64Bit", HKLM, "HKLM", "SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\")
    'Call GetInstalledPrograms_Detail(objReg, "64Bit", HKLM, "HKLM_II", "SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Installer\UserData\")
    Call GetInstalledPrograms_Detail(objReg, "64Bit", HKCU, "HKCU", "SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\")
    'Call GetInstalledPrograms_Detail(objReg, "64Bit", HKU, "HKU", "S-1-5-21-1339542434-2085076969-2153875556-1000\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\")
    
    Set objReg = Nothing
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & StrComputer & "/root/default:StdRegProv")
    Call GetInstalledPrograms_Detail(objReg, "32Bit", HKLM, "HKLM", "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\")
    'Call GetInstalledPrograms_Detail(objReg, "32Bit", HKLM, "HKLM_II", "Software\Microsoft\Windows\CurrentVersion\Installer\UserData\")
    Call GetInstalledPrograms_Detail(objReg, "32Bit", HKCU, "HKCU", "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\")
    'Call GetInstalledPrograms_Detail(objReg, "32Bit", HKCU, "HKU", "S-1-5-21-1339542434-2085076969-2153875556-1000\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\")
    
    Range("A1:D2").Font.Bold = True
    Cells.EntireColumn.AutoFit
    Columns("G:G").ColumnWidth = 25
    Range("A3").Select
    ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True

End Sub

Sub GetInstalledPrograms_Detail(objReg As Variant, StrBit As String, xRoot, strRoot As String, strBaseKey As String)
Dim strRegistry  As String
Dim strValue     As String
Dim strVersion   As String
Dim xdwSystem    As Variant
Dim xdwWinInst   As Variant
Dim xdwNoRemove  As Variant
Dim strUninstall As String
Dim strKey       As Variant
Dim arrSubKeys   As Variant

strRegistry = objReg.EnumKey(xRoot, strBaseKey, arrSubKeys)

If IsNull(arrSubKeys) Then Exit Sub

For Each strKey In arrSubKeys
    On Error Resume Next
        strValue = ""
        strVersion = ""
        xdwSystem = ""
        strUninstall = ""
        xdwWinInst = ""
        xdwNoRemove = ""
        Call objReg.GetStringValue(xRoot, strBaseKey & strKey, "DisplayName", strValue)
        If strValue = "" Then Call objReg.GetStringValue(xRoot, strBaseKey & strKey, "QuietDisplayName", strValue)
        Call objReg.GetStringValue(xRoot, strBaseKey & strKey, "DisplayVersion", strVersion)
        Call objReg.GetDWORDValue(xRoot, strBaseKey & strKey, "SystemComponent", xdwSystem)
        Call objReg.GetStringValue(xRoot, strBaseKey & strKey, "UninstallString", strUninstall)
        Call objReg.GetDWORDValue(xRoot, strBaseKey & strKey, "WindowsInstaller", xdwWinInst)
        Call objReg.GetDWORDValue(xRoot, strBaseKey & strKey, "NoRemove", xdwNoRemove)
        Range("A" & xRow & ":" & "I" & xRow) = Array(StrBit, strKey, strValue, strVersion, strRoot, xdwSystem, strUninstall, xdwWinInst, xdwNoRemove)
        xRow = xRow + 1
    On Error GoTo 0
Next

Set strKey = Nothing
Set arrSubKeys = Nothing
End Sub

Open in new window

0
 

Author Comment

by:jbirkku
ID: 38421543
Brian,

Thanks for your help!  The vbs program works but the excel vba still just pulls in the 64-bit programs even when calling the 32-bit programs.  I have attached the results.  Is it possible to call a vbs program from excel then import the text file it generates?  

Thanks,
Josh
vbsoutput.xlsx
Computer-Software-Inventory-VLas.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38422347
Josh,

Please see attached. Couple of points....
(1) Please update the macro to point to the location in which you save the VBScript.
(2) This is incredibly silly. If you want to delete this question I would be delighted.!

Regards,
Brian.List-Unistall-X.xlsList-Unistall-X.vbs
0
 

Author Comment

by:jbirkku
ID: 38422522
Brian,

The vbs files work great!  When I try to run the vbs file from excel the program won't excute.  Is there a reference library I need to add or do I need to disable the prompts that I receive when I click the .vbs files and if so is there a way to do this within excel?

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38422605
Josh,

(1) When I try to run the vbs file from excel the program won't excute
By clicking on the button?

(2) the program won't excute
What happens?

(3) do I need to disable the prompts
What prompt?

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38422624
Brian,

When I double click the .vbs file it gives me a security popup box asking if I want to run the program; I'll click yes then it will run and create the file.

When I run the excel macro it flashes a black screen but doesn't create the output xls file and gets hung up in the Do Loop and the output file doesn't show up in the specfied folder.

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38422716
Josh,

In the Immediate Pane, please try the following (after changing the paths as necessar)...
msgbox Shell("CScript D:\WSH\List_Unistall_X.vbs d:\Fred.xls", 1)

Not sure it makes a difference, but what folder are you using for the VBS file?

Thanks,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38422847
Brian,

I got the vbs file to run using "ActiveWorkbook.FollowHyperlink "Z:\Temp\Birk\SoftwareList\List-Unistall-VIII.vbs"". However, when I used the excel version it still pulled in the 64-bit software for the 32-bit list.  So, I used the vbs file that outputs to a text file then imported that text file to excel and it worked.  Not sure what the issue is but it looks like it's an issue within excel.  I have attached the excel spreadsheet I used.  Do you know if there is a way to disable all of the prompts?

Thanks,
Josh
List-Unistall-X.xls
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38422940
Josh,

Did you try the Immediate Pane command I suggested?

when I used the excel version
Which Excel version?

disable all of the prompts
What prompts?

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38423003
Brian,

Yes, it returns a number value.  

When I call the List-Uninstall-X.vbs using excel vba it will create the xls file but it repeats the 64-bit software for the 32-bit list.  

When call I call the vbs file through excel it gives me two prompts: 1. "Opening......Some files can contain viruses....would you like to open this file?"; click OK; 2. "Do you want to open this file?, Name:..., Publisher..., Type....., From...."; click Open and the program runs.  Is there anyway to disable these two prompts?

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38423078
Josh,

(1) The number value is simply the process no. of the running VBSscript. Did it create the Fred.Xls?

(2) Do I understand you correctly - if  List-Uninstall-X.vbs is called directly then it gives the
correct result, but if it's called from Excel then it doubles up on the 64Bit entries?

(3)  What versions of Windows and Office.

(4) How often will you produce the listing? Will it just be your PC or an number of other ones?

Edit: (5) Is the attached any better?

Thanks,
Brian.List-Unistall-Xb.xls
0
 

Author Comment

by:jbirkku
ID: 38423251
Brian,

1)  No, the program won't execute using "wshShell.Run" command.  There is a blink on the screen but it doesn't create the file.

2) Yes,  If the List-Uninstall-X.vbs is called directly then it give the correct result, but if it's called from excel then it doubles up on the 64Bit entries.

3) I'm using Windows 7 64bit, Office 2007.

4) This will be used to generate the software listing for several PC's.

5) No, it still won't excute using "wshShell.Run" command.

What program are you using to create and debug a .vbs file?  I'm thinking, if anyone can run this file, that it might be easier to generate a software and hardware list using a .vbs file which will create a final excel document.

Thanks,
Josh
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38423376
Josh,

I'm thinking, if anyone can run this file, that it might be easier to generate a software and hardware list using a .vbs file which will create a final excel document.
Yes, that was the thrust of my (4)! With so many bizarre things happening this may be the best solution. Pretty easy too - as it stands, if you double-click on List_Unistall_X.vbs it just creates the file in the PC's Temp folder.
I just use Notepad for editing the VBS (no debug, I'm afraid). Want me to change it to use a different folder?

Cheers,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38423463
Brian,

Cool!  Thanks your help!  No, I think I can make the modifications to fit the final result I'm looking for.  Is there a reference I could use to learn more about the commands available for a .vbs file?

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38423510
Josh.

Useful general summary...
http://www.massmind.org/techref/language/asp/vbs/vbscript/199.htm

Scriping for System Administrators
http://technet.microsoft.com/en-us/library/ee198896.aspx
(Very old. but still relevant.)

Regards,
Brian.
0
 

Author Comment

by:jbirkku
ID: 38423656
Brian,

Awesome!  You have been a tremendous help!  I ended up on a better path!

Thanks,
Josh
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38423668
Josh,

Nice of you to say that but it was at quite a cost - I'm embarrassed that this dragged on for so long.

Regards,
Brian.
0
 

Author Closing Comment

by:jbirkku
ID: 38423669
Brian was a huge help!  He spent a lot of time to end with an awesome solution.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
New style of hardware planning for Microsoft Exchange server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question