-1

I am using the script below, found on this thread.. I would like to automatically save the Excel file in the same folder as the CSV file, using the same name as the CSV file.

How can I get the folder location and filename?

$CSVs = @()
$args.ForEach({
    If ((Test-Path $_) -and ($_ -Match "\.csv$|\.txt$")) {
        $CSVs += ,$_
    } 
})

if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()

    for ($i=0; $i -lt $CSVs.Count; $i++){

        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename

        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType  = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }
}
codedemon
  • 11

1 Answers1

1

It/they would be in $args. $args will only be populated if the script is launched from the SendTo shortcut created in your linked post.

$args | ForEach{
  echo ('Path: "{0}"' -f (Split-Path $_))
  echo ('FIle: "{0}"' -f (Split-Path $_ -Leaf))
}
Keith Miller
  • 10,694
  • 1
  • 20
  • 35