Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Working with Excel

630 views
Skip to first unread message

Michael Gao

unread,
Jan 30, 2008, 8:55:08 AM1/30/08
to
I can work with an Excel file via the following code. My question is:
1. Can I name the worksheet that I insert?
2. How can I add a column in a specified worksheet (like sheet2)?

$a = New-Object -ComObject excel.application
$a.visible = $TRUE
$a.DisplayAlerts = $FALSE
$b = $a.Workbooks.Open("d:\abc.csv")
$b.worksheets.add()
$c = $b.worksheets.Item(1)
$c.Cells.Item(1,1).Value() = "ABC"
$b.SaveAs("D:\abc.xlsx")
$a.Quit()

Marco Shaw [MVP]

unread,
Jan 30, 2008, 10:21:35 AM1/30/08
to

Try getting an instance of that specific sheet instead of "1":

PSH>$c = $b.worksheets.Item("sheet2")
PSH>$c.Cells.Item(1,1).Value() = "ABC"

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

Marco Shaw [MVP]

unread,
Jan 30, 2008, 10:25:39 AM1/30/08
to

Sorry, and as for the first Q:
$sheet=$b.worksheets.add()
$sheet.name="foo"

Michael Gao

unread,
Jan 30, 2008, 10:38:48 AM1/30/08
to
Thank you very much for the first question. But I still cannot insert an
entire column in a worksheet. In VBScript, I can do it. Is there some
different between them?

"Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com> wrote in message
news:%23ryEOP1...@TK2MSFTNGP03.phx.gbl...

Marco Shaw [MVP]

unread,
Jan 30, 2008, 12:17:33 PM1/30/08
to
Michael Gao wrote:
> Thank you very much for the first question. But I still cannot insert an
> entire column in a worksheet. In VBScript, I can do it. Is there some
> different between them?

Can you provide some sample code (even if VBScript)? You mean you
actually want to paste a column into Excel while "pushing" the
existing column over?

Marco

Michael Gao

unread,
Jan 30, 2008, 8:32:04 PM1/30/08
to
Yes, here's some code from MS site which can insert an entire column. I
don't know why the constant is set to -4161 for column and -4121 for row,
but it does work.

Const xlShiftToRight = -4161

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Dataset 1"
objWorksheet.Cells(1,2) = "Dataset 2"
objWorksheet.Cells(1,3) = "Dataset 4"

Set objRange = objExcel.Range("C1").EntireColumn
objRange.Insert(xlShiftToRight)

Michael

"Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com> wrote in message

news:uUpdBQ2Y...@TK2MSFTNGP06.phx.gbl...

Michael Gao

unread,
Jan 30, 2008, 11:09:01 PM1/30/08
to
Thank you for the hint, I can insert an entire column now :)

Two line additional:
$e = $a.Range("A1").EntireColumn
$e.Insert()

The result is what I want. Thanks again!

Michael Gao

"Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com> wrote in message

news:uUpdBQ2Y...@TK2MSFTNGP06.phx.gbl...

0 new messages