x

Powershell excel script failure

Hi all

I have written a powershell script that creates a workbook in preparation for a ssis data load. The script runs fine on my laptop but on the server it seems to fail almost everytime stating cannot find .add on excel com object. Any help would be hugely appreciated!

# Start of Script 
## 
# Then we create and save a sample worksheet 
# Create Excel object 
$excel = new-object -comobject Excel.Application 

# Make Excel visible 
$excel.visible = $true 

# Create a new workbook 
$workbook = $excel.workbooks.add() 

# The default workbook has three sheets, remove 2 
$S2 = $workbook.sheets | where {$_.name -eq "Sheet2"} 
$s3 = $workbook.sheets | where {$_.name -eq "Sheet3"} 
#$s2.delete() 
$s3.delete() 
# Get sheet and update sheet name 
$s1 = $workbook.sheets | where {$_.name -eq 'Sheet1'} 
$s1.name = "Claims" 

# Get sheet and update sheet name 
$s2 = $workbook.sheets | where {$_.name -eq 'Sheet2'} 
$s2.name = "History"   

# Update workook properties 
$workbook.author = "Chill Winston" 
$workbook.title = "Excel and PowerShell play nice!" 
$workbook.subject = "Demonstrating the Power of PowerShell with Excel" 

# Next update some cells in the worksheet 'PowerShell Sample' 
$s1.range("A1:R1").Interior.ColorIndex = 11
$s1.range("A1:R1").Font.ColorIndex = 2
$s1.range("A1:R1").Font.Bold = $True

# Add column headers
$s1.Cells.Item(1,1) = "NUMBER" 
$s1.Cells.Item(1,2) = "NUMBER2" 
$s1.Cells.Item(1,3) = "START_DATE" 
$s1.Cells.Item(1,4) = "TERMINATION_DATE" 
$s1.Cells.Item(1,5) = "PARTICIPANT_CONSENT_OBTAINED" 
$s1.Cells.Item(1,6) = "WEEKLY_HOURS" 
$s1.Cells.Item(1,7) = "EMPLOYER_NAME" 
$s1.Cells.Item(1,8) = "EMPLOYER_STREET_1" 
$s1.Cells.Item(1,9) = "EMPLOYER_STREET_2" 
$s1.Cells.Item(1,10) = "STREET_3" 
$s1.Cells.Item(1,11) = "EMPLOYER_CITY" 
$s1.Cells.Item(1,12) = "COUNTY" 
$s1.Cells.Item(1,13) = "EMPLOYER_ZIP" 
$s1.Cells.Item(1,14) = "COUNTRY" 
$s1.Cells.Item(1,15) = "EMPLOYER_CONTACT_NAME" 
$s1.Cells.Item(1,16) = "EMPLOYER_CONTACT_MOBILE" 
$s1.Cells.Item(1,17) = "EMPLOYER_CONTACT_EMAIL" 
$s1.Cells.Item(1,18) = "EMPLOYER_CONTACT_PHONE" 


# And save it away: 
$s1.saveas("C:\Users\ChillWinston\Desktop\ProviderEvidence.xlsx") 

# close the worksheets
$excel.workbooks.close()
$excel.quit()

# end of script
more ▼

asked Jan 26, 2012 at 07:20 AM in Default

chillw1nston gravatar image

chillw1nston
138 10 10 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
When running on the server, are you using credentials that have the right permissions?
more ▼

answered Jan 26, 2012 at 07:35 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.3k 47 49 76

I have even tried executing the script using a .bat and run as administrator
Jan 26, 2012 at 07:35 AM chillw1nston
Turns out that being logged in as a local administrator allows the script to run. Ill just have to find out how to proxy the service or add relevent permissions to service running the script now.
Jan 26, 2012 at 07:48 AM chillw1nston
(comments are locked)
10|1200 characters needed characters left
Just throwing this out there, does the server have Excel installed on it? I'm not a PS expert but I would think that you would need Excel on the server if the script is running there.
more ▼

answered Jan 26, 2012 at 07:25 AM

JohnM gravatar image

JohnM
6.9k 1 3 7

Hi, yes its a dev server and includes full office install
Jan 26, 2012 at 07:28 AM chillw1nston

Same Office version that you have on your laptop?

Also, I'm not sure what PS GUI you might be using, if you use something like PowerGUI ( http://powergui.org/index.jspa) you can see the objects and what properties/methods are available as the script runs. Not sure if that would help, but it might give you further insight.
Jan 26, 2012 at 07:39 AM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28

asked: Jan 26, 2012 at 07:20 AM

Seen: 1370 times

Last Updated: Jan 26, 2012 at 07:31 AM