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 Kaelin is a CBS Interactive Senior Editor for TechRepublic. He is the host for the Microsoft Windows and Office blog, the Google in the Enterprise blog, the Five Apps blog and the Big Data Analytics blog.

19 comments
andrew.brewster@environment-agency.gov.uk
andrew.brewster@environment-agency.gov.uk

Copy the original at the top of the page, paste it into a new Notepad document with line formatting preserved (turn off Word Wrap). Make the corrections, and save as something like 'SysInfoFormat.bas', setting the file type to 'All Files', so Notepad doesn't add '.txt'. to the file name. Then just Import the file into the VBEditor and the 'Attribute...' line at the top will be correctly processed

services.online
services.online

Mark Is there any way to provide this macro as a download, supplying it like this removes all the indenting Mike Radmore

services.online
services.online

@stuart... The error is caused by the absence of a blank first row, which occurs in some versions of Excel/Windows; fixed in the full code below @dvenus Thanks for the link to the CSV Tool, I got the tool but haven't played with it yet.. Full updated macro listing follows. Note: The first line enables Import into VBA without having to Insert a Module, it creates a Module of the specified name. If you don't want to call the module SysInfo just edit the first line to a name of your choice. If you choose Copy/Paste into an existing module remove the first line before Copy or after Paste Once again: Let me know if there are any problems - I'm retired so have plenty of time 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 ' NOTE : This was written in Excel 2003 under WinXP SP3 _ and might need modification to cope with later _ versions of Excel and/or Windows ' Usage : Open a command window ' Execute this command: ' systeminfo /fo csv > .csv _ using appropriate values for filepath & filename ' Open the .csv file in Excel ' Run this macro, which operates on the active _ worksheet and therefore should cater for multiple _ .csv files in mutiple sheets (not tested) '----------------------------------------------------------------- Sub Tidy() '------------ EDIT THESE CONSTANTS AS REQUIRED ------------------- Const HOTFIX_STR = "Hotfix(s)" ' hotfix field name in user's OS Const NIC_STR = "NetWork Card(s)" ' ditto for network cards '----------------------------------------------------------------- Dim Fields As Integer ' count of fieldnames (cols to be processed) Dim CommaPos As Integer Dim ActiveCellValue As String ' If first row is blank then delete entire row If Range("A1").Value = "" Then Range("A1").EntireRow.Delete '----------------------------------------------------------------- ' This code transposes row 1 to column A, and row 2 to column B '----------------------------------------------------------------- ' NOTE: A Paste Special using Transpose:=True stops at the first _ blank cell and causes an error when processing the hotfix _ field values (because they haven't been transposed), hence the _ code below which transposes the cells 'by hand' as it were ' Get the number of occupied columns = field names Fields = Range("A1", Range("A1").End(xlToRight)).Columns.Count ' Copy each field name to a cell in Col A Transpose Fields:=Fields, RowOffset:=2, ColOffset:=0 ' Copy each field value to a cell in Col B corresponding to its _ field name in Col A after activating the field values in row 2 ActiveCell.Offset(1, 0).Activate Transpose Fields:=Fields, RowOffset:=1, ColOffset:=1 '----------------------------------------------------------------- ' 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 Search FieldName:=HOTFIX_STR ' 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 And CommaPos > 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 ' NOTE: This single-cell/multiple-value set should behave the _ same as the hotfixes but (on my system) sysinfo terminates _ the hotfixe values with a comma and doesn't do so for the NIC _ values ' However my hotfix values are truncated: count of hotfixes = 395 _ fieldname/fieldvalue pairs = 248; why? I don't know, but watch _ out for this discrepancy) '----------------------------------------------------------------- ' Find the cell with the appropriate fieldname, in case it varies _ from O/S to O/S Search FieldName:=NIC_STR ' 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 containing the raw input 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 '================================================================= ' Transpose a row of cells to a column of cells '----------------------------------------------------------------- Sub Transpose(Fields As Integer, _ RowOffset As Integer, _ ColOffset As Integer) Dim Field As Integer ' an individual field With ActiveCell For Field = 0 To Fields - 1 .Offset(Field + RowOffset, ColOffset).Value = _ .Offset(0, Field).Value Next Field End With End Sub '================================================================= ' Locate the fieldname of a single-cell/multiple-value cell '----------------------------------------------------------------- Sub Search(FieldName As String) ' Start at A1 Range("A1").Select ' Find the field name Cells.Find( _ What:=FieldName, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False _ ).Activate ' Activate the next column that receives the values ActiveCell.Offset(0, 1).Activate End Sub

dvenus
dvenus

Hi! You might want to check out a really nice CSV tool called CSVED at http://csved.sjfrancke.nl/. With this tool you could examine the CSV file and fix it before importing it into Excel. Can someone please post an updated version of the VBA code with all the changes in it? Thanks! Dave

stuartc
stuartc

Fails on with block not set on Cells.Find( _ What:=HOTFIX_STR, _ in 2010

services.online
services.online

@RickC998 The hotfix list ends with a comma (on my machine, anyway) which doesn't cause a problem until there's a following space; then I get the error you describe: Change this line: If Len(ActiveCellValue) > 0 Then to this: If Len(ActiveCellValue) > 0 And CommaPos > 0 Then and all should be good. Let me know. Mike @Mark Kaelin Thanks for the mug, etc, coffee never tasted so good :-)

Mark W. Kaelin
Mark W. Kaelin

Thanks for continuing the conversation Mike. You are presenting a good crash course in VBA.

RickC998
RickC998

@Mike Radmore The revised code now handles a blank field against 'Registered Organization' but, sorry, I'm still getting "Runtime error '5': Invalid procedure call or argument" further on. The loop finishes adding the last of the hotfixes to column B then it adds a blank row (line 80 - "Selection.EntireRow.Insert") to the end of the hotfix list. It's at this point in the loop (line 81 - "ActiveCell.Value = Left(ActiveCellValue, CommaPos - 1)") that the error occurs, i.e., as the hotfix loop should end and the code begin processing the "Network Card(s)" data.

services.online
services.online

Replace the code between this comment: "this code transposes row 1 to column A, and row 2 to column B" and the following comment with the following code, which moves each cell individually and therefore copes with blank cell values (sorry, but I don't know if this blog permits font editing for code, etc): First add these two declarations: Dim Fields As Integer ' count of fields Dim Field As Integer ' an individual field ' get the number of occupied columns = field names Fields = Range("A1", Range("A1").End(xlToRight)).Columns.Count ' copy each field name to a cell in Col A With ActiveCell For Field = 0 To Fields - 1 .Offset(Field + 2, 0).Value = .Offset(0, Field).Value Next Field End With ' copy each field value to a cell in Col B corresponding to its _ field name in Col A ActiveCell.Offset(1, 0).Activate With ActiveCell For Field = 0 To Fields - 1 .Offset(Field + 1, 1).Value = .Offset(0, Field).Value Next Field End With That's it. It should cater for blank cells. @RickC998 "Runtime error '5': Invalid procedure call or argument" seems to be caused by the Hotfix list being blank, which is caused by the truncation of field values due to a blank field value in any preceding cell. Thanks for your comments - glad my 45 years as an IT professional has finally paid off :-) Mike Radmore

Juergen Hartl
Juergen Hartl

Would it not be a lot easier to export systeminfo.exe to a plain text file (systeminfo >sysinfo.txt) and the open it in Excel, as a fixed length text file?

Mark W. Kaelin
Mark W. Kaelin

Do you have some suggestions for improving Mike's VBA code? Do you have a different approach to the problem?

services.online
services.online

@RickC998 Sorry Rick, this must be another version difference. I now see that my hotfix list is in fact truncated by sysinfo. If you email me the raw .csv file from sysinfo at: techrepublic.radmore@virginmedia.com I'll see what I can do. Mike

services.online
services.online

@Juergen Hartl I agree I was unfamiliar with sysinfo and simply followed Mark's instructions Your way is much simpler Mike Radmore

RickC998
RickC998

I'm using Excel 2007. The first line (Attribute VB_Name = "SysInfo") isn't recognised as valid VBA so I commented it out. Subsequent code failed with error "Runtime Error '91': Object variable or With block variable not set". Using 'Step Into' to debug, it appeared the function starting at line 47 was the problem. So I started going through the code line by line and saw line 24 "delete blank row at start of file". What blank row? There isn't a blank row at the start of the file. So, I commented line 24 out as well and re-loaded the CSV file again. Yay, now it works... except it doesn't. It transposes the first row to a column then only transposes a part of row 2 to a column, failing at the first blank cell it finds. So, reloaded the CSV file once again, put a dummy value in the blank cell and tried again. Yay, it works!... except it doesn't. It gets to listing the hotfixes then fails again with "Runtime error '5': Invalid procedure call or argument". I got bored at this point and gave up. My tip for improvement? Try testing the code before publishing it.

RickC998
RickC998

@Mike Radmore I'm very grateful for your kind offer but think I've already taken up too much of your time on something that isn't very important. I don't have access to Excel 2003 but I've tested with Excel 2000 and 2007 on Win XP and Win 7 and found another issue. Suffice to say, I think I'll use Juergen's method for simplicity. Thanks again for showing me how to transposition rows to columns... it's much appreciated. Regards, Rick

services.online
services.online

@RickC998 Sorry it didn't work for you Rick. It was written in Excel 2003 under WinXP SP3 in which sysinfo does produce a blank line at the start of its output. The line "Attribute VB_Name = "SysInfo" is put there by VBA when the macro is Exported, and is required in order to Import into VBA as I assumed that Mark would provide a download link. My apologies. As to failing on blank cells, I can well imagine that it might do so but the existence of blank cells presumably indicates that there's a fieldname without a corresponding value, which doesn't happen for me. As for "Runtime error '5': Invalid procedure call or argument" I can't comment without seeing the output from sysinfo. Sorry. Mike Radmore PS: See my reply to Juergen Hartl's "No VBA required" - his method is much simpler.

OkeyM
OkeyM

I am also running Office 2007 and the code worked as specified. Would be nice though if I did not have to manually generate the sysinfo.csv file.

Mark W. Kaelin
Mark W. Kaelin

I said you would have to change the code to make it work for your particular situation. This is a code example, nothing more.

RickC998
RickC998

@Mike Radmore I suspected my experience may be due to differences between versions (Excel and OS), hence my "Try testing the code before publishing it" comment which was directed at Mark Kaelin, not you Mike. In my opinion the article should have included this info. Juergen's method IS simpler but yours is more elegant and taught me a lot about range selection and transposition of rows to columns... so sincere thanks for that. PS - The blank cell was due to no entry against 'Registered Organization', which the registration process allows.

Editor's Picks