none
Excel VBA to read registry key RRS feed

  • Question

  • I use Excel to perform various queries against a list of servers and drop the results in the adjacent columns (i.e. ping, OS version, Uptimes, etc). All works fine doing WMI calls etc. I am trying to read a registry key now so that I can populate the cell with the value of a specific key (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download\LastSuccessTime ) so I can see the last time Windows Update ran successfully. It works fine as a stand-alone vbs. In excel I am having the issue. I obviously had to modify it a little from the original VBS but here is what I have:

    Function WinUpdate(Host)
    
    Dim oReg As Object
    Dim strKeyPath As String
    Dim strValueName As String
    Dim strvalue
    
    Const HKEY_LOCAL_MACHINE = &H80000002
       strComputer = Host
    
    
     Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
    
    strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Download"
    strValueName = "LastSuccessTime"
    oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
    
     
    WinUpdate = strValue
    
    End Function
    
    
    
    
    Sub GetWinUpdate()
    
      Dim Cell As Range
      Dim ipRng As Range
      Dim Result As String
      Dim Wks As Worksheet
    
    
    Set Wks = Worksheets("Sheet1")
    
    Set ipRng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, ipRng.Column).End(xlUp)
    Set ipRng = IIf(RngEnd.Row < ipRng.Row, ipRng, Wks.Range(ipRng, RngEnd))
    
      For Each Cell In ipRng
        Result3 = WinUpdate(Cell)
        Cell.Offset(0, 4) = Result3
      Next Cell
    
    End Sub
    
    
    (Don't mind all the ipRng references - part of my other tests)

    strValue is never populated.  In all my troubleshooting I created a reg key as a test and I am pretty sure it worked.  What I found as the only difference and pinpointed the problem to be the space in the path of what i am looking for.  Now of course I can not get anything to work (with or without a space).  So I'm either crazy and never got it working or i've been staring at this too long!

    Any help would be greatly appreciated


    • Edited by Espo1026 Wednesday, December 24, 2014 6:15 PM added line
    • Moved by George123345 Thursday, December 25, 2014 5:35 AM
    Wednesday, December 24, 2014 6:12 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Visual Basic for Applications

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Thursday, December 25, 2014 5:35 AM