Solved

VB Script output to Excel

Posted on 2010-11-10
12
684 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

16 Experts available now in Live!

Get 1:1 Help Now