We help IT Professionals succeed at work.

how to fix this VBA code

Flora Edwards
on
i am trying to put this in array of cells but it overwrites other values

i want the objQuickFix headers to appear in Columns

like this Computer:   "Description: " "Hot Fix ID: "  as header of columns and then the data to put in rows.

i tried the below code but it overwrites the previous data and it is a mess.

 Sub Test()
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colQuickFixes = objWMIService.ExecQuery _
    ("Select * from Win32_QuickFixEngineering")
 
For Each objQuickFix In colQuickFixes
    Cells(1, 1).Value = "Computer: "
    Cells(2, 1).Value = objQuickFix.CSName
    Cells(1, 2).Value = "Description: "
    Cells(2, 2).Value = objQuickFix.Description
    Cells(2, 3).Value = "Hot Fix ID: "
    Cells(3, 3).Value = objQuickFix.HotFixID
    Cells(3, 4).Value = "Installation Date: "
    Cells(4, 4).Value = objQuickFix.InstallDate
    Cells(4, 5).Value = "Installed By: "
    Cells(5, 5).Value = objQuickFix.InstalledBy
Next
End Sub

Open in new window

Comment
Watch Question

ThierrySysAdmin

Commented:
Because you are overwriting the same rows, over and over again.

add a variable to the rows part of the cell

for example cell(B,1)

then add a b=b+1 to in the for-next loop.

You might want to get the 'headers' out of the for-next loop
Top Expert 2016
Commented:
Hi,

pls try
Sub Test()
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colQuickFixes = objWMIService.ExecQuery _
    ("Select * from Win32_QuickFixEngineering")
 
    Cells(1, 1).Value = "Computer: "
    Cells(1, 2).Value = "Description: "
    Cells(1, 3).Value = "Hot Fix ID: "
    Cells(1, 4).Value = "Installation Date: "
    Cells(1, 5).Value = "Installed By: "
    idx = 2
For Each objQuickFix In colQuickFixes
    Cells(idx, 1).Value = objQuickFix.CSName
    Cells(idx, 2).Value = objQuickFix.Description
    Cells(idx, 3).Value = objQuickFix.HotFixID
    Cells(idx, 4).Value = objQuickFix.InstallDate
    Cells(idx, 5).Value = objQuickFix.InstalledBy
    idx = idx + 1
Next
End Sub

Open in new window

Regards
Flora EdwardsMedicine

Author

Commented:
Thank you very much