Powershell code to insert excel worksheet into excel file

Question

Is it possible to embed object in to excel file?

I tried using "$sheet.OLEObjects().Add() but was not able to get the right parameters to embed the object. I'm wondering if its possible to do it using powershell.

Any help on this matter would be greatly appreciated.

Edit : 21/12/2020

Here is the code I was referring to :

$src = "C:\Temp\Temp\src.xlsm"
$dst="C:\Temp\Temp\dst.xlsx"

$excel=New-Object -ComObject Excel.Application
$excel.visible = $false
$excel.DisplayAlerts=$false
$book = $excel.workbooks.Open($src, $null, $false)

$sheet = $excel.worksheets.item("sheetname")
[void]$sheet.Activate()

$sheet.OLEObjects().Add($missing, $dst, $false, $true,$missing, 1, "Attachment', $missing, $missing, $missing)

[void] $book.save()
[void]$book.Close()
[void]$book.Quit()

The above code gives error "Unable to get the Add property of the OLEObjects class.

Also tried the below which actually attaches an empty sheet to the woorkbook but not the destination file and also gives the same error message as above.

$sheet.OLEObjects().Add('excel.sheet', $missing, $false, $true, $dst, 1, "Attachment', $missing, $missing,$missing)

What parameters were you having issues with? Here is a link to the method documentation: https://learn.microsoft.com/en-us/office/vba/api/excel.oleobjects.add — Benson Igarabuza, Dec 19 '20 at 18:34
Please add more code, maybe an error statement, so we have a better understanding what we are dealing here with. — Alex_P, Dec 20 '20 at 10:15

score 0 · Answer 1 · answered Dec 29 '20 at 10:08

0

Got it working with the below two code line additions :

$oleObjects = $sheet.OLEObjects($missing)

$oleObjects.Add($missing,$dst, $false, $true, "WINWORD.EXE", "LabelName", $missing, $missing, $missing)

answered Dec 29 '20 at 10:08

roshan nair

1
1

score 0 · Answer 2 · answered Feb 04 '23 at 00:42

0

You are trying to insert an icon into a worksheet, you aren't trying to insert an additional worksheet into a workbook

answered Feb 04 '23 at 00:42

Bbb

517
6
27

Powershell code to insert excel worksheet into excel file

2 Answers2