Solved

VB Script output to Excel

Posted on 2010-11-10
12
686 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

815 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

8 Experts available now in Live!

Get 1:1 Help Now