[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Directory Size and bolding Excel cells

anon1m0us

12/12/2006 2:57:00 PM

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??
8 Answers

David Mullet

12/12/2006 4:12:00 PM

0

anon1m0us@yahoo.com 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

anon1m0us

12/12/2006 5:35:00 PM

0

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

mully wrote:
> anon1m0us@yahoo.com 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

David Mullet

12/12/2006 6:06:00 PM

0


anon1m0us@yahoo.com wrote:
> 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

Timothy Brown

12/12/2006 6:11:00 PM

0

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

On Dec 12, 2006, at 12:35 EST, anon1m0us@yahoo.com wrote:

> 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
>
> mully wrote:
>> anon1m0us@yahoo.com 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
>
>

anon1m0us

12/12/2006 6:27:00 PM

0

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.

Timothy Brown wrote:
> 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
>
> On Dec 12, 2006, at 12:35 EST, anon1m0us@yahoo.com wrote:
>
> > 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
> >
> > mully wrote:
> >> anon1m0us@yahoo.com 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
> >
> >

anon1m0us

12/12/2006 8:48:00 PM

0

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
anon1m0us@yahoo.com wrote:
> 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.
>
> Timothy Brown wrote:
> > 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
> >
> > On Dec 12, 2006, at 12:35 EST, anon1m0us@yahoo.com wrote:
> >
> > > 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
> > >
> > > mully wrote:
> > >> anon1m0us@yahoo.com 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
> > >
> > >

David Mullet

12/13/2006 12:41:00 PM

0


anon1m0us@yahoo.com 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

anon1m0us

12/13/2006 1:33:00 PM

0

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

mully wrote:
> anon1m0us@yahoo.com 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