Powershell excel to csv script works in ISE but not in powershell console-CodePudding

I have a short script that takes certain worksheets out of an excel file and saves them individually as a CSV file.

Function ExcelToCsv ($File) {
    $myDir = Split-Path -Parent $($global:MyInvocation.MyCommand.Definition)
    $path = "$myDir\csv_files"
    If(!(test-path -PathType container $path))
    {
        Write-Host "Folder csv_files not found, creating..."
        New-Item -ItemType Directory -Path $path
    }
    $excelFile = "$myDir\"   $File   ".xlsx"
    $Excel = New-Object -ComObject Excel.Application
    $wb = $Excel.Workbooks.Open($excelFile)
    $excel.DisplayAlerts = $false;
    
    foreach ($ws in $wb.Worksheets) {
        if($ws.name -like '*sheet*') {
            $ws.SaveAs("$myDir\csv_files\"   $ws.name   ".csv", 6, 0, 0, 0, 0, 0, 0, 0, $true)
            Write-Host "Saved file: $myDir\csv_files\"$ws.name".csv"
        } else {
            Write-Host "Worksheet "$ws.name" not an correct sheet. Not saved"
        }
    }
    $Excel.Quit()
}

$FileName = "myexcel"
ExcelToCsv -File $FileName
Write-Host "`nCSV files successfully created"
read-host "Press ENTER to exit...."

This is the entire code, not much to it and this is the correct output when run from ISE:

PS U:\excel_to_csv> U:\excel_to_csv\create_csv_files.ps1
Saved file: U:\excel_to_csv\csv_files\ 1sheet .csv
Saved file: U:\excel_to_csv\csv_files\ 2sheet .csv
Saved file: U:\excel_to_csv\csv_files\ 3sheet .csv

CSV files successfully created
Press ENTER to exit....:

But when I use a Powershell console:

Folder csv_files not found, creating...
New-Item : Cannot find drive. A drive with the name 'if((Get-ExecutionPolicy ) -ne 'AllSigned') { Set-ExecutionPolicy -
Scope Process Bypass }; & 'U' does not exist.
At U:\excel_to_csv\create_csv_files.ps1:7 char:9
          New-Item -ItemType Directory -Path $path
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : ObjectNotFound: (if((Get-Executi... Bypass }; & 'U:String) [New-Item], DriveNotFoundExce
   ption
      FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.NewItemCommand

Ľutujeme, if((Get-ExecutionPolicy ) -ne 'AllSigned') { Set-ExecutionPolicy -Scope Process Bypass }; & 'U:\excel_to_csv\
myexcel.xlsx sa nepodarilo nájsť. Je možné, že bol premiestnený, premenovaný alebo odstránený.
At U:\excel_to_csv\create_csv_files.ps1:11 char:5
      $wb = $Excel.Workbooks.Open($excelFile)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : OperationStopped: (:) [], COMException
      FullyQualifiedErrorId : System.Runtime.InteropServices.COMException


CSV files successfully created
Press ENTER to exit....:

Some of it is written in Slovakian, it roughly translates to:

Sorry, if((Get-ExecutionPolicy ) -ne 'AllSigned') { Set-ExecutionPolicy -Scope Process Bypass }; & 'U:\excel_to_csv\
    myexcel.xlsx could not be found. It is possible it was moved, renamed or deleted.

I have tried searching for a solution but nothing worked yet. I tried making a bash script like this:

powershell.exe -noexit -file U:\create_csv_files.ps1

But that didn't work, not sure if I screwed it up.

As I am on a company computer I am unable to use administrator privileges, but if you believe the lack of them is the problem, I might be able to do something about it.

Thank you for any and all help!

CodePudding user response：

Replace

$myDir = Split-Path -Parent $($global:MyInvocation.MyCommand.Definition)

with

$myDir = $PSScriptRoot

The automatic $PSScriptRoot variable reliably reports the full path of the directory in which the currently executing script is located, irrespective of how the script was invoked. The related $PSCommandPath variable contains the script file's own full path.

Your symptom implies that you invoked your script from outside a PowerShell session, via powershell.exe -Command (with the -Command CLI parameter potentially being positionally implied), in which case $global:MyInvocation.MyCommand.Definition contains the entire command text passed to -Command.