- This topic has 4 replies, 4 voices, and was last updated 3 weeks, 6 days ago by
July 8, 2020 at 10:29 pm #241220ParticipantTopics: 1Replies: 1Points: 19Rank: Member
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
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!
July 9, 2020 at 5:38 am #241289ParticipantTopics: 12Replies: 38Points: 261Rank: Contributor
Those lines should work no matter location.
It would help if you included the full script (Please see how to format code).
July 10, 2020 at 1:34 pm #241598ParticipantTopics: 1Replies: 2Points: 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.
July 11, 2020 at 5:13 am #241814ModeratorTopics: 4Replies: 234Points: 1,276Rank: Community Hero
Please post your code wrapping with PRE tags and also take a look at ImportExcel module…
July 12, 2020 at 9:20 am #241925ParticipantTopics: 1Replies: 1Points: 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.
- You must be logged in to reply to this topic.