How to execute powershell script from Excel?

Question

I have fully functional poweshell script, but when I try to run it from MS Excel 2010 I got an error message "You cannot call a method on a null-valued expression" and "cannot index into a null array". I don't know where is a problem, because as I mentioned the script works without any issues when I don't try to execute it from Excel.

Thank you for any suggestions.

$paths = Get-ChildItem 'E:\TEMP' -Filter '*.txt'
$delete = Get-Content 'E:\TEMP\TEMP1\delete.log'


ForEach ($path in $paths) {
$pathtmp = "$path.tmp"
$sr = New-Object -TypeName System.IO.StreamReader -ArgumentList $path
$sw = New-Object -TypeName System.IO.StreamWriter -ArgumentList $pathtmp

Do {
 $line = $sr.ReadLine()
 $Column =  $line.split(",")


If ($delete -notcontains $Column[1])  {
    $sw.WriteLine($line)
}
} Until ( $sr.EndOfStream )

$sr.close()
$sw.close()

Remove-Item $path
Rename-Item $pathtmp $path
}

This script will delete entire row from all text files in a directory if first column of text file matches string from delete.log file.

well, i don't know PS in any way, but out of logical thinking i i can make a guess. some of variables look like arrays, especially $Column, does it always receive a supposed value? what if some file do not have tested patterns in it? try supply this script some files you know, when in excel. ie move from temp, all files except one, for test. — user3694243
– user3694243, Commented May 28, 2016 at 3:25
I use .bat file to call powershell script. When I call this script using bat file, everything works OK. But when I call bat file from excel I will get those error messages. — Steve
– Steve, Commented May 28, 2016 at 4:01
So, what's the VBA code you use to call the PS script from Excel? — tyg
– tyg, Commented May 28, 2016 at 10:53

user3694243 · Accepted Answer · 2016-05-30 08:14:16Z

An issue of the script is that it normally executes only if it being run inside of the directory where the script is placed itself, i think.
In this situation it would be e:\temp\temp1.
This possibly was the reason why excel complains, because working directories are already being set to other places than the script.
If to modify it slightly, it is possible to make it working from anywhere.
One of the possible solutions may be with assigning a full working path to read and write files variables.
Try to use these scripts:

test.ps1 script (inside e:\temp\temp1)

$paths = Get-ChildItem 'e:\TEMP' -Filter '*.txt'
$delete = Get-Content 'e:\TEMP\TEMP1\delete.log'

ForEach ($path in $paths) {

$fpath = $path.fullname
$pathtmp = "$fpath.txt"

$sr = New-Object -TypeName System.IO.StreamReader -ArgumentList "$fpath"
$sw = New-Object -TypeName System.IO.StreamWriter -ArgumentList "$pathtmp"

Do {

$line = $sr.ReadLine()

 $Column =  $line.split(",")

 If ($delete -notcontains $Column[0]) {
$sw.WriteLine($line)
}

} Until ( $sr.EndOfStream )

 $sr.close()
 $sw.close()

Remove-Item "$fpath"
Rename-Item "$pathtmp" "$fpath"
}

excel macro

Sub test()
    Call Shell(Environ$("COMSPEC") & " /c powershell -file E:\temp\temp1\test.ps1", vbNormalFocus)
End Sub

Collectives™ on Stack Overflow

How to execute powershell script from Excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related