Powershell Script not finding File on Remote Desktop

Question

I have a simple powershell script that I have saved at C:\Users\Public which contains the following code:

#run program
$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path "C:\Users\Public" -Include *.xls, *xlsm -Recurse
Foreach($file in $excelFiles)
{
    $workbook = $excel.workbooks.open("sendEmail.xlsm")
    $worksheet = $workbook.worksheets.item(1)
    $excel.Run("CDO_Mail_Small_Text")
    $workbook.save()
    $workbook.close()
}
$excel.quit()

As you can see, the script opens an excel file that's located in the same folder as the .ps1 file. The excel file is supposed to send an email. This is a test case that I created and it works fine on my local machine.

I'm trying to get this to run on an RDP. When I copy the two files from my local machine to the same location on the RDP (C:\Users\Public) and run the .ps1 file through powershell there, I get the following error:

Exception calling "Open" with "1" argument(s): "'sendEmail.xlsm' could not be found.

Since this works perfectly on my computer I have no idea why it fails to work on the remote computer. Any ideas?

Ultimately, here's what I'm trying to do:

I have an Access database on an RDP
I have an Excel file on a mapped drive

On a recurring basis, I need to automatically open the Excel file, pull in a table from the Access database, and manipulate the data within the Excel file and send an email. This is all very specific to the particulars of my business, so I welcome suggestions on alternative ideas but "just do everything in Access" isn't an option.

I made a test case where everything was on the mapped drive and it all works fine in that case, including having it run as a recurring scheduled task. I just need to figure out how to get it to work with the Access database being on an RDP.

Thanks.

score 2 · Accepted Answer · edited May 23 '17 at 12:24

2

When calling

$excel.workbooks.open("sendEmail.xlsm")

you should really pass in a full path, not just the filename.

If you just use the filename, then you're relying on a specific value for the default directory, and that's typically not a good plan.

See How do I get the directory of the PowerShell script I execute? for how you could get that full path (assuming your Excel file is in the same folder as your script)

edited May 23 '17 at 12:24

Community

1
1

answered Feb 15 '17 at 01:18

Tim Williams

154,628
8
97
125

Thanks. Isn't this line specifying the path though? $excelFiles = Get-ChildItem -Path "C:\Users\Public" -Include *.xls, *xlsm -Recurse. From this line on aren't we dealing with a collection of excel files located at C:\Users\Public? – tjnel Feb 15 '17 at 07:24
That doesn't tell Excel where to find your macro file though – Tim Williams Feb 15 '17 at 15:55

Powershell Script not finding File on Remote Desktop

1 Answers1