RE: How to select a specified range in Excel?

Kiron

unread,

Oct 10, 2008, 9:40:01 PM10/10/08

to

# you can get your selection like this:
$xlToRight = -4161
$c.range('a2', $c.range('a2').end($xlToRight)).select()

# to get the A1 notation of a range, column, row or cell
# use the AddressLocal method
$c.columns.item(52).addressLocal(0,0)

--
Kiron

"MG" wrote:

> Hi,
> I need select current line data range but not entire row in Excel.
> Name Company Phone Fax Address Mailbox
> Data1 Data2 Data3 Data4 Data5 Data6
>
> The columns number is not sure
> Use VBA macro, the code like this:
>
> Range(Selection, Selection.End(xlToRight)).Select
>
> In PowerShell, I can select a certain range like this:
> $a = New-Object -comobject Excel.Application
> $a.Workbooks.Open("d:\out\m2.xlsx")
> $c = $a.Worksheets.Item(1)
> $c.range("a2:f2").select()
>
> But if I don't know how many columns, can I select the range like VBA code?
> (From current cell to last cell of current row/column)
>
> or
>
> I can get the columns number like this:
> $c.usedrange.columns.count
>
> The value is 52, but how can I interpret it in "A1" format? (Actually, it's
> "AZ" column)
>
> Thanks!
> MG
>

MG

unread,

Oct 11, 2008, 3:00:04 AM10/11/08

to

Yes, I got it!
Another question, where can I find xlDown, xlToLeft, xlToRight, xlUp value?

MG

"Kiron" <Ki...@discussions.microsoft.com> wrote in message
news:20622E52-62FA-4B4F...@microsoft.com...

Flowering Weeds

unread,

Oct 11, 2008, 6:19:18 PM10/11/08

to

> Another question, where can I find
> xlDown, xlToLeft, xlToRight, xlUp
> value?

Microsoft Office Enumerated Constants
http://msdn.microsoft.com/en-us/library/aa170976.aspx

Microsoft Excel Constants [Excel 2003 VBA Language Reference]
http://msdn.microsoft.com/en-us/library/aa221100(office.11).aspx

New Members and Constants [Excel 2007 Developer Reference]
http://msdn.microsoft.com/en-us/library/bb225807.aspx

Excel 2007 Enumerations
http://msdn.microsoft.com/en-us/library/bb259478.aspx

And for those without Office but
using the Office Web Components

Office Web Components Constants
http://msdn.microsoft.com/en-us/library/aa204179(office.11).aspx

Yep the Office Web Components
run within the automation tool
powershell.exe too!

OldDog

unread,

Oct 12, 2008, 11:42:50 AM10/12/08

to

On Oct 11, 5:19 pm, "Flowering Weeds"

<floweringnoweed...@hotnomail.nom> wrote:
> > Another question, where can I find
> > xlDown, xlToLeft, xlToRight, xlUp
> > value?
>

> Microsoft Office Enumerated Constantshttp://msdn.microsoft.com/en-us/library/aa170976.aspx

>
> Microsoft Excel Constants [Excel 2003 VBA Language Reference]http://msdn.microsoft.com/en-us/library/aa221100(office.11).aspx
>
> New Members and Constants [Excel 2007 Developer Reference]http://msdn.microsoft.com/en-us/library/bb225807.aspx
>

> Excel 2007 Enumerationshttp://msdn.microsoft.com/en-us/library/bb259478.aspx

>
> And for those without Office but
> using the Office Web Components
>

> Office Web Components Constantshttp://msdn.microsoft.com/en-us/library/aa204179(office.11).aspx

>
> Yep the Office Web Components
> run within the automation tool
> powershell.exe too!

To add to the fun, I have an Excel spreadsheet with my favorite
constants here;

http://cid-c2db05eefa6c21a1.skydrive.live.com/browse.aspx/Public

OldDog

Flowering Weeds

unread,

Oct 12, 2008, 5:07:29 PM10/12/08

to

> To add to the fun, I have an Excel
> spreadsheet with my favorite constants

This way is way too easy to get
"human" errors within the list. :)

Perhaps one can with PowerShell
and the Office .NET way or by having
PowerShell call JScript / VBScript at
run-time, get the Office constants! :)