Improve company productivity with a Business Account.Sign Up

x
?
Solved

Insert new sheet into an existing excel file

Posted on 2012-03-14
4
Medium Priority
?
2,185 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 11

Expert Comment

by:Joe Klimis
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 11

Expert Comment

by:Joe Klimis
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

Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What is Archiving? Archiving in Exchange Online (called In-Place Archiving) provides users with additional mailbox storage space.
This article is divided into two sections. 1) First describes how you can connect to server / service / device with Username and secure password in powershell. 2) Second part is to use secure credentials and connect to vCenters which are in Link…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

589 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