mbritojr
asked on
VBSCRIPT: "Input past EOF" error
This is a script from www.windowsITpro.com http://www.windowsitpro.com/Article/ArticleID/39111/39111.html that worked for a while and worked great but now has started failing. It has never been modified. I don't know what the problem is. It fails at random points in the file processing with the following error:
Script: \\server\share\buildreport .vbs
Line: 278
Char: 5
Error: Input past end of file
Code: 800A003E
Source: Microsoft VB Script Runtime Error
Line 278 is starred...
I can provide examples of the files being proccessed if needed. I normally wouldn't ask a question this broad or post code like this but I really am at a total loss... Thanks for any help...
**********************STAR T CODE*******************
'Constants used in this script
Const ForReading = 1
Const xlNormal = &HFFFFEFD1
Const xlWBATWorksheet = &HFFFFEFB9
Const xlRight = &HFFFFEFC8
Const xlOtherSessionChanges = 2
'Initialize global variables
strInvFilePath = "\\server\share\profiles\"
strReportPath = "\\server\share\report\"
'New sheet names. This is outside of a specific sub-routine
'because multiple subroutines use it.
arrWBNames = Array("Computer Systems","Page Files","RAM", _
"SCSI Controllers","IDE Controllers","Disk SerNums", _
"Removable Media","Fixed Disks","Processors", _
"NICs","Monitors","Video Adapters","MotherBoards", _
"BIOS")
'Creat Excel workbook globally because it's used by several routines
Set objExcel = CreateObject("Excel.Applic ation")
Set objWorkBook = objExcel.Workbooks.Add 'new
Call PrepareWorkSheet
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set objFolder = objFso.GetFolder(strInvFil ePath)
Set objFiles = objFolder.Files
'intRowPosition = 2 'Inventory data begins to be written to row 2 of each worksheet
For Each File in objFiles
If Mid(File.Name,1,9) = "HrdWrInv_" Then
strFileName = File.Name
'Use the value of computer name for the first instance of each row of data.
strComputerName = Mid(strFileName,10,(Instr( strFileNam e,".") - 10))
WScript.Echo "Processing inventory for " & strComputerName
Set objTextFile = objFSO.OpenTextFile(strInv FilePath & strFileName,ForReading)
'Using a sepearate text file object because you don't want to put the
'cursor at the end of the file and the readall method will do this
'and then you can't use readline, etc. to move through the file's contents.
Set objFileForSearching = objFSO.OpenTextFile(strInv FilePath & strFileName,ForReading)
'read the file and use this for determining if a particular section head is
'found anywhere in the file.
strTextFile = objFileForSearching.ReadAl l
arrItems = Array("SkipLine","SkipLine ","Caption ","Version ")
Call GenReport("OSInformation:" ,strTextFi le, _
"CreationClassName: Win32_OperatingSystem",arr Items)
arrItems = Array("SkipLine","Manufact urer","Mod el", _
"SkipLine","NumberOfProces sors","Sys temType"," TotalPhysi calMemory" )
Call GenReport("ComputerSystem: ",strTextF ile, _
"CreationClassName: Win32_ComputerSystem",arrI tems)
arrItems = Array("MaximumSize","Name" )
Call GenReport("PageFileSetting :",strText File, _
"MaximumSize:",arrItems)
arrItems = Array("SkipLine","MaxCapac ity","Tag" )
Call GenReport("PhysicalMemoryA rray:",str TextFile, _
"CreationClassName: Win32_PhysicalMemoryArray" ,arrItems)
arrItems = Array("SkipLine","Descript ion","Skip Line","Man ufacturer" ,"Name")
Call GenReport("SCSIController: ",strTextF ile, _
"CreationClassName: Win32_SCSIController",arrI tems)
arrItems = Array("SkipLine","Descript ion","Skip Line","Man ufacturer" ,"Name")
Call GenReport("IDEController:" ,strTextFi le, _
"CreationClassName: Win32_IDEController",arrIt ems)
arrItems = Array("SkipLine","SerialNu mber","Ski pLine")
Call GenReport("PhysicalMedia:" ,strTextFi le, _
"CreationClassName: Not available",arrItems)
arrItems = Array("SkipLine","Descript ion","Devi ceID","Ski pLine")
Call GenReport("LogicalDisk:",s trTextFile , _
"CreationClassName: Win32_LogicalDisk",arrItem s)
arrItems = Array("Caption","SkipLine" ,"Descript ion","Skip Line", _
"InterfaceType","Manufactu rer","Medi aType","Mo del","Part itions", _
"Size")
Call GenReport("DiskDrive:",str TextFile, _
"CreationClassName: Win32_DiskDrive",arrItems)
arrItems = Array("SkipLine","DeviceID ","ExtCloc k","Manufa cturer", _
"MaxClockSpeed","SkipLine" ,"Revision ","Version ")
Call GenReport("Processor:",str TextFile, _
"CreationClassName: Win32_Processor",arrItems)
arrItems = Array("AdapterType","SkipL ine","Skip Line", _
"MACAddress","Manufacturer ","Product Name")
Call GenReport("NetworkAdapter: ",strTextF ile, _
"CreationClassName: Win32_NetworkAdapter",arrI tems)
arrItems = Array("SkipLine","Descript ion","Skip Line","Mon itorType")
Call GenReport("DesktopMonitor: ",strTextF ile, _
"CreationClassName: Win32_DesktopMonitor",arrI tems)
arrItems = Array("AdapterRAM","SkipLi ne","SkipL ine", _
"MaxRefreshRate","Name")
Call GenReport("VideoController :",strText File, _
"CreationClassName: Win32_VideoController",arr Items)
arrItems = Array("SkipLine","Descript ion","Manu facturer", _
"Product","SkipLine")
Call GenReport("BaseBoard:",str TextFile, _
"CreationClassName: Win32_BaseBoard",arrItems)
arrItems = Array("Manufacturer","Name ","SMBIOSB IOSVersion ", _
"SMBIOSMajorVersion","SMBI OSMinorVer sion","Sof twareEleme ntID", _
"SkipLine","SkipLine","Ver sion")
Call GenReport("BIOS:",strTextF ile, _
"SoftwareElementID",arrIte ms)
End If 'End reading inventory file
Next
objWorkBook.Save
objWorkBook.Sheets("Comput er Systems").Select
objExcel.Visible = True
'*****Subroutines and Functions*********
Sub GenReport(SectionHead,Text File,Uniqu eLabel,Rep ortItemsAr ray)
Call MoveToSectionHead(SectionH ead,TextFi le)
intInstances = FindValues(UniqueLabel,Tex tFile)
If intInstances > 0 Then
For i = 1 to intInstances
'Determine the next available row in the current worksheet
intRow = NextRowCount(SectionHead)
'Move over to the fourth column if the SectionHead is "ComputerSystem:"
'because this sheet first gets 3 cols of data from the OSInformation section.
If SectionHead = "ComputerSystem:" Then
intColPosition = 4
Else
intColPosition = 2
End If
For Each Item in ReportItemsArray
If item = "SkipLine" Then
'Doing this because you don't want to advance to the next
'column for a skipped line.
intColPosition = intColPosition - 1
objTextFile.SkipLine
Else
strItem = ReadVal(Item)
If strItem = " " Then
strItem = "Not Listed"
End If
Call InsertValues(SectionHead,i ntRow,intC olPosition ,strItem,i )
End If
intColPosition = intColPosition + 1
Next
objTextFile.SkipLine
Next
End If
End Sub
'Select the worksheet based on the section of data being read
Function SelectWorkSheet(SectionHea d)
arrSectionNames = Array("OSInformation:Compu terSystem: ", _
"PageFileSetting:","Physic alMemoryAr ray:","SCS IControlle r:", _
"IDEController:","Physical Media:","L ogicalDisk :","DiskDr ive:", _
"Processor:","NetworkAdapt er:","Desk topMonitor :", _
"VideoController:","BaseBo ard:","BIO S:")
If SectionHead = "OSInformation:" Or _
SectionHead = "ComputerSystem:" Then
SelectWorkSheet = 0
Else
i=0
Do Until arrSectionNames(i) = SectionHead
i=i + 1
Loop
SelectWorkSheet = i
End If
End Function
Function NextRowCount(SectionHead)
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
If SectionHead = "ComputerSystem:" Then
NextRowCount = objExcel.ActiveSheet.UsedR ange.Rows. Count
Else
NextRowCount = objExcel.ActiveSheet.UsedR ange.Rows. Count + 1
End If
End Function
'this is the routine that inserts values in the spreadsheet
Sub InsertValues(SectionHead,R owPosition ,ColPositi on,Item,In stances)
Select Case SectionHead
Case "OSInformation:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Case "ComputerSystem:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:H1")
Case "PageFileSetting:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:C1")
Case "PhysicalMemoryArray:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:C1")
Case "SCSIController:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:D1")
Case "IDEController:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:D1")
Case "PhysicalMedia:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:B1")
Case "LogicalDisk:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:C1")
Case "DiskDrive:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:I1")
Case "Processor:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:G1")
Case "NetworkAdapter:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:E1")
Case "DesktopMonitor:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:C1")
Case "VideoController:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:D1")
Case "BaseBoard:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:D1")
Case "BIOS:"
objWorkBook.Sheets(arrWBNa mes(Select WorkSheet( SectionHea d))).Selec t
Call FillCells(ColPosition,RowP osition,It em,Instanc es)
Call FormatWorkSheet("A1:H1")
End Select
End Sub
Sub FillCells(ColPosition,RowP osition,It em,Instanc es)
'An instance value of 0 means there's nothing to write
'the section exists in the text file but there's no data
'in it.
If Instances = 1 Then
objExcel.Cells(RowPosition ,1).Value = strComputerName
objExcel.Cells(RowPosition ,ColPositi on).Value = Trim(Item)
ElseIf Instances > 1 Then
objExcel.Cells(RowPosition ,ColPositi on).Value = Trim(Item)
End If
End Sub
Sub MoveToSectionHead(HeadName ,TextFile)
Set objRegExp = New RegExp
objRegExp.Pattern = HeadName 'Look for this value
objRegExp.IgnoreCase = False 'Look for value w/specific case
objRegExp.Global = False 'Find only the first match
Set arrSectionHead = objRegExp.Execute(TextFile )
If objRegExp.Test(TextFile) <> False Then
strText = objTextFile.ReadLine
objRegExp.Execute(strText)
Do Until objRegExp.Test(strText) = True
strText = objTextFile.ReadLine
objRegExp.Execute(strText)
Loop
End If
End Sub
'This tells you how many values you have in a section of hardware inventory
'data. Knowing this then tells you what you have to read from the section.
Function FindValues(Label,TextFile)
Set objRegExp2 = New RegExp
objRegExp2.Pattern = Label 'Look for this value
objRegExp2.IgnoreCase = False 'Look for value w/specific case
objRegExp2.Global = True 'Find all matches in this section
Set arrValues = objRegExp2.Execute(TextFil e)
i = 0
For Each Value in arrValues
i = i + 1
Next
FindValues = i
End Function
Function ReadVal(Label)
'************************* ****LINE 278*********************** ********** *
ReadVal = Mid(objTextFile.ReadLine,L en(Label) + 3)
'************************* ****LINE 278*********************** ********** *
End Function
Sub PrepareWorkSheet()
objWorkBook.SaveAs strReportPath & "InvReport.xls",xlNormal
objExcel.SheetsInNewWorkBo ok = 14
'Ensure that the worksheet has enough sheets to run
'correctly. If not, use the Add method to add worksheets.
If objWorkBook.Sheets.Count < 14 then
For i = objWorkBook.Sheets.Count to 14
objWorkBook.Sheets.Add()
Next
End if
'iterate the array of new sheet names and rename the sheets
i=1
For Each WBName in arrWBNames
RenameSheets "Sheet" & i,WBName
i = i + 1
Next
'Column naming Section
'Name the Computer Systems columns
arrColumns = Array("Caption","Version", "Manufactu rer","Mode l", _
"# Processors","System Type","RAM")
Call NameColumns(0,arrColumns)
'Name the Page Files columns
arrColumns = Array("Maximum Size","Name")
Call NameColumns(1,arrColumns)
'Name the RAM columns
arrColumns = Array("Maximum Capacity","Tag")
Call NameColumns(2,arrColumns)
'Name the SCSI controllers columns
arrColumns = Array("Description","Manuf acturer"," Name")
Call NameColumns(3,arrColumns)
'Name the IDE controllers columns
arrColumns = Array("Description","Manuf acturer"," Name")
Call NameColumns(4,arrColumns)
'Name the Disk SerNums columns
arrColumns = Array("Serial Number")
Call NameColumns(5,arrColumns)
'Have to do this because Excel interprets a serial
'number (w/only numbers) as a numeric value rather
'than a string and then
'clips the value and uses and exponent designation
objWorkbook.ActiveSheet.Co lumns("B:B ").Select
objExcel.Cells.EntireColum n.NumberFo rmat = "@"
objExcel.Cells.HorizontalA lignment = xlRight
objExcel.Range("A1").Selec t
'Name the Removable Media columns
arrColumns = Array("Descripton","Device ID")
Call NameColumns(6,arrColumns)
'Name the Fixed Disk columns
arrcolumns = Array("Description","Devic e ID","Interface Type", _
"Manufacturer","Media Type","Model","# of Partitions","Size")
Call NameColumns(7,arrColumns)
'Name the Processors columns
arrColumns = Array("DeviceID","External Clock","Manufacturer", _
"Maximum Speed","Revision","Version ")
Call NameColumns(8,arrColumns)
'Name the NICs columns
arrColumns = Array("Adapter Type","MAC Address", _
"Manufacturer","Product Name")
Call NameColumns(9,arrColumns)
'Name the Monitors columns
arrColumns = Array("Description","Monit orType")
Call NameColumns(10,arrColumns)
'Name the Video Adapters columns
arrColumns = Array("Adapter Memory","Maximum Refresh Rate","Name")
Call NameColumns(11,arrColumns)
'Name the Motherboards columns
arrColumns = Array("Description","Manuf acturer"," Product")
Call NameColumns(12,arrColumns)
'Name the BIOS columns
arrColumns = Array("Manufacturer","Name ","BIOS Version", _
"Major Version","Minor Version","Software Element ID", _
"Version")
Call NameColumns(13,arrColumns)
'objWorkBook.Sheets("Compu ter Systems").Select
objWorkBook.Save
End Sub
Sub NameColumns(SheetNum,Colum nsArray)
objWorkBook.Sheets(arrWBNa mes(SheetN um)).Selec t
i=2
For Each ColumnHead in ColumnsArray
'All sheets will have ComputerName as the first column name
'so hard code it here.
objExcel.Cells(1,1).Value = "Computer Name"
objExcel.Cells(1,i).Value = ColumnHead
i = i + 1
Next
End Sub
'Rename each sheet
Sub RenameSheets(CurrentName,N ewName)
objWorkBook.Sheets(Current Name).Sele ct
objWorkbook.ActiveSheet.Na me = NewName
End Sub
'This is for formatting each worksheet.
Sub FormatWorkSheet(HeadCellRa nge)
objExcel.Range(HeadCellRan ge).Font.B old = True
objExcel.Cells.EntireColum n.AutoFit
objExcel.Cells.EntireRow.A utoFit
End Sub
Script: \\server\share\buildreport
Line: 278
Char: 5
Error: Input past end of file
Code: 800A003E
Source: Microsoft VB Script Runtime Error
Line 278 is starred...
I can provide examples of the files being proccessed if needed. I normally wouldn't ask a question this broad or post code like this but I really am at a total loss... Thanks for any help...
**********************STAR
'Constants used in this script
Const ForReading = 1
Const xlNormal = &HFFFFEFD1
Const xlWBATWorksheet = &HFFFFEFB9
Const xlRight = &HFFFFEFC8
Const xlOtherSessionChanges = 2
'Initialize global variables
strInvFilePath = "\\server\share\profiles\"
strReportPath = "\\server\share\report\"
'New sheet names. This is outside of a specific sub-routine
'because multiple subroutines use it.
arrWBNames = Array("Computer Systems","Page Files","RAM", _
"SCSI Controllers","IDE Controllers","Disk SerNums", _
"Removable Media","Fixed Disks","Processors", _
"NICs","Monitors","Video Adapters","MotherBoards", _
"BIOS")
'Creat Excel workbook globally because it's used by several routines
Set objExcel = CreateObject("Excel.Applic
Set objWorkBook = objExcel.Workbooks.Add 'new
Call PrepareWorkSheet
Set objFSO = CreateObject("Scripting.Fi
Set objFolder = objFso.GetFolder(strInvFil
Set objFiles = objFolder.Files
'intRowPosition = 2 'Inventory data begins to be written to row 2 of each worksheet
For Each File in objFiles
If Mid(File.Name,1,9) = "HrdWrInv_" Then
strFileName = File.Name
'Use the value of computer name for the first instance of each row of data.
strComputerName = Mid(strFileName,10,(Instr(
WScript.Echo "Processing inventory for " & strComputerName
Set objTextFile = objFSO.OpenTextFile(strInv
'Using a sepearate text file object because you don't want to put the
'cursor at the end of the file and the readall method will do this
'and then you can't use readline, etc. to move through the file's contents.
Set objFileForSearching = objFSO.OpenTextFile(strInv
'read the file and use this for determining if a particular section head is
'found anywhere in the file.
strTextFile = objFileForSearching.ReadAl
arrItems = Array("SkipLine","SkipLine
Call GenReport("OSInformation:"
"CreationClassName: Win32_OperatingSystem",arr
arrItems = Array("SkipLine","Manufact
"SkipLine","NumberOfProces
Call GenReport("ComputerSystem:
"CreationClassName: Win32_ComputerSystem",arrI
arrItems = Array("MaximumSize","Name"
Call GenReport("PageFileSetting
"MaximumSize:",arrItems)
arrItems = Array("SkipLine","MaxCapac
Call GenReport("PhysicalMemoryA
"CreationClassName: Win32_PhysicalMemoryArray"
arrItems = Array("SkipLine","Descript
Call GenReport("SCSIController:
"CreationClassName: Win32_SCSIController",arrI
arrItems = Array("SkipLine","Descript
Call GenReport("IDEController:"
"CreationClassName: Win32_IDEController",arrIt
arrItems = Array("SkipLine","SerialNu
Call GenReport("PhysicalMedia:"
"CreationClassName: Not available",arrItems)
arrItems = Array("SkipLine","Descript
Call GenReport("LogicalDisk:",s
"CreationClassName: Win32_LogicalDisk",arrItem
arrItems = Array("Caption","SkipLine"
"InterfaceType","Manufactu
"Size")
Call GenReport("DiskDrive:",str
"CreationClassName: Win32_DiskDrive",arrItems)
arrItems = Array("SkipLine","DeviceID
"MaxClockSpeed","SkipLine"
Call GenReport("Processor:",str
"CreationClassName: Win32_Processor",arrItems)
arrItems = Array("AdapterType","SkipL
"MACAddress","Manufacturer
Call GenReport("NetworkAdapter:
"CreationClassName: Win32_NetworkAdapter",arrI
arrItems = Array("SkipLine","Descript
Call GenReport("DesktopMonitor:
"CreationClassName: Win32_DesktopMonitor",arrI
arrItems = Array("AdapterRAM","SkipLi
"MaxRefreshRate","Name")
Call GenReport("VideoController
"CreationClassName: Win32_VideoController",arr
arrItems = Array("SkipLine","Descript
"Product","SkipLine")
Call GenReport("BaseBoard:",str
"CreationClassName: Win32_BaseBoard",arrItems)
arrItems = Array("Manufacturer","Name
"SMBIOSMajorVersion","SMBI
"SkipLine","SkipLine","Ver
Call GenReport("BIOS:",strTextF
"SoftwareElementID",arrIte
End If 'End reading inventory file
Next
objWorkBook.Save
objWorkBook.Sheets("Comput
objExcel.Visible = True
'*****Subroutines and Functions*********
Sub GenReport(SectionHead,Text
Call MoveToSectionHead(SectionH
intInstances = FindValues(UniqueLabel,Tex
If intInstances > 0 Then
For i = 1 to intInstances
'Determine the next available row in the current worksheet
intRow = NextRowCount(SectionHead)
'Move over to the fourth column if the SectionHead is "ComputerSystem:"
'because this sheet first gets 3 cols of data from the OSInformation section.
If SectionHead = "ComputerSystem:" Then
intColPosition = 4
Else
intColPosition = 2
End If
For Each Item in ReportItemsArray
If item = "SkipLine" Then
'Doing this because you don't want to advance to the next
'column for a skipped line.
intColPosition = intColPosition - 1
objTextFile.SkipLine
Else
strItem = ReadVal(Item)
If strItem = " " Then
strItem = "Not Listed"
End If
Call InsertValues(SectionHead,i
End If
intColPosition = intColPosition + 1
Next
objTextFile.SkipLine
Next
End If
End Sub
'Select the worksheet based on the section of data being read
Function SelectWorkSheet(SectionHea
arrSectionNames = Array("OSInformation:Compu
"PageFileSetting:","Physic
"IDEController:","Physical
"Processor:","NetworkAdapt
"VideoController:","BaseBo
If SectionHead = "OSInformation:" Or _
SectionHead = "ComputerSystem:" Then
SelectWorkSheet = 0
Else
i=0
Do Until arrSectionNames(i) = SectionHead
i=i + 1
Loop
SelectWorkSheet = i
End If
End Function
Function NextRowCount(SectionHead)
objWorkBook.Sheets(arrWBNa
If SectionHead = "ComputerSystem:" Then
NextRowCount = objExcel.ActiveSheet.UsedR
Else
NextRowCount = objExcel.ActiveSheet.UsedR
End If
End Function
'this is the routine that inserts values in the spreadsheet
Sub InsertValues(SectionHead,R
Select Case SectionHead
Case "OSInformation:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Case "ComputerSystem:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:H1")
Case "PageFileSetting:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:C1")
Case "PhysicalMemoryArray:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:C1")
Case "SCSIController:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:D1")
Case "IDEController:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:D1")
Case "PhysicalMedia:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:B1")
Case "LogicalDisk:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:C1")
Case "DiskDrive:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:I1")
Case "Processor:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:G1")
Case "NetworkAdapter:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:E1")
Case "DesktopMonitor:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:C1")
Case "VideoController:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:D1")
Case "BaseBoard:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:D1")
Case "BIOS:"
objWorkBook.Sheets(arrWBNa
Call FillCells(ColPosition,RowP
Call FormatWorkSheet("A1:H1")
End Select
End Sub
Sub FillCells(ColPosition,RowP
'An instance value of 0 means there's nothing to write
'the section exists in the text file but there's no data
'in it.
If Instances = 1 Then
objExcel.Cells(RowPosition
objExcel.Cells(RowPosition
ElseIf Instances > 1 Then
objExcel.Cells(RowPosition
End If
End Sub
Sub MoveToSectionHead(HeadName
Set objRegExp = New RegExp
objRegExp.Pattern = HeadName 'Look for this value
objRegExp.IgnoreCase = False 'Look for value w/specific case
objRegExp.Global = False 'Find only the first match
Set arrSectionHead = objRegExp.Execute(TextFile
If objRegExp.Test(TextFile) <> False Then
strText = objTextFile.ReadLine
objRegExp.Execute(strText)
Do Until objRegExp.Test(strText) = True
strText = objTextFile.ReadLine
objRegExp.Execute(strText)
Loop
End If
End Sub
'This tells you how many values you have in a section of hardware inventory
'data. Knowing this then tells you what you have to read from the section.
Function FindValues(Label,TextFile)
Set objRegExp2 = New RegExp
objRegExp2.Pattern = Label 'Look for this value
objRegExp2.IgnoreCase = False 'Look for value w/specific case
objRegExp2.Global = True 'Find all matches in this section
Set arrValues = objRegExp2.Execute(TextFil
i = 0
For Each Value in arrValues
i = i + 1
Next
FindValues = i
End Function
Function ReadVal(Label)
'*************************
ReadVal = Mid(objTextFile.ReadLine,L
'*************************
End Function
Sub PrepareWorkSheet()
objWorkBook.SaveAs strReportPath & "InvReport.xls",xlNormal
objExcel.SheetsInNewWorkBo
'Ensure that the worksheet has enough sheets to run
'correctly. If not, use the Add method to add worksheets.
If objWorkBook.Sheets.Count < 14 then
For i = objWorkBook.Sheets.Count to 14
objWorkBook.Sheets.Add()
Next
End if
'iterate the array of new sheet names and rename the sheets
i=1
For Each WBName in arrWBNames
RenameSheets "Sheet" & i,WBName
i = i + 1
Next
'Column naming Section
'Name the Computer Systems columns
arrColumns = Array("Caption","Version",
"# Processors","System Type","RAM")
Call NameColumns(0,arrColumns)
'Name the Page Files columns
arrColumns = Array("Maximum Size","Name")
Call NameColumns(1,arrColumns)
'Name the RAM columns
arrColumns = Array("Maximum Capacity","Tag")
Call NameColumns(2,arrColumns)
'Name the SCSI controllers columns
arrColumns = Array("Description","Manuf
Call NameColumns(3,arrColumns)
'Name the IDE controllers columns
arrColumns = Array("Description","Manuf
Call NameColumns(4,arrColumns)
'Name the Disk SerNums columns
arrColumns = Array("Serial Number")
Call NameColumns(5,arrColumns)
'Have to do this because Excel interprets a serial
'number (w/only numbers) as a numeric value rather
'than a string and then
'clips the value and uses and exponent designation
objWorkbook.ActiveSheet.Co
objExcel.Cells.EntireColum
objExcel.Cells.HorizontalA
objExcel.Range("A1").Selec
'Name the Removable Media columns
arrColumns = Array("Descripton","Device
Call NameColumns(6,arrColumns)
'Name the Fixed Disk columns
arrcolumns = Array("Description","Devic
"Manufacturer","Media Type","Model","# of Partitions","Size")
Call NameColumns(7,arrColumns)
'Name the Processors columns
arrColumns = Array("DeviceID","External
"Maximum Speed","Revision","Version
Call NameColumns(8,arrColumns)
'Name the NICs columns
arrColumns = Array("Adapter Type","MAC Address", _
"Manufacturer","Product Name")
Call NameColumns(9,arrColumns)
'Name the Monitors columns
arrColumns = Array("Description","Monit
Call NameColumns(10,arrColumns)
'Name the Video Adapters columns
arrColumns = Array("Adapter Memory","Maximum Refresh Rate","Name")
Call NameColumns(11,arrColumns)
'Name the Motherboards columns
arrColumns = Array("Description","Manuf
Call NameColumns(12,arrColumns)
'Name the BIOS columns
arrColumns = Array("Manufacturer","Name
"Major Version","Minor Version","Software Element ID", _
"Version")
Call NameColumns(13,arrColumns)
'objWorkBook.Sheets("Compu
objWorkBook.Save
End Sub
Sub NameColumns(SheetNum,Colum
objWorkBook.Sheets(arrWBNa
i=2
For Each ColumnHead in ColumnsArray
'All sheets will have ComputerName as the first column name
'so hard code it here.
objExcel.Cells(1,1).Value = "Computer Name"
objExcel.Cells(1,i).Value = ColumnHead
i = i + 1
Next
End Sub
'Rename each sheet
Sub RenameSheets(CurrentName,N
objWorkBook.Sheets(Current
objWorkbook.ActiveSheet.Na
End Sub
'This is for formatting each worksheet.
Sub FormatWorkSheet(HeadCellRa
objExcel.Range(HeadCellRan
objExcel.Cells.EntireColum
objExcel.Cells.EntireRow.A
End Sub
Oops sorry, posted that accidentally.
Anyway, try replacing the ReadVal code with this.
It just checks for the end of the file before doing any further processing.
I leave it up to you to decide what to do if you're at the end of the file.
Anyway, try replacing the ReadVal code with this.
It just checks for the end of the file before doing any further processing.
I leave it up to you to decide what to do if you're at the end of the file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ReadVal = Mid(objTextFile.ReadLine,L
Else
' At the end of file
ReadVal = " "
End If