Software

Follow up: Format Windows system information imported into Excel

Following up on a tip published just days before, Mike Radmore offers some example VBA code that will format system information imported into Excel into a more readable format.

Early in the week (July 6, 2011) I published the blog post "Quick Tip: Gather Windows System Information into Excel." The tip explained how to use the systeminfo command to gather system information for your Windows-based computers and then import that information into Excel.

Almost immediately, readers started asking for ways to format the information imported into Excel so that it was more readable. The discussion thread following the blog post offered several very good options, and one suggested using some VBA code to automate the process.

Well, as you might expect from an audience of IT professionals, someone had some VBA code they were willing to share that addresses this specific problem. Mike Radmore offers this VBA code as an example only — one that you will have to tweak for your specific situation. If you are unfamiliar with VBA and how it works, this code may not mean that much to you.

Tidy

Attribute VB_Name = "SysInfo"

Option Explicit

'=================================================================

' Project : VBAProject

' Module : SysInfo

' Procedure : Tidy

' Author : Macro Man

' Date : 2011-07-07

' Purpose : The output of: "systeminfo /fo csv > [filename].csv" _

is a 2-row csv file with: _

row 1 containing the file names, and _

row 2 containing the field values

' This macro converts this format to one field name _

per row with the relevant field value in the _

adjacent column

'————————————————————————————————-
Sub Tidy()

'—————— EDIT THESE CONSTANTS AS REQUIRED —————————-

Const HOTFIX_STR = "Hotfix(s)"

Const NIC_STR = "NetWork Card(s)"

'————————————————————————————————-
Dim CommaPos As Integer
Dim ActiveCellValue As String
' delete blank row at start of file
Range("A1").EntireRow.Delete
'————————————————————————————————-

' this code transposes row 1 to column A, and row 2 to column B

'————————————————————————————————-

Range("A1", Range("A1").End(xlToRight)).Copy

Range("A3").PasteSpecial _

Paste:=xlPasteAll, _

Operation:=xlPasteSpecialOperationNone, _

Transpose:=True
  Range("A2", Range("A2").End(xlToRight)).Copy

Range("B3").PasteSpecial _

Paste:=xlPasteAll, _

Operation:=xlPasteSpecialOperationNone, _

Transpose:=True
'————————————————————————————————-

' installed hotfixes are comma separated within a single cell

' this code places each hotfix in its own cell

'————————————————————————————————-
' find the cell with the appropriate fieldname, in case it varies _

from O/S to O/S

Range("A1").Select

Cells.Find( _

What:=HOTFIX_STR, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlNext, _

MatchCase:=True, _

SearchFormat:=False _

).Activate

ActiveCell.Offset(0, 1).Activate
' store the field value in a variable because we're going to _

a) replace it, and _

b) edit it

ActiveCellValue = ActiveCell.Value

CommaPos = InStr(ActiveCellValue, ",")

ActiveCell.Value = Left(ActiveCellValue, CommaPos - 1)

ActiveCell.Offset(1, 0).Activate

Do While CommaPos <> 0

ActiveCellValue = _

Right(ActiveCellValue, Len(ActiveCellValue) - CommaPos)

CommaPos = InStr(ActiveCellValue, ",")

If Len(ActiveCellValue) > 0 Then

Selection.EntireRow.Insert

ActiveCell.Value = Left(ActiveCellValue, CommaPos - 1)

ActiveCell.Offset(1, 0).Activate

End If

Loop
'————————————————————————————————-

' installed NIC info is comma separated within a single cell

' this routine places each parameter in its own cell

'————————————————————————————————-
' find the cell with the appropriate fieldname, in case it varies _

from O/S to O/S

Range("A1").Select

Cells.Find( _

What:=NIC_STR, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlNext, _

MatchCase:=True, _

SearchFormat:=False _

).Activate

ActiveCell.Offset(0, 1).Activate
' store the field value in a variable because we're going to _

a) replace it, and _

b) edit it

ActiveCellValue = ActiveCell.Value

CommaPos = InStr(ActiveCellValue, ",")

ActiveCell.Value = Left(ActiveCellValue, CommaPos - 1)

ActiveCell.Offset(1, 0).Activate

Do While CommaPos <> 0

ActiveCellValue = _

Right(ActiveCellValue, Len(ActiveCellValue) - CommaPos)

CommaPos = InStr(ActiveCellValue, ",")

If CommaPos = 0 Then

ActiveCell.Value = ActiveCellValue

Else

If Len(ActiveCellValue) > 0 Then

Selection.EntireRow.Insert

ActiveCell.Value = Left(ActiveCellValue, CommaPos - 1)

ActiveCell.Offset(1, 0).Activate

End If

End If

Loop
'————————————————————————————————-

' finally, tidy up

'————————————————————————————————-
' remove first two rows

Range("A1").EntireRow.Delete

Range("A1").EntireRow.Delete
' adjust column widths

Columns("A:A").EntireColumn.AutoFit

Columns("B:B").EntireColumn.AutoFit
' go home
Range("A1").Select
' query save
Application.GetSaveAsFilename
End Sub

Take a look at Mike's VBA. Do you have any suggestions for improvement?

About

Mark W. Kaelin has been writing and editing stories about the IT industry, gadgets, finance, accounting, and tech-life for more than 25 years. Most recently, he has been a regular contributor to BreakingModern.com, aNewDomain.net, and TechRepublic.

Editor's Picks