[Arkur55]

This is what I need help with. Below is the section of code I am using:

objExcel.Cells(b, 14).Value = ((objDisk.DeviceID &" "& Round(objDisk.Size/1073741824) & "GB"))

This produces the drive ID and Size in the cell, However if the system has more than one drive the inormation is overwritten by the last drive found. For example:
Server1 is a 2003 server with Primary = C and Sercondary = D.
Server2 is a citrix server with Primary = M and Sercondary = N.

Server Name Drive C
Server1 D: 30GB
Server2 N: 19GB


What I need the code to do is insert a column into the spread sheet for each logical drive in the server. For example:

Server Name Drive 1 Drive 2 Drive 3
Server1 C: 24GB D: 324GB E: 324GB
Server2 M: 24GB N: 324GB E: 324GB


Can anyone help?

[OBP]

You need some VBA code that has a "for/next" loop that uses the count of system drives.
Have you established how to find the number of drives in Excel?

[Arkur55]

Thanks OBP, What I can not figure out how to set the LogicalDisk to a variable and have the script loop through each existing drive and insert the information into an excel spread sheet. The way I am currently performing the task is with the script below. This would be great if all the servers had jus a C and D drive, but thats not the case.

What I am looking for is to have the script look for the drives like C: inset a column Labled Drive C at cell A1 and the Size in Cell A2 when it finds let say drive D: it inserts a colum
at cell B1 and the Size at B2.

Can you still help??


set oSvc = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
wqlQuery = "select DeviceID,Size from Win32_LogicalDisk where Name = 'C:'"

For Each oData In oSvc.ExecQuery(wqlQuery)
for each oProperty in oData.Properties_
if oProperty.Name = "DeviceID" Then
DiskName = oProperty.Value
elseif oProperty.Name = "Size" Then
DiskSize = oProperty.Value
end If
Next
WScript.Echo (DiskName & vbTab & DiskSize)
objExcel.Cells(b, 14).Value = ((DiskName &" "& Round(DiskSize/1073741824) & " GB"))
Next
wqlQuery = "select DeviceID,Size from Win32_LogicalDisk where Name = 'D:'"

For Each oData In oSvc.ExecQuery(wqlQuery)
for each oProperty in oData.Properties_
if oProperty.Name = "DeviceID" Then
DiskName = oProperty.Value
Elseif oProperty.Name = "Size" Then
DiskSize = oProperty.Value
end If
Next
WScript.Echo (DiskName & vbTab & DiskSize)
objExcel.Cells(b, 15).Value = ((DiskName &" "& Round(DiskSize/1073741824) & " GB"))
next

[OBP]

I do not know if I can help, but I know a man who probably can, Zack the Firefytr, I will send him an email. I can do it with VBA from within Excel, but not with VBS.

[Arkur55]

OBP,

I had some additional help and came up with the following code.
So if you are ever asked the same question below is the answer.

Thanks for your assistance and replys.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" """""""""""""""""

Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")
ColOffset = 0
For Each objDisk in colDisks
objExcel.Cells(b, 14 + ColOffset).Value = ((objDisk.DeviceID &" "& Round(objDisk.Size/1073741824) & " GB"))

ColOffset = ColOffset + 1

Next

[OBP]

Arkur, thanks for the info. Can you mark your thread as "Solved" using the thread tools at the top of the page please?

[Arkur55]

Yes, Thanks!