1 workbook many rows, instead of many workbooks and 1 row.

B

BGMike

I have some code that creates a new excel workbook, then adds a line
of computer hardware information.
It currently creates many workbooks with 2 rows of info. I would like
to have 1 workbook with many rows of info.
I am very new to vbs, and would like to learn if this is possible and
if yes, how.
Here is a sample of my code:

'**********[ Main
Program ]****************************************************

Call BuildXLS()
objXL.DisplayAlerts = False 'disable the overwrite? prompt, JUST DO IT

Call AddLineToXLS(strIPAddress, strHostName, _
strMake, strModel, strSerial, strOS, _
strSP, strBIOSrev, strProc, strSpeed, strRAM, strHDSize, _
struser, strMask, strGate, strMAC, strDateInstalled)

'*** Subroutine to Build XLS ***
Sub BuildXLS()

intRow = 1
Set objXL = Wscript.CreateObject("Excel.Application")
objXL.Visible = False
objXL.WorkBooks.Add
objXL.Sheets("Sheet1").Select()
objXL.Sheets("Sheet1").Name = "PC Details"

'***Set Row Height***
objXL.Rows(1).RowHeight = 25

'***Set Column widths***
objXL.Columns(1).ColumnWidth = 15
objXL.Columns(2).ColumnWidth = 12
objXL.Columns(3).ColumnWidth = 30
objXL.Columns(4).ColumnWidth = 18
objXL.Columns(5).ColumnWidth = 16
objXL.Columns(6).ColumnWidth = 30
objXL.Columns(7).ColumnWidth = 16
objXL.Columns(8).ColumnWidth = 16
objXL.Columns(9).ColumnWidth = 32
objXL.Columns(10).ColumnWidth = 15
objXL.Columns(11).ColumnWidth = 10
objXL.Columns(12).ColumnWidth = 15
objXL.Columns(13).ColumnWidth = 24
objXL.Columns(14).ColumnWidth = 15
objXL.Columns(15).ColumnWidth = 19
objXL.Columns(16).ColumnWidth = 18
objXL.Columns(17).ColumnWidth = 27

'*** Set Cell Format for Column Titles ***
objXL.Range("A1:Q1").Select
objXL.Selection.Font.Bold = True
objXL.Selection.Font.Size = 8
objXL.Selection.Interior.ColorIndex = 11
objXL.Selection.Interior.Pattern = 1 'xlSolid
objXL.Selection.Font.ColorIndex = 2
objXL.Selection.WrapText = True
objXL.Columns("A:Q").Select
objXL.Selection.HorizontalAlignment = 3 'xlCenter


'*** Set Column Titles ***
dim arrNicTitle(4)
arrNicTitle(0) = "NIC #1 Model"
arrNicTitle(1) = "NIC #2 Model"
arrNicTitle(2) = "NIC #3 Model"
arrNicTitle(3) = "NIC #4 Model"
arrNicTitle(4) = "NIC #5 Model"

'*** Creates Title Bar on Excel Sheet ***
Call AddLineToXLS("IP Address" , "Hostname" , _
"Make" , "Model" , "Serial Number" , _
"Operating System" , "Service Pack" , "BIOS Revision" , _
"Processor Type" , "Processor Speed", "Ram", _
"Hard Drive Size", "Logged in user" , "Subnet Mask" , _
"Default Gateway", "MAC Address", "Date Installed")

END SUB

Sub AddLineToXLS(strIPAddress, strHostName, _
strMake, strModel, strSerial, strOS, _
strSP, strBIOSrev, strProc, strSpeed, strRAM, strHDSize, _
struser, strMask, strGate, strMAC, strDateInstalled)
objXL.Cells(intRow, 1).Value = strIPAddress
objXL.Cells(intRow, 2).Value = strHostName
objXL.Cells(intRow, 3).Value = strMake
objXL.Cells(intRow, 4).Value = strModel
objXL.Cells(intRow, 5).Value = strSerial
objXL.Cells(intRow, 6).Value = strOS
objXL.Cells(intRow, 7).Value = strSP
objXL.Cells(intRow, 8).Value = strBIOSrev
objXL.Cells(intRow, 9).Value = strProc
objXL.Cells(intRow, 10).Value = strSpeed
objXL.Cells(intRow, 11).Value = strRAM
objXL.Cells(intRow, 12).Value = strHDSize
objXL.Cells(intRow, 13).Value = struser
objXL.Cells(intRow, 14).Value = strMask
objXL.Cells(intRow, 15).Value = strGate
objXL.Cells(intRow, 16).Value = strMAC
objXL.Cells(intRow, 17).Value = strDateInstalled



intRow = intRow + 1
objXL.Cells(1, 1).Select

End Sub

'*** Subroutine Add Lines to XLS ***
objXL.Columns("A:Q").Select
objXL.Selection.HorizontalAlignment = 3 'xlCenter
objXL.Selection.Font.Size = 8

'***Saves the Excel Files As***
objXL.ActiveWorkbook.SaveAs ("\\128.1.1.111\c\PCErrors\PCDetails
\PCDetails - " & sComputerName & ".xls")

'***Closes Excel***
objxl.Application.Quit
 
Top