Solved

VB Script output to Excel

Posted on 2010-11-10
12
685 Views
Last Modified: 2012-05-10
Hello,
I need help with one script. I have a script that checks for available space on computer drives. I would like to output information in to Excel or csv file. Also when I run this script I would like new information to append to the same file, so I am able to see how the disk space is changing overtime. The script I have is below. Any help?
' List Available Disk Space


Const HARD_DISK = 3

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

For Each objDisk in colDisks
    Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
    Wscript.Echo "Free Disk Space: "& vbTab & objDisk.FreeSpace
Next

0
Comment
Question by:docutechPA
  • 6
  • 5
12 Comments
 
LVL 3

Assisted Solution

by:arweeks
arweeks earned 200 total points
ID: 34104172
I'd do it like this.   I'd have a function, as below, that logs to file.  Obviously you need to define logPath as a csv file. Then I'd change the code from wscript.echo to the function that logs it.

 Note, I haven't tested this, it doesn't run on my Win7 box, I assume it'll work fine on XP etc.



logToFile("DeviceID," &  objDisk.DeviceID      
   ",Free Disk Space," &  objDisk.FreeSpace)


sub logToFile(logString)
Set f = fso.GetFile(logPath)
Set txtStream = f.OpenAsTextStream(8)
   txtStream.WriteLine(logString)
txtStream.Close
end sub



The full script would be


strComputer = "."
logPath = "c:\users\arweeks\disk.csv"
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

For Each objDisk in colDisks
    Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
    Wscript.Echo "Free Disk Space: "& vbTab & objDisk.FreeSpace
      
logToFile("DeviceID," &  objDisk.DeviceID & ",Free Disk Space," &  objDisk.FreeSpace)
      
Next


sub logToFile(logString)
Set f = fso.GetFile(logPath)
Set txtStream = f.OpenAsTextStream(8)
   txtStream.WriteLine(logString)
txtStream.Close
end sub
0
 

Author Comment

by:docutechPA
ID: 34104706
I tried to run this script on Win 7, XP and Server 2003. It errors out on line 9
 
For Each objDisk in colDisks
    Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
    Wscript.Echo "Free Disk Space: "& vbTab & objDisk.FreeSpace



error.bmp
0
 
LVL 9

Expert Comment

by:Orcbighter
ID: 34114129
It is hard to tell from your post which line of code is line 9. Which one is it?

While you answer that, How do you know that the "Set colDisks.." and "Set objWMIService.." were actually assigned a value and are not null? I see no code testing for this. If either of these values is null then the program would break at the "For Each..." statement

You stated that you have not tested this on Windows 7 but that you assume it runs on XP. On what basis do you make that assumption?
0
 

Author Comment

by:docutechPA
ID: 34114503
This is line 9, 10, and 11
For Each objDisk in colDisks
    Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
    Wscript.Echo "Free Disk Space: "& vbTab & objDisk.FreeSpace

And the script is not running on 7,XP or server2003
0
 
LVL 9

Expert Comment

by:Orcbighter
ID: 34116599
I posted the answer but it disappeared, so I will try again.
The error code 80041017 indicates there is a syntax error in the code.
Look at your SELECT statement; you either have one-too-few or one-too-many double-quotes
0
 
LVL 9

Expert Comment

by:Orcbighter
ID: 34116678
Try hard-coding the value as a check, eg
  ("Select * from Win32_LogicalDisk Where DriveType = 3")
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:docutechPA
ID: 34120418
That worked.  Now I am getting another error. Line 20 (wich is: Set f=fso.GetFile (logPath)
The error states:
Line 20
Column 1
Error Object required: 'fso'
Code 800A01A8
0
 
LVL 9

Expert Comment

by:Orcbighter
ID: 34125349
where in the code do you either create or get the fso object?
0
 

Author Comment

by:docutechPA
ID: 34145209
Here is compete cod I am using

1.strComputer = "."
2.logPath = "d:\scripts\disk.csv"
3.Set objWMIService = GetObject("winmgmts:" _
4.    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
5.
6.Set colDisks = objWMIService.ExecQuery _
7.   ("Select * from Win32_LogicalDisk Where DriveType = 3")
8.
9.
10.For Each objDisk in colDisks
11.   Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
12.    Wscript.Echo "Free Disk Space: "& vbTab & objDisk.FreeSpace
13.      
14.logToFile("DeviceID," &  objDisk.DeviceID & ",Free Disk Space," &  objDisk.FreeSpace)
15.      
16.Next
17
18
19.sub logToFile(logString)
20.Set f = fso.GetFile(logPath)
21.Set txtStream = f.OpenAsTextStream(8)
22.   txtStream.WriteLine(logString)
23.txtStream.Close
24.end sub

It errors out on line 20 "object required fso" Code: 800A01A8
If that helps I have a file disk.csv on d:\scripts
0
 
LVL 9

Accepted Solution

by:
Orcbighter earned 300 total points
ID: 34149239
If fso stands for "FileSystemObject", then..
Before you can use the GetFile(...) method of the fso, you have to create the fso object. I cannot see in your code where you do that.
eg
Set fso = CreateObject("Scripting.FileSystemObject")
Set myFile = fso.GetFile(logPath)

If fso is null, then, of course, it will generate an error when you try to use a method from a null object.
0
 

Author Comment

by:docutechPA
ID: 34187783
Thank you very much it works now
0
 

Author Closing Comment

by:docutechPA
ID: 34187795
Thank you arweek and Orcbighter. Combined I got the answer I was looking for.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now