Solved: VBS and Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Arkur55

Thread Starter
Joined
Dec 26, 2005
Messages
5
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

Joined
Mar 8, 2005
Messages
19,896
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

Thread Starter
Joined
Dec 26, 2005
Messages
5
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

Joined
Mar 8, 2005
Messages
19,896
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

Thread Starter
Joined
Dec 26, 2005
Messages
5
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

Joined
Mar 8, 2005
Messages
19,896
Arkur, thanks for the info. Can you mark your thread as "Solved" using the thread tools at the top of the page please?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top