| |
nik1420
Posts: 49
Score: 0
Joined: 12/19/2007
Status: offline
|
I'm trying to create a script that will get the Home drive location via AD, then go to that directory and get the size of the directory. All of which gets sent to an Excel file.
I have been successfull with this script in getting the information I need and echo it. But I have over 100 users to do. I inserted some lines to write the output to an excel file, but I get stuck in this endless loop.
Excel launches
Row Headers are added
First line writes
Problem: can't get the output to write to the next line. I found my problem with the loop. I had another intRow = 2 after ocjExcel.Workbooks.Add. I still need to know how to write to the same spreadsheet. I understand that each time it is doing the loop it creates a new workbook, but how do I keep it within the same spreadsheet?
Here's the code:
Option Explicit
DIM strExcelPath, objExcel, objSheet, intRow, strPath
DIM objFSO, objFolder
DIM bytes
strExcelPath = \\server\My Documents\cyd.xls
On Error Resume Next
set objExcel = CreateObject("Excel.Application")
If err.Number <> 0 Then
On error goto 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End if
On Error Goto 0
'Open Spreadsheet
On error resume next
objExcel.workbooks.open strExcelPath
If Err.Number <> 0 Then
On Error goto 0
Wscript.Echo "Spreadsheet cannot be opened: " & strExcelPath
Wscript.quit
End If
On error goto 0
'Bind to worksheet
set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'the first row of the spreadsheet is skipped (column headings). Each row
'after is processed until the first blank entry in the first column.
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strPath = objsheet.cells(intRow, 1).Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objFolder = objFSO.GetFolder (strPath)
bytes = objFolder.size
' This is where the loop happens for me.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells (1,1).Value = "Home Dir Path"
objExcel.Cells (1,2).Value = "Dir Size"
objExcel.Cells (intRow,1).Value = strPath
objExcel.Cells (intRow, 2).Value = bytes
intRow = introw +1
Loop
Wscript.Echo "Done"
|
|
Printable Version
Add text to same spreadsheet - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread