Powershell script works only from a specific folder

[tkcbein Participant Topics: 1 Replies: 1 Points: 19Rank: Member]

Hello,

I’m new to Powershell and to this fourm.

I created a powershell script that reads from two different excel file and a few sheets within them and writes output to a new sheet in one of the two excel file. The script works perfectly fine from the a subdirectory under my desktop.

However, if I copy the script and the excel files to a folder under c:\, the script does not run and locks the excel files as well.

Seems like some basic parameter initiation but couldn’t figure out.

I have these lines at the beginning:

$objExcel = New-Object -ComObject Excel.Application

$objExcel.Visible=$false

Will the above lines work from any directory? Do I have to set any powershell configuration parameters (path etc.)?

 

Everything else in the script is basically  assigning sheets to variables and processing cell values and writing to a new sheet.

Appreciate any help!

thanks!

-tkcbein

 

 

 

 

[Lars Panzerbjørn Participant Topics: 12 Replies: 38 Points: 261Rank: Contributor]

Those lines should work no matter location.

It would help if you included the full script (Please see how to format code).

[mrgodo50 Participant Topics: 1 Replies: 2 Points: 1Rank: Member]

The script must be running else it wouldn’t lock the files.
How is your logging? Sounds like you’re lacking some as well as not having the right try/catch statements. See if you can narrow it down a bit by sprinkling in some status statements to see how far the script progresses.

[Kiran Moderator Topics: 4 Replies: 234 Points: 1,276Rank: Community Hero]

Please post your code wrapping with PRE tags and also take a look at ImportExcel module…

https://www.powershellgallery.com/packages/ImportExcel/7.1.0

[tkcbein Participant Topics: 1 Replies: 1 Points: 19Rank: Member]

Thank you, guys!

I think I found the issue. The script is locking the excel files and thus it becomes read-only. So, I am not able to write to a new sheet within the excel file.

As a remedy, I have changed the script to write the output to a new excel file and it works.

Now, the same script does not lock the file if I run it from a folder under my user home directory.

[Author]

Posts