Solved

Insert new sheet into an existing excel file

Posted on 2012-03-14
4
1,566 Views
Last Modified: 2012-03-22
Hello,

Excel version 2010

I am preparing a script to insert a new sheet into an existing Excel file. The code is such:

$xl=New-Object -Com Excel.Application
$xl.Visible=$true
$wb=$xl.Workbooks.Open('c:\preparacion\prueba.xlsx')
$worksheet = $wb.worksheets.add()

I am not able to insert the new sheet in the FIRST POSITION. When I try add(1) I get an error.

Any idea to achieve my goal?
0
Comment
Question by:projectja
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:JoeKlimis
ID: 37724603
The default behaviour for me is that it creates the sheet in index position 1


This works for me .

#excel tabs
$null = [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
$testfile = "C:\Joek\Scripts\Experts-exchange\testfile.xlsx"
$xl=New-Object -Com Excel.Application
$xl.Visible=$true
$wb=$xl.Workbooks.Open($testfile)
$worksheet = $wb.worksheets.add()
#$worksheet.move($worksheet.sheets.item(1) )  # move to index position 1
$wb.sheets.item($worksheet.name).move($wb.sheets.item(1) ) #  move "sheet3" to position 1
$worksheet.name="Now in Index 1"
$wb.WorkSheets.Item("Now in Index 1").Tab.Color = [System.Drawing.Color]::Black.ToArgb() 
$xl.workbooks.save
$xl.visible=$false
$xl.quit()

Open in new window


Let me know how you get on
Joe
0
 

Accepted Solution

by:
projectja earned 0 total points
ID: 37732363
Thanks very much. It is very useful
0
 
LVL 10

Expert Comment

by:JoeKlimis
ID: 37732380
if this doent work post the results to allow me to fix it ,   this has been tested and works fine.
0
 

Author Closing Comment

by:projectja
ID: 37751414
It does work and I can apply to my enviroment
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question