Hi!

Is there any good web-pages or other sources of information about Excel and
PowerShell around?



One specific question I have is: How do I select/activate a certain
tab/sheet in a workbook?

Thanks a million!!

/Sofia
powershell newbe

Check this out for starters:
http://olddogsblog.spaces.live.com/b...21A1!129.entry

Marco

"Sofia Engvall" <@> wrote in message
news:#6KakkxuKHA.800@newsgroup

> Hi!
>
> Is there any good web-pages or other sources of information about Excel
> and PowerShell around?
>
> One specific question I have is: How do I select/activate a certain
> tab/sheet in a workbook?
>
> Thanks a million!!
>
> /Sofia
> powershell newbe
>

On Mar 3, 4:33*pm, "Sofia Engvall" <@> wrote:

> Hi!
>
> Is there any good web-pages or other sources of information about Excel and
> PowerShell around?
>
> One specific question I have is: How do I select/activate a certain
> tab/sheet in a workbook?
>
> Thanks a million!!
>
> /Sofia
> powershell newbe

The following is just a simple example..


$excel = New-Object -comobject Excel.Application
$excel.Visible = $TRUE
$workbooks = $excel.workbooks.Open("C:\users\chris\NewsDB.xlsm")
$worksheets = $workbooks.worksheets

$worksheet = $worksheets.Item("My_Worksheet")
$rg = $worksheet.UsedRange
$col = $rg.Columns
$row = $rg.Rows

# $col.Count #Counts number of columns in usedRange..
$cellItem = $worksheet.Cells.Item(1,1)

$title = $worksheet.Range("TITLE")

$title.Cells.item(3)

#~~~~~~~~~~~~~~


Hopefully this can help a little.

chris.
http://www.powershellblog.com

Thanks guys! Anyone that knows the answer to the specifc question:
How do I select/activate a certain tab/sheet in a workbook?

Thanks a million!

/Sofia


"Marco Shaw [MVP]" <marco.shaw@newsgroup_SPAMgmail.com> wrote in message
news:OxKPLL0uKHA.4636@newsgroup

> Check this out for starters:
> http://olddogsblog.spaces.live.com/b...21A1!129.entry
>
> Marco
>
> "Sofia Engvall" <@> wrote in message
> news:#6KakkxuKHA.800@newsgroup

>> Hi!
>>
>> Is there any good web-pages or other sources of information about Excel
>> and PowerShell around?
>>
>> One specific question I have is: How do I select/activate a certain
>> tab/sheet in a workbook?
>>
>> Thanks a million!!
>>
>> /Sofia
>> powershell newbe
>>

On Mar 4, 3:09*am, "Sofia" <@> wrote:

> Thanks guys! Anyone that knows the answer to the specifc question:
> How do I select/activate a certain tab/sheet in a workbook?
>
> Thanks a million!
>
> /Sofia
>
> "Marco Shaw [MVP]" <marco.shaw@newsgroup_SPAMgmail.com> wrote in messagenews:OxKPLL0uKHA.4636@newsgroup
>
>
>

> > Check this out for starters:
> >http://olddogsblog.spaces.live.com/b...21A1!129.entry

>

> > Marco

>

> > "Sofia Engvall" <@> wrote in message
> >news:#6KakkxuKHA.800@newsgroup

> >> Hi!

>

> >> Is there any good web-pages or other sources of information about Excel
> >> and PowerShell around?

>

> >> One specific question I have is: How do I select/activate a certain
> >> tab/sheet in a workbook?

>

> >> Thanks a million!!

>

> >> /Sofia
> >> powershell newbe- Hide quoted text -

>
> - Show quoted text -

# Create base object
$xl = new-object -comobject Excel.Application


# make Excel visible
$xl.visible = $true


# open a workbook
$wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")

# Get sheet1
$ws = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- Selects
sheet 1

>> Thanks guys! Anyone that knows the answer to the specifc question:

>> How do I select/activate a certain tab/sheet in a workbook?
>>
>> Thanks a million!
>>
>> /Sofia

>
> # Create base object
> $xl = new-object -comobject Excel.Application
>
> # make Excel visible
> $xl.visible = $true
>
> # open a workbook
> $wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")
>
> # Get sheet1
> $ws = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- Selects
> sheet 1

Thanks for your reply Olddog but what I am asking for is to select/activate
the sheet in Excel (visably), not to get the sheet object.

Anyone else?

Thanks again!

/Sofia

On Mar 5, 1:47*pm, "Sofia" <@> wrote:

> >> Thanks guys! Anyone that knows the answer to the specifc question:
> >> How do I select/activate a certain tab/sheet in a workbook?

>

> >> Thanks a million!

>

> >> /Sofia

>

> > # Create base object
> > $xl = new-object -comobject Excel.Application

>

> > # make Excel visible
> > $xl.visible = $true

>

> > # open a workbook
> > $wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")

>

> > # Get sheet1
> > $ws = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- Selects
> > sheet 1

>
> Thanks for your reply Olddog but what I am asking for is to select/activate
> the sheet in Excel (visably), not to get the sheet object.
>
> Anyone else?
>
> Thanks again!
>
> /Sofia- Hide quoted text -
>
> - Show quoted text -

try this:

# Create base object
$xl = new-object -comobject Excel.Application

# make Excel visible
$xl.visible = $true

# open a workbook
$wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")

# Get sheet3
$ws = $wb.worksheets | where {$_.name -eq "sheet3"} #<------- Selects

> > sheet 3

#Activat sheet 3
$ws.activate()

Thanks! The .activate works.

Here's a working example:

# Create Excel.Application object
$excel = new-object -comobject Excel.Application

# Show Excel
$excel.visible = $true

# Open a workbook
$workbook = $excel.workbooks.open("C:\Scripts\PowerShell\test.xls")

# Get sheets
$worksheet1 = $workbook.worksheets | where {$_.name -eq "sheet1"} #<-------
Selects sheet 1
$worksheet2 = $workbook.worksheets | where {$_.name -eq "sheet2"} #<-------
Selects sheet 2
$worksheet3 = $workbook.worksheets | where {$_.name -eq "sheet3"} #<-------
Selects sheet 3

# Activate sheet 3
$worksheet3.activate()

Start-Sleep 1

# Activate sheet 1
$worksheet1.activate()

Start-Sleep 1

# Activate sheet 2
$worksheet2.activate()

(Why not testrun the code before news-ing it? You use different variable
names for the Excel.Application object. Some newbe's might not get past
that. )

Thanks again!!

/Sofia

On Mar 8, 5:55*am, "Sofia" <@> wrote:

> Thanks! The .activate works.
>
> Here's a working example:
>
> # Create Excel.Application object
> $excel = new-object -comobject Excel.Application
>
> # Show Excel
> $excel.visible = $true
>
> # Open a workbook
> $workbook = $excel.workbooks.open("C:\Scripts\PowerShell\test.xls")
>
> # Get sheets
> $worksheet1 = $workbook.worksheets | where {$_.name -eq "sheet1"} #<------- *
> Selects sheet 1
> $worksheet2 = $workbook.worksheets | where {$_.name -eq "sheet2"} #<------- *
> Selects sheet 2
> $worksheet3 = $workbook.worksheets | where {$_.name -eq "sheet3"} #<------- *
> Selects sheet 3
>
> # Activate sheet 3
> $worksheet3.activate()
>
> Start-Sleep 1
>
> # Activate sheet 1
> $worksheet1.activate()
>
> Start-Sleep 1
>
> # Activate sheet 2
> $worksheet2.activate()
>
> (Why not testrun the code before news-ing it? You use different variable
> names for the Excel.Application object. Some newbe's might not get past
> that. )
>
> Thanks again!!
>
> /Sofia

Sorry about that, I got in too much of a hurry.

> Sorry about that, I got in too much of a hurry.

No problem, it worked great! That's what matters!



It turned out to be another thing that was the problem though. The collegue
I sent this for (never tried to do it myself except to run the code you guys
posted, I didn't have the time) turned out to have this problem:
http://support.microsoft.com/default...b;en-us;320369. He had tried
a lot of things but nothing worked. After adding this locale settings it all
worked.

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

So if you get funny errors from Excel and do not have an all-US mashine (his
os was set to swedish locale), try adding the above line to the script.

http://stackoverflow.com/questions/6...-workbooks-add


On general Excel programming:
http://msdn.microsoft.com/en-us/library/bb149067.aspx