Solved

VB Script output to Excel

Posted on 2010-11-10
12
688 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
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!

 

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

Independent Software Vendors: 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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

697 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