Accessing Remote registry in Excel

[newsgroups.jd]

I have a list of servers in excel and I would like to access a few
registry setting on each server and write that value to other column.
Can anyone help with this? I do not need to write to the registry,
just read.

Thanks in advance

 

[Dave Peterson]

Look at GetSetting in VBA's help. It has an example, too.

 

[Dave Peterson]

Ignore that last post. It'll work if you want to save or retrieve a setting for
your VBA program--but it's not used for accessing the registry in general:

Option Explicit

Sub testme()
Dim wsh As Object
Dim RegStr As String
Dim RegVal As String

RegStr = "HKLM\Software\Microsoft\mediaplayer\" _
& "settings\mp3encoding\PreferredCodecName"

Set wsh = CreateObject("WScript.Shell")

RegVal = "not found!!!!"
On Error Resume Next
RegVal = wsh.RegRead(RegStr)
On Error GoTo 0

If RegVal = "not found!!!!" Then
MsgBox "key wasn't found"
Else
MsgBox RegVal
End If
End Sub

 

[Dave Peterson]

Ahhhh.

Please ignore both these posts.

I have no idea how to access the registry on a server.

If you don't get a real answer in this newsgroup, you may want to post in a VB
newsgroup--explain that you want to use it in excel's VBA and maybe you'll get a
response you don't have to tweak.

 

[Dave Peterson]

Lots of times, it's easier to answer the questions you read--instead of the
question they write <vbg>.

 

[Tim Williams]

Try the vbscript group: this is the type of thing vbscript gets used for a
lot.

Tim

 

[newsgroups.jd]

Thanks

 

[newsgroups.jd]

Thanks

I got a VBS script to work pulling remote registry data - could you
guys help me now figure ut how to put this into excel please??


-- code --


On Error Resume Next

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "remoteServer"
Set objReg = GetObject("winmgmts:\\" & strComputer & "\root
\default:StdRegProv")
strKeyPath = "SOFTWARE\AssetInfo"

AssetTag = "AssetTag"
SerialNumber = "SerialNumber"

objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, AssetTag,
strValue1
objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath,
SerialNumber, strValue2

Wscript.Echo strValue1
Wscript.Echo strValue2


-- code --

Now I jsut need it to pull the strComputer from like excel cell A2 and
then instead of echo put the value in B2 and so on...

Thanks in advance.


JD

 

[Dave Peterson]

Untested, but it did compile:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myCell As Range
Dim strValue1 As String
Dim strValue2 As String
Dim strComputer As String
Dim objReg As Object
Dim strKeyPath As String
Dim AssetTag As String
Dim SerialNumber As String

Const HKEY_LOCAL_MACHINE = &H80000002

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

On Error Resume Next
For Each myCell In myRng.Cells

strComputer = myCell.Value
Set objReg = GetObject("winmgmts:\\" & strComputer _
& "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\AssetInfo"

AssetTag = "AssetTag"
SerialNumber = "SerialNumber"

objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
AssetTag, strValue1

objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
SerialNumber, strValue2

myCell.Offset(0, 1).Value = strValue1
myCell.Offset(0, 2).Value = strValue2

Next myCell
On Error GoTo 0

End Sub

 

[newsgroups.jd]

Untested, but it did compile:

Option Explicit
Sub testme02()

    Dim myRng As Range
    Dim myCell As Range
    Dim strValue1 As String
    Dim strValue2 As String
    Dim strComputer As String
    Dim objReg As Object
    Dim strKeyPath As String
    Dim AssetTag As String
    Dim SerialNumber As String

    Const HKEY_LOCAL_MACHINE = &H80000002

    With Worksheets("sheet1")
        Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
    End With

    On Error Resume Next
    For Each myCell In myRng.Cells

        strComputer = myCell.Value
        Set objReg = GetObject("winmgmts:\\" & strComputer _
                            & "\root\default:StdRegProv")
        strKeyPath = "SOFTWARE\AssetInfo"

        AssetTag = "AssetTag"
        SerialNumber = "SerialNumber"

        objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
                                  AssetTag, strValue1

        objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
                                  SerialNumber, strValue2

        myCell.Offset(0, 1).Value = strValue1
        myCell.Offset(0, 2).Value = strValue2

    Next myCell
    On Error GoTo 0

End Sub



















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks -

I am not very proficient in excel but I think I am doing it right...

I went to Tool > Macros > VB Editor and copied this to This Workbook -
Saved and ran. Took a while and did not post anythign to the
workbook. I only have about 5 servers listed for testing and tried to
debug - it goes through the loop well over 100 times - in theroy it
should only go through the loop 6 times... I stopped the debug at that
point - any suggestions? It is sheet 1, servers are listed a2 - a6.

Any thoughts?

JD

 

[Dave Peterson]

First, the code shouldn't be under the ThisWorkbook module.

Take a look at Debra Dalgleish's site for instructions for placing the code in a
general module.
http://contextures.com/xlvba01.html#Regular

Second, I don't see anything in the code I posted that would cause the loop to
be executed 100 times. Maybe you have an event macro running????

I don't see why the code would loop more than 5 times (5 cells in A2:A6).

 

[newsgroups.jd]

First, the code shouldn't be under the ThisWorkbook module.

Take a look at Debra Dalgleish's site for instructions for placing the code in a
general module.http://contextures.com/xlvba01.html#Regular

Second, I don't see anything in the code I posted that would cause the loop to
be executed 100 times.  Maybe you have an event macro running????

I don't see why the code would loop more than 5 times (5 cells in A2:A6).

(e-mail address removed) wrote:

<<snipped>>

Good article for noobs -

Plus I forgot I was opening excel with a reg user account - once I
runas with a admin account that has access to the servers it works -
this is very cool!!

I have added about 15 reg keys that I want to get values from and they
all pull into excel. I am guessing I will use this code for a long
time to come in the future - thanks for your help!!!!

This will make my life easy since I have about 150 servers I want data
from


JD

 

[Dave Peterson]

Glad you got it working--and thanks for posting back the details.

I'm not sure where you got the .VBS script from, but you may want to drop off a
note of thanks to that guy/gal, too.

 

[newsgroups.jd]

Glad you got it working--and thanks for posting back the details.

I'm not sure where you got the .VBS script from, but you may want to dropoff a
note of thanks to that guy/gal, too.







(e-mail address removed) wrote:







--

Dave Peterson- Hide quoted text -

- Show quoted text -

I figured that out on my own from technet articles....

http://msdn.microsoft.com/en-us/library/aa394600(VS.85).aspx

 

[Dave Peterson]

It's time to buy yourself a nice cold drink in thanks <vbg>.