0

I have been using a script to import a data.tsv file into Excel which creates a workbook then closes it. It worked in PowerShell 4 under Windows 8.1. I've upgraded to Windows 10 with PowerShell 5. Now it is no longer working.

As far as I can tell, by debugging line by line in ISE, it is this line:

$xl.Workbooks.OpenText($importcsv)

The script: 

#Import to xlsx
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$wbpath=Join-Path "$psscriptroot" 'data.xlsx'
$importcsv=Join-Path "$psscriptroot\CPU\" 'data.tsv'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.Workbooks.OpenText($importcsv)
$xl.DisplayAlerts = $false
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl.ActiveWorkbook.SaveAs($wbpath,51)
$xl.Quit()
while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){'released'}

If I run the script, it just hangs. It opens Excel in the background and just sits there. No errors.

Any ideas?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3019059
  • 187
  • 1
  • 17
  • The [Workbooks.OpenText method](https://msdn.microsoft.com/en-us/library/office/ff837097.aspx) offers little that the [Workbooks.Open method](https://msdn.microsoft.com/en-us/library/office/aa195811(v=office.11).aspx) does not and may have paramteters that (somehow) now need to be defined rather than defaulted. –  Sep 18 '15 at 09:59
  • Workbooks.Open works but outputs a ton of information on screen. Too much to post here i think. Anyway to prevent this? According to the documentation only filename is requiret in Workbooks.OpenText. – user3019059 Sep 18 '15 at 10:37
  • I've had odd results when opening a .CSV with the .OpenText method. Seems I could not overwrite delimiters like I could when opening a .TXT. Have you tried on a file with the .TXT extension? –  Sep 18 '15 at 10:54
  • Just did. Same result. But the extension is actually .tsv. – user3019059 Sep 18 '15 at 10:57
  • Can you ouput the value of `$importcsv` just to check the path looks OK ? – sodawillow Sep 18 '15 at 16:44
  • Just did, the path is correct and the documents are created correctly when i use Worksbooks.Open instead of Opentext. But Workbooks.Open outputs all of this: (okay no room). http://pastebin.com/42Y5rrHe – user3019059 Sep 21 '15 at 06:53

0 Answers0