Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.scripting.vbscript,microsoft.scripting.wsh
|
|||
|
|||
Help, Input int different excel sheets
Please help!!
I know it is simple but can't think how it works. I tried to search but no luck. We have about 10 servers to admin. My boss wants to put 10 servers info into excel and each server info on one worksheet. I only know how to import into excel on ONE worksheet. I have script to query server info but I don't know how to select different worksheet in VBscript. Thanks |
#2
Posted to microsoft.public.excel.programming,microsoft.public.scripting.vbscript,microsoft.public.scripting.wsh
|
|||
|
|||
Help, Input int different excel sheets
"Sam Chan" wrote in message
... Please help!! I know it is simple but can't think how it works. I tried to search but no luck. We have about 10 servers to admin. My boss wants to put 10 servers info into excel and each server info on one worksheet. I only know how to import into excel on ONE worksheet. I have script to query server info but I don't know how to select different worksheet in VBscript. Thanks Will this help? Here's a variation of a script that I gather came from "Script Center". Enter the computer names in the InputBox with each separated by a space. Watch for word-wrap. Option Explicit 'On Error Resume Next Dim objNetwork Set objNetwork = CreateObject("Wscript.Network") Dim strLocalComputer strLocalComputer = objNetwork.ComputerName Dim strComputers strComputers = InputBox("What computer(s) would you like info on?", "Computer Inventory", strLocalComputer) If strComputers = "" Then Wscript.Quit Dim objExcel Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Application.DisplayAlerts = False objExcel.Workbooks.Add Dim arrComputers arrComputers = Split(strComputers," ") Dim strComputer For Each strComputer in arrComputers 'WScript.Echo strComputer '* '* Declare '* Dim objWMIService Set objWMIService = GetObject ("winmgmts:\\" & strComputer & "\root\cimv2") Dim colItems Dim objItem Dim strDomainRole Dim objRange '* '* Workbook '* objExcel.Worksheets.Add objExcel.Workbooks(1).Worksheets(1).Name = strComputer objExcel.Columns(1).ColumnWidth = 20 objExcel.Columns(2).ColumnWidth = 40 '* '* Cells '* objExcel.Cells(1,1).Value = "OS Version" objExcel.Cells(2,1).Value = "Service Pack" objExcel.Cells(3,1).Value = "# of Processors" objExcel.Cells(4,1).Value = "Processor Type" objExcel.Cells(5,1).Value = "Max. Clock Speed" objExcel.Cells(6,1).Value = "Total Phy Memory" objExcel.Cells(7,1).Value = "Free Phy Memory" objExcel.Cells(8,1).Value = "Total Virtual Memory" objExcel.Cells(9,1).Value = "Free Virtual Memory" objExcel.Cells(10,1).Value = "Total Visible Memory" objExcel.Cells(11,1).Value = "Domain" objExcel.Cells(12,1).Value = "Domain Role" objExcel.Cells(13,1).Value = "Manufacturer" objExcel.Cells(14,1).Value = "Model" objExcel.Cells(15,1).Value = "Serial Number" objExcel.Cells(16,1).Value = "User" '* '* Caption '* Service Pack Major Version '* Service Pack Minor Version '* Free Physical Memory '* Total Virtual Memory Size '* Free Virtual emory '* Total Visible Memory Size '* SerialNumber '* Set colItems = objWMIService.ExecQuery("Select * From Win32_OperatingSystem") For Each objItem in colItems objExcel.Cells(1,2).Value = objItem.Caption objExcel.Cells(2,2).Value = objItem.ServicePackMajorVersion & "." & objItem.ServicePackMinorVersion objExcel.Cells(7,2).Value = FormatNumber(objItem.FreePhysicalMemory,0) objExcel.Cells(8,2).Value = FormatNumber(objItem.TotalVirtualMemorySize,0) objExcel.Cells(9,2).Value = FormatNumber(objItem.FreeVirtualMemory,0) objExcel.Cells(10,2).Value = FormatNumber(objItem.TotalVisibleMemorySize,0) objExcel.Cells(15,2).Value = objItem.SerialNumber Next Set colItems = Nothing '* '* Number Of Processors '* Total Physical Memory '* Domain Role... '* Manufacturer '* Model '* Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") For Each objItem In colItems objExcel.Cells(3,2).Value = objItem.NumberOfProcessors objExcel.Cells(6,2).Value = FormatNumber(objItem.TotalPhysicalMemory,0) objExcel.Cells(11,2).Value = objItem.Domain Select Case objItem.DomainRole Case 0 strDomainRole = "Standalone Workstation" Case 1 strDomainRole = "Member Workstation" Case 2 strDomainRole = "Standalone Server" Case 3 strDomainRole = "Member Server" Case 4 strDomainRole = "Backup Domain Controller" Case 5 strDomainRole = "Primary Domain Controller" End Select objExcel.Cells(12,2).Value = strDomainRole objExcel.Cells(13,2).Value = objItem.Manufacturer objExcel.Cells(14,2).Value = objItem.Model objExcel.Cells(16,2).Value = objItem.UserName Next Set colItems = Nothing '* '* Name '* Max Clock Speed '* Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor") For Each objItem in colItems objExcel.Cells(4,2).Value = objItem.Name objExcel.Cells(5,2).Value = objItem.MaxClockSpeed Next Set colItems = Nothing '* '* Destroy '* Set objWMIService = Nothing Next objExcel.Workbooks(1).Worksheets("Sheet3").Delete objExcel.Workbooks(1).Worksheets("Sheet2").Delete objExcel.Workbooks(1).Worksheets("Sheet1").Delete Set objExcel = Nothing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pull data from multiple sheets based on input | Excel Worksheet Functions | |||
My excel spread sheets are dividing all numbers input by 100. | Excel Worksheet Functions | |||
Need to input data in excel that will print to pre printed sheets | Setting up and Configuration of Excel | |||
Rollup of Data in Multiple Sheets (based on user input) | Excel Programming | |||
adding sheets based on input | Excel Programming |