Solved

VB Script output to Excel

Posted on 2010-11-10
12
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

689 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