Copy Physical memory Data from task Manager using vba

[alex gomes]

I am trying to extract the highlighted data (attached doc) from TaskManager And copy to the worksheet Called "Physical Memory" in the current workbook .

And one more thing is i also want the Same data from the Remotely connected pc and copy it to the same Sheet called "Physical Memory".


Plz go through the attachment.

Really appreciate your help!!!!

 

[Debaser]

I think you need two WMI queries for that.

Sub PhysicalMemWMI()
    Dim dTotalMemory          As Double
    Dim dAvailable            As Double
    Dim dFreeMem              As Double
   
    sWQL = "SELECT * FROM Win32_OperatingSystem"
    Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")
    Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)

    With ThisWorkbook.Sheets("Physical Memory")
        With .Range("A1").Resize(1, 3)
            .Value = Array("TotalVisibleMemorySize", "FreePhysicalMemory", "In use")
            .Font.Bold = True
        End With

        For Each oWMIObjEx In oWMIObjSet
            dTotalMemory = dTotalMemory + oWMIObjEx.TotalVisibleMemorySize
        Next
        dTotalMemory = dTotalMemory / 1024
        Set colItems = oWMISrvEx.ExecQuery("Select * from Win32_PerfFormattedData_PerfOS_Memory", , 48)
        For Each objitem In colItems
            dFreeMem = dFreeMem + objitem.FreeAndZeroPageListBytes
            dAvailable = dAvailable + objitem.AvailableBytes
        Next objitem
        dFreeMem = dFreeMem / 1024 / 1024
        .Range("A2:C2").Value2 = Array(Format(dTotalMemory, "#,##0 MB"), Format(dFreeMem, "#,##0 MB"), Format(((dTotalMemory * 1024 * 1024) - dAvailable) / 1024 / 1024 / 1024, "#,##0.00 GB"))
    End With
End Sub

 

[alex gomes]

Thank You for your reply!!
It is working in Host pc perfectly

How to read the similar data from remote pc which is connected to the Host pc.

Thanks Again!!

 

[Debaser]

Try changing the initial set up to:

Set oWMISrvEx = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

where strComputer has the remote computer name.

 

[alex gomes]

Yes it is working!!
Thanks a lot Debaser

 

[alex gomes]

What if, I want to copy the data in different Range every time whenever I will call the function
I want to make it generic

Thanks In Advance!!!

 

[Debaser]

Here's one way:

Sub PhysicalMemWMI()
    Dim dTotalMemory          As Double
    Dim dAvailable            As Double
    Dim dFreeMem              As Double
    Dim strComputer           As String
    Dim oWMISrvEx             As Object
    Dim oWMIObjSet            As Object
    Dim oWMIObjEx             As Object
    Dim colItems              As Object
    Dim objItem               As Object
    Dim sWQL                  As String
    Dim rPaste                As Range

    strComputer = InputBox(prompt:="Enter computer name (use . for the local computer)", Title:="Computer name", Default:=".")
    If StrPtr(strComputer) = 0 Then Exit Sub
   
    On Error Resume Next
    Set rPaste = Application.InputBox(prompt:="Select range to output data to", Title:="Output range", Type:=8)
    On Error GoTo 0

    If rPaste Is Nothing Then Exit Sub

    sWQL = "SELECT * FROM Win32_OperatingSystem"
    Set oWMISrvEx = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)


    With rPaste.Cells(1).Resize(1, 3)
        .Value = Array("TotalVisibleMemorySize", "FreePhysicalMemory", "In use")
        .Font.Bold = True
    End With

    For Each oWMIObjEx In oWMIObjSet
        dTotalMemory = dTotalMemory + oWMIObjEx.TotalVisibleMemorySize
    Next
    dTotalMemory = dTotalMemory / 1024
    Set colItems = oWMISrvEx.ExecQuery("Select * from Win32_PerfFormattedData_PerfOS_Memory", , 48)
    For Each objItem In colItems
        dFreeMem = dFreeMem + objItem.FreeAndZeroPageListBytes
        dAvailable = dAvailable + objItem.AvailableBytes
    Next objItem
    dFreeMem = dFreeMem / 1024 / 1024
    rPaste.Cells(1).Offset(1).Resize(1, 3).Value2 = Array(Format(dTotalMemory, "#,##0 MB"), Format(dFreeMem, "#,##0 MB"), Format(((dTotalMemory * 1024 * 1024) - dAvailable) / 1024 / 1024 / 1024, "#,##0.00 GB"))
End Sub