Help needed with VB Script and producing an Excel Worksheet

Question

I am trying to create a VB Script which will produce an Excel based report on the disk space for the servers within my environment. I have 3 hurdles left which I can't get over.

1. How can I left align all of the columns/cells within the worksheet? Line 25 is where I have tried to do this however it throws the error, "Unable to set the HorizontalAlignment property of the Range class".

2. Some servers have more than 1 drive (eg C, D, E). When the script produces the report, it will only show the last drive (eg E). How can I make it show every drive for each server?

3. When I run the script, I would like it to append the report with the current day's disk usage. At the moment, it will replace the existing cells with the current day's disk usage.

The code for my script is as follows:

On Error Resume Next

Const ForReading = 1
Const HARD_DISK = 3

x = 1

dtmDate = Date

strDay = Day(Date)
strMonth = Month(Date)
strYear = Right(Year(Date), 2)

strFileName = "C:\Users\cvandal\Desktop\Scripts\Server_Disk_Space_Report.xlsx"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists("C:\Users\cvandal\Desktop\Scripts\Server_Disk_Space_Report.xlsx") Then
    Set serverList = objFSO.OpenTextFile("servers.txt", ForReading)

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open "C:\Users\cvandal\Desktop\Scripts\Server_Disk_Space_Report.xlsx"
    objExcel.Visible = True
    objExcel.Columns("A:ZZ").ColumnWidth = 25
    objExcel.Columns("A:ZZ").HorizontalAlignment = xlHAlignLeft
    objExcel.Cells(2, 1).Value = "Server Disk Space Report"
    objExcel.Cells(4, 1).Value = dtmDate
    objExcel.Cells(5, 1).Value = "Drives:"
    objExcel.Cells(6, 1).Value = "Total Capacity (in GB):"
    objExcel.Cells(7, 1).Value = "Used Capacity (in GB):"
    objExcel.Cells(8, 1).Value = "Free Space (in GB):"
    objExcel.Cells(9, 1).Value = "Free Space (in %):"

    Do Until serverList.AtEndOfStream
        x = x + 1

        strComputer = serverList.ReadLine

        Set objWMIService = GetObject("winmgmts:{impersonationlevel=impersonate}!\\" & strComputer & "\root\cimv2")
        Set colDisks = objWMIService.ExecQuery("SELECT * FROM Win32_LogicalDisk WHERE DriveType = " & HARD_DISK & "")

        If Err.Number <> 0 Then
            'WScript.Echo "Error: " & Err.Number
            'WScript.Echo "Error (Hex): " & Hex(Err.Number)
            'WScript.Echo "Source: " &  Err.Source
            'WScript.Echo "Description: " &  Err.Description

            objExcel.Cells(4, x).Value = strComputer & " - " & Err.Description
            objExcel.Cells(4, x).Columns.AutoFit

            Err.Clear
        Else
            For Each objDisk in colDisks
                drives = "Error"
                totalCapacity = 0
                freeSpace1 = 0
                usedCapacity = 0
                freeSpace2 = 0

                drives = objDisk.DeviceID
                totalCapacity = Round((objDisk.Size / 1073741824), 2)
                freeSpace1 = Round((objDisk.FreeSpace / 1073741824), 2)
                usedCapacity = Round((totalCapacity - freeSpace1), 2)
                freeSpace2 = Round((freeSpace1 / totalCapacity)*100, 0)

                If freeSpace2 > 20 Then
                    objExcel.Cells(4, x).Value = strComputer
                    objExcel.Cells(5, x).Value = drives
                    objExcel.Cells(6, x).Value = totalCapacity & " GB"
                    objExcel.Cells(7, x).Value = usedCapacity & " GB"
                    objExcel.Cells(8, x).Value = freeSpace1 & " GB"
                    objExcel.Cells(9, x).Value = freeSpace2 & "%"
                    objExcel.Cells(9, x).Interior.Color = RGB(198,239,206)
                ElseIf freeSpace2 < 10 Then
                    objExcel.Cells(4, x).Value = strComputer
                    objExcel.Cells(5, x).Value = drives
                    objExcel.Cells(6, x).Value = totalCapacity & " GB"
                    objExcel.Cells(7, x).Value = usedCapacity & " GB"
                    objExcel.Cells(8, x).Value = freeSpace1 & " GB"
                    objExcel.Cells(9, x).Value = freeSpace2 & "%"
                    objExcel.Cells(9, x).Interior.Color = RGB(255,199,206)
                Else
                    objExcel.Cells(4, x).Value = strComputer
                    objExcel.Cells(5, x).Value = drives
                    objExcel.Cells(6, x).Value = totalCapacity & " GB"
                    objExcel.Cells(7, x).Value = usedCapacity & " GB"
                    objExcel.Cells(8, x).Value = freeSpace1 & " GB"
                    objExcel.Cells(9, x).Value = freeSpace2 & "%"
                    objExcel.Cells(9, x).Interior.Color = RGB(255,235,156)
                End If
            Next
        End If
    Loop
Else
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False

    Set objWorkbook = objExcel.Workbooks.Add()
    objWorkbook.SaveAs(strFileName)

    objExcel.Quit

    WScript.Echo "Server_Disk_Space_Report.xlsx has been created. Please re-run the script."
End If

Answer 1

1. It doesn't look like you've defined a constant for the value of xlHAlignLeft.

2. You should increment the counter x inside of your disk loop:

   For Each objDisk in colDisks
       x = x + 1   ' <-- add this line
   

   You'll probably have to play with where exactly in the code you increment the counter, depending on how you want the output to look. I think the place I put it in my example would result in a blank line between each machine.

3. The trick here is to initialize x to the first available row, instead of always defaulting to 1. The following code searches the first column ('A') for the last non-empty row. (reference)

   Const xlUp = -4162
   x = objExcel.Cells(Rows.Count, 1).End(xlUp).Row
   

Answer 2

Did you know that you can put the markup for HTML tables into a .xls file and open it with Excel? It even works for Excel 2000! Try it, and you'll be so much happier that you don't have to create the "Excel.Application" COM object!