Excel Insert Object

PowerShell Help
1

Im looking for PS code to do the following:
Im trying to insert an pdf object in excel. The manual steps are: Insert Tab, then Object(in the Text section), then keep Object Type: as Adobe Acrobat Document, then Create from File(tab), then browse to any pdf file, then click on Display as icon.

Illustrated here:

To help, I created a Macro but it generates VB code below. BTW I can activate the excel file and the worksheet already. I have the cell location as well. I just need code to insert.

#This code is generated when recording a Macro in Excel. Using the Developer Tab in Excel and pressing Start/Stop
#The Macro is created and then the edit button is pressed to get this output.

Sub Macro1()
’
’ Macro1 Macro
’

’
ActiveSheet.OLEObjects.Add(Filename:= _
“C:\Users\xxx\Downloads\PDF_Archive\myfile.pdf” _
, Link:=False, DisplayAsIcon:=True, IconFileName:= _
“C:\Windows\Installer{AC76BA86-1033-FFFF-7760-0E1108756300}_PDFFile.ico”, _
IconIndex:=0, IconLabel:= _
“Myfile.pdf”).Select
End Sub

2

Hi, welcome to the forum :wave:

Try this:

$missing = [System.Type]::missing

$workbook = 'E:\Temp\Files\Book1.xlsx'
$file     = 'E:\Temp\Files\test.pdf'

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.Open($workbook)
$ws = $wb.Worksheets.Item(1)
$ws.OLEObjects().Add($missing,$file,$false,$false,$missing,$missing,$missing,$missing,$missing,$missing,$missing)

$wb.Save()
$xl.Quit()

Reference:

3

Hi Matt,
I tried this but it’s not giving me what I want. I need to see the pdf icon like the one in shown in the link. It’s should have the file name below the icon.

4

It does have the filename below the icon when I test it so I’m not sure what would be different for you.

2023-07-25-TestExcel

Have you looked at the options for the Add() method?

5

Hi Matt,
Thanks for the reply. Just wondering, is your test.pdf file have the pdf icon in it? Or is it a pdf file?

6

The icon is not embedded if that’s what you mean?

It’s just the icon associated with that file extension.
e.g. if I change a file extension from .TXT to .PDF the PDF icon shows in Excel.

7

Ok. I was just making sure. It seems like you are doing it right. When I do it, the entire contents of the pdf gets pasted in excel. So I see the contents of test.pdf and not the pdf icon.
Also just curious, are you assigning a variable like this for example $icon = $worksheet.OLEObjects().Add($Missing …… bla bla)
Or no variable(just like what you sent originally).

I feel like I’m close. I did see the Add method:

https://www.oreilly.com/library/view/programming-excel-with/0596007663/re546.html

Seems like some of those parameters should be true but if it works for you then I’m doing something wrong.

8

I tested it using the code I posted above.

Can you share your code?

9

See attached

(Attachment insertOBJ.txt is missing)

10

Function insertobj{
param(

[parameter(Mandatory = $true)] [String]$CurrentCMDir,
[parameter(Mandatory = $true)] [String]$EvidenceFolder,
[parameter(Mandatory = $true)] [PSObject]$DeviceOBJ,
[parameter(Mandatory = $true)] [String]$ServerNum,
[parameter(Mandatory = $true)] [PSObject]$McAfeeReportOBJ

)

set-location $CurrentCMDir

write-host "Changed to CM Main Folder: "$CurrentCMDir
write-host "Selected: " $ServerNum
#get the worksheet name from psobject
$wksname = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property Worksheetname |select -ExpandProperty Worksheetname
#get the workbook name from psobject
$wbname = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property WBName |select -ExpandProperty WBName
write-host "Worksheetname: " $wksname -ForegroundColor Green

if (test-path .$wbname){
write-host “Worksheet Found: $wbname” -ForegroundColor Green}
else {write-host “No Workbook Found” -ForegroundColor Red}

$missing=[System.Type]::missing

$fullname = $CurrentCMDir + '' + $wbname
$file=‘C:\ps_scripts\temp.pdf’

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.open($fullname)
$ws = $wb.worksheets.Item(1)

$ws.oleobjects().Add($missing,$file,$false,$false,$missing,$missing,$missing,$missing,$missing,$missing,$missing)

#get the worksheet row from psobject
$WKSRow = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property WorksheetRow |select -ExpandProperty WorksheetRow
write-host $WKSRow
#$XL.Activesheet.Range(“A$WKSRow”).Activate()

$ReportNum = 1
$col = $McAfeeReportOBJ |where-object -Property Number -eq $ReportNum |Select-Object -Property WKSCol |select -ExpandProperty WKSCol

#$ws.Cells.Item($WKSRow,$col)

#$XL.ActiveSheet.Paste() | Out-Null
$wb.Save() |Out-Null
$wb.Close() |Out-Null
$xl.Quit() |Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($XL)

}

11

Before we continue, please can you edit your post and paste the code in again, this time formatting it as code.

How to format code on PowerShell.org

If you can’t see the </> icon in your toolbar, you will find it under the gear icon.

12 
Function insertobj{
param(

[parameter(Mandatory = $true)] [String]$CurrentCMDir,
[parameter(Mandatory = $true)] [String]$EvidenceFolder,
[parameter(Mandatory = $true)] [PSObject]$DeviceOBJ,
[parameter(Mandatory = $true)] [String]$ServerNum,
[parameter(Mandatory = $true)] [PSObject]$McAfeeReportOBJ

)

set-location $CurrentCMDir

write-host "Changed to CM Main Folder: "$CurrentCMDir
write-host "Selected: " $ServerNum
#get the worksheet name from psobject
$wksname = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property Worksheetname |select -ExpandProperty Worksheetname
#get the workbook name from psobject
$wbname = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property WBName |select -ExpandProperty WBName
write-host "Worksheetname: " $wksname -ForegroundColor Green

if (test-path .$wbname){
write-host “Worksheet Found: $wbname” -ForegroundColor Green}
else {write-host “No Workbook Found” -ForegroundColor Red}

$missing=[System.Type]::missing

$fullname = $CurrentCMDir + '' + $wbname
$file=‘C:\ps_scripts\temp.pdf’

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.open($fullname)
$ws = $wb.worksheets.Item(1)

$ws.oleobjects().Add($missing,$file,$false,$false,$missing,$missing,$missing,$missing,$missing,$missing,$missing)

#get the worksheet row from psobject
$WKSRow = $McAfeeserversOBJ |where-object -Property Number -eq $ServerNum |Select-Object -Property WorksheetRow |select -ExpandProperty WorksheetRow
write-host $WKSRow
#$XL.Activesheet.Range(“A$WKSRow”).Activate()

$ReportNum = 1
$col = $McAfeeReportOBJ |where-object -Property Number -eq $ReportNum |Select-Object -Property WKSCol |select -ExpandProperty WKSCol

#$ws.Cells.Item($WKSRow,$col)

#$XL.ActiveSheet.Paste() | Out-Null
$wb.Save() |Out-Null
$wb.Close() |Out-Null
$xl.Quit() |Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($XL)

}`Preformatted text`
13

The main things that jump out at me are:

  • you’re getting the workbook name and worksheet name from $McAfeeserversOBJ but your parameter is called $McAfeeReportOBJ

  • you’re referencing the worksheet by its index, rather than its name

$ws = $wb.worksheets.Item(1)

should be:

$ws = $wb.worksheets($wksname)

Other than that, I don’t see any problems with the code for inserting the file. It works fine for me when the variables are set appropriately.

14

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.