Directory Size and bolding Excel cells

A

anon1m0us

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??
 
M

mully

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
 
A

anon1m0us

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 <Unknown>
<No Description>
HRESULT error code:0x80020009
Exception occurred. from C:/test4.rb:17
from C:/test4.rb:15
 
M

mully

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 <Unknown>
<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
 
T

Timothy Brown

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
 
A

anon1m0us

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.
 
A

anon1m0us

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
 
M

mully

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
 
A

anon1m0us

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top