Script writing to excel (unknown # of Columns)

I am writing a script to gather hardware information from a list of PC's. Currently the script is just asking one at a time for testing purposes. I have it gather all the data and list it in a row however I noticed an issue since my boss wants me to export it to excel. When it comes to the network card and software section as it pulls all the network cards and all the software installed. I know for certain that there could be anywhere from 1 card to 3 cards but a unknown number of software packages. The problem is I don't know how you would code it so that whether there are 1, 3, or unknown number it uses the same number of columns and the next section starts on the same column everytime. Fortunatly the software list is at the end so i don't have to worry about what is after it because at the end it will go to the next pc in the list.

Any thoughts?
 Here is my current code that exports the list to a text file on the desktop.
Dim strIP, strSubnet, strDescription, lnX, strcomputer, objwmiservice, colitems, objitems

strComputer  =  InputBox ("Enter the Computer name to get its Inventory:-")
lnX = 1

' ********************************************************************************************
'Section to create folder

Dim oShell, oEnv, oFS, strPath

Set oShell = CreateObject("wscript.Shell")
set oFS = CreateObject("scripting.FileSystemObject")
Set oEnv = oShell.Environment("Process")

strPath = oEnv("USERPROFILE") & "\Desktop\Inventory\"
if not oFS.FolderExists(strPath) then oFS.CreateFolder(strPath)

' ********************************************************************************************
'Section to change a filename using timestamps
strMonth  =  DatePart("m", Now())
strDay  =  DatePart("d",Now())

if Len(strMonth) = 1 then
 strMonth  =  "0" & strMonth
else
 strMonth  =  strMonth
end if

if Len(strDay) = 1 then
 strDay  =  "0" & strDay
else
 strDay  =  strDay
end if

strFileName  =  DatePart("yyyy",Now()) & strMonth & strDay 
strFileName  =  Replace(strFileName,":","")
' ********************************************************************************************

'Variable Declarations
Const ForAppending  =  8

'Get CompName
Set objWMIService  =  GetObject("winmgmts:" & "{impersonationLevel = impersonate}!\\" & strComputer & "\root\cimv2")

' ********************************************************************************************
'Get Operation System & Processor Information
' ********************************************************************************************
Set colItems  =  objWMIService.ExecQuery("Select * from Win32_Processor")
For Each objItem in colItems
     CompName  =  objItem.SystemName
Next

Set objFSO  =  CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(strPath & CompName & "_" & "_Inventory.txt") then
 WScript.Quit
end if

'Set the file location 
Set objFSO  =  CreateObject("Scripting.FileSystemObject")
Set objTextFile  =  objFSO.OpenTextFile(strPath & CompName & "_" & "Inventory.txt", ForAppending, True)

' ********************************************************************************************
'Get Processor Information
' ********************************************************************************************
Set colItems  =  objWMIService.ExecQuery("Select * from Win32_Processor")
For Each objItem in colItems
	If lnX = 1 Then
     objTextFile.Write objItem.SystemName & VBTab
     objTextFile.Write objItem.Name & VBTab
     lnX = lnX + 1
    End If
Next

' ********************************************************************************************
'Get Computer Manufacturer and RAM details
' ********************************************************************************************
Set colSystems  =  objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
For Each objSystems In colSystems
  objTextFile.Write objSystems.Manufacturer & VBTab
  objTextFile.Write objSystems.Model & VBTab
  objTextFile.Write objsystems.Domain & VBTab
  objTextFile.Write Round (objSystems.TotalPhysicalMemory / 1048576, 0) & " MB "& VBTab
Next

' ********************************************************************************************
' Get the Serial number/ Service Tag of the system
' ********************************************************************************************
Set colSMBIOS = objWMIService.ExecQuery("Select * from Win32_SystemEnclosure") 
For Each objSMBIOS in colSMBIOS 
	objTextFile.Write objSMBIOS.SerialNumber & VBTab
Next 

' ********************************************************************************************
'Get User Information
' ********************************************************************************************
Set colItems  =  objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
For Each objItem in colItems
     objTextFile.Write objItem.UserName & VBTab
Next

' ********************************************************************************************
'Get BIOS Information
' ********************************************************************************************
Set colBIOS  =  objWMIService.ExecQuery("Select * from Win32_BIOS")
For each objItem in colBIOS
    objTextFile.Write objItem.Manufacturer & VBTab
    objTextFile.Write objItem.Name & VBTab
Next

' ********************************************************************************************
'Get OS Information
' ********************************************************************************************
Set colSettings  =  objWMIService.ExecQuery("SELECT * FROM Win32_OperatingSystem")
For Each objOperatingSystem in colSettings
    objTextFile.Write objOperatingSystem.Caption & VBTab
    objTextFile.Write "Service Pack "& objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion & VBTab
Next
 
' ********************************************************************************************
'Get Logical Disk Size and Partition Information
' ********************************************************************************************
Set colDisks  =  objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType  =  3")
For Each objDisk in colDisks
    intFreeSpace  =  objDisk.FreeSpace
    intTotalSpace  =  objDisk.Size
    pctFreeSpace  =  intFreeSpace / intTotalSpace
    objTextFile.Write Round((objDisk.Size/1000000000),4) & " GB" & VBTab
	objTextFile.Write Round((intFreeSpace/1000000000)*1.024,4) & " GB Free Space" & VBTab
Next

' ********************************************************************************************
'Get NETWORK ADAPTERS information
' ********************************************************************************************
Set colNicConfigs  =  objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled  =  True")
For Each objNicConfig In colNicConfigs
	strDescription = objNicConfig.Caption
	strMACAddress = objNicConfig.MACAddress
	strDHCP = objNicConfig.DHCPEnabled
	
	For Each strIPAddress In objNicConfig.IPAddress
		If Not IsNull(objNicConfig.IPAddress) Then 
			For i=LBound(objNicConfig.IPAddress) to UBound(objNicConfig.IPAddress)
				If InStr(objNicConfig.IPAddress(i),":") = 0 Then
				strIPAddress = objNicConfig.IPAddress(i)
				End If
				strIP = strIPAddress
			Next
	    End If
	Next

	objTextFile.Write strDescription & VBTab
	objTextFile.Write strIP & VBTab
	objTextFile.Write strMACAddress & VBTab
	objTextFile.Write strDHCP & VBTab 
	
Next
Set colNicConfigs  = NOTHING

' ********************************************************************************************
'Get GRAPHICS ADAPTERS information
' ********************************************************************************************
Set colGraphics  =  objWMIService.ExecQuery ("Select * from Win32_DisplayControllerConfiguration")
For Each objGraphics in colGraphics
    objTextFile.Write objGraphics.Name & VBTab
Next

' ********************************************************************************************
' Get the list of Installed software
' ********************************************************************************************
Set colSoftware  =  objWMIService.ExecQuery("Select * from Win32_Product")
For Each objSoftware in colSoftware
    objTextFile.Write objSoftware.Description & VBTab
Next

'Close text file after writing logs
objTextFile.Write VbCrLf
objTextFile.Close

'Clean Up
WScript.Echo "Inventory Complete "

Open in new window

EverwulfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ElrondCTCommented:
You should also anticipate that there could be multiple disks on some computers.

I understand you to be saying that you want to have columns to show, for instance, the operating system, processor, RAM, serial number, etc., all of which have a single entry per computer (each computer is shown on a separate line). Then you have disks, network adapters, and software installed, each of which could have one or several entries, which you also want to show one per column.

The only way to make sure that the next set starts in a particular column is to pad out as much space (i.e., add columns) as you could "possibly" use (I put possibly in quotes because my experience is you either don't put enough out, or you put out so many that you end up with big blank spaces that make it hard to see the actual data).

You might want to think about a third dimension: create tabs for each of disks, network adapters, and software installed. To be sure, there's no way to see everything for a particular computer on a single line, but it becomes easy to select each set of multiple items.
0
EverwulfAuthor Commented:
I think I understand what you getting at. You’re saying that for the information that I know it going to be a fixed number of cells I can have in one tab but for the items that could vary on the number of cells they have each of those items should be on a separate worksheets of their own. So like Hard drives, Network Cards and Software would all be completely different worksheets.

Correct?
0
ElrondCTCommented:
That's right. I think that will make it the easiest to find the various types of information in a consistent way. One question, though, would be whether you'd want to set up the Software tab so that the columns are filled in order, or they're ordered by specific software name. So, for instance, is column B Microsoft Office for everyone (checked or blank), or could it be Office for Jane, Quickbooks for Charley, AutoCAD for George? (And George's Office might be in column B.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.