Directory Size and bolding Excel cells

Ruby
1

Does anyone know any commands that will tell me the size of a
directory? My program needs to scan every user in a OU and inform me
how large their roaming profile is. I currently use:

result_of_locdsquery = dsquery user "OU=#{loc},OU=AB,DC=abcd,DC=root,DC=xyz,DC=com" -limit 999 -name "*" | dsget user -samid -profile.split("\n")

result_of_locdsquery.pop #remove the last item of the array
result_of_locdsquery.shift #remove the first item of the array

counter=2
result_of_locdsquery.each do |line|
user, path= line.split(/\s+.\s+/)
loc_users=[user]
loc_path=[path]
result_of_locdiruse = diruse /m #{path}.chomp

However, this takes forever to do…any shorter way??

In addtion, how do you BOLD and hightlight and excel CELL??

2

[email protected] wrote:

In addtion, how do you BOLD and hightlight and excel CELL??

Where ws is your Worksheet object and row and col are integers…

ws.Cells(row, col).Font.Bold = 1
ws.Cells(row, col).Interior.ColorIndex = 6

One of the best ways to determine the necessary Excel objects and
methods is to record a macro in Excel and then review the macro’s VBA
code.

Mully

3

I tried
excel.Worksheets(x).Cells(“A1:D1”).Font.Bold=1

the (x) is a variable in a loop since I have numerous worksheets.

I receive the following error:
/test4.rb:17:in `method_missing’: Cells (WIN32OLERuntimeError)
OLE error code:80020005 in

HRESULT error code:0x80020009
Exception occurred. from C:/test4.rb:17
from C:/test4.rb:15

4

[email protected] wrote:

<No Description>

HRESULT error code:0x80020009
Exception occurred. from C:/test4.rb:17
from C:/test4.rb:15

The ‘Cells’ method does not accept a range of cells as a parameter. If
you’re using a range, replace ‘Cells’ with ‘Range’…

excel.Worksheets(x).Range(“A1:D1”).Font.Bold=1

Mully

5

— mully [email protected] wrote:

The ‘Cells’ method does not accept a range of cells
as a parameter. If
you’re using a range, replace ‘Cells’ with
‘Range’…

excel.Worksheets(x).Range(“A1:D1”).Font.Bold=1

Mully

Is it possible to write a range of cells with numbers,

for example A1 is cell (1,1) and D1 is cell(4,1)?

Thanks,

Li

6

Ok, I created a Macro in Excel to see what properties I need to you
with Excel.
When i use
excel.Worksheets(x).Columns(“A:A”).EntireColumn.AutoFit
excel.Worksheets(x).Columns(“B:B”).EntireColumn.AutoFit

It does not autofit the cells.

7

Try excel.worksheets(x).range(‘a:a’).columns.autofit

Nate

8

Ok, I got the cells to autofit using the below code. "#{loc} is a
variable for my worksheets.

            excel.Worksheets("#{loc}").Columns("A:A").AutoFit
excel.Worksheets("#{loc}").Columns("B:B").AutoFit
excel.Worksheets("#{loc}").Columns("C:C").AutoFit
excel.Worksheets("#{loc}").Columns("D:D").AutoFit
excel.Worksheets("#{loc}").Columns("E:E").AutoFit
9

Nope:

excel.Worksheets(x).Cells(1, 1).Font.Bold = 1
excel.Worksheets(x).Cells(4, 1).Font.Bold = 1

A…Z 1…2 are references Excel uses, not Win32OLE.

Tim

10

[email protected] wrote:

Ok, I got the cells to autofit using the below code. "#{loc} is a
variable for my worksheets.

            excel.Worksheets("#{loc}").Columns("A:A").AutoFit

excel.Worksheets("#{loc}").Columns(“B:B”).AutoFit
excel.Worksheets("#{loc}").Columns(“C:C”).AutoFit
excel.Worksheets("#{loc}").Columns(“D:D”).AutoFit
excel.Worksheets("#{loc}").Columns(“E:E”).AutoFit

FYI, the Columns method can accept a range of columns, so you could
replace the above five lines of code with a single line…

excel.Worksheets("#{loc}").Columns(“A:E”).AutoFit

Or, to address all columns in a worksheet…

excel.Worksheets("#{loc}").Columns.AutoFit

Mully

11

Have any clue how to do Freeze Panes?? The VB code is
excel.Worksheets(x).Rows(“2:2”).Select
excel.Worksheets(x).FreezePanes =1