I have a powershell script that downloads a CSV file from yahoo finance, and my goal is to convert it to an excel file (.xlsx). The script is:
$path = "D:"
$client = New-Object System.Net.WebClient
$url = "http://download.finance.yahoo.com/d/quotes.csv?s=EDV,VEA,VWO,VHT,BND,VTI&f=sl1d1t1c1ohgv&e=.csv"
$csv_filename = Join-Path $path "prices.csv"
$client.DownloadFile($url, $csv_filename)
$xl_filename = Join-Path $path "prices.xlsx"
$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.OpenText($csv_filename)
$wb.SaveAs($xl_filename, 51)
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
The script is located in `D:\get_prices.ps1' and I execute it with
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Unrestricted -File D:\get_prices.ps1
When I run it I get two errors:
You cannot call a method on a null-valued expression.
At D:\get_prices.ps1:14 char:11
+ $wb.SaveAs <<<< ($xl_filename, 51)
+ CategoryInfo : InvalidOperation: (SaveAs:String) [], RuntimeExc
eption
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At D:\get_prices.ps1:15 char:10
+ $wb.Close <<<< ()
+ CategoryInfo : InvalidOperation: (Close:String) [], RuntimeExce
ption
+ FullyQualifiedErrorId : InvokeMethodOnNull
The conversion code is adapted from this question-answer (How to export a CSV to Excel using Powershell).
I looked at the documentation for Workbook.SaveAs and as far as I nkow Im using it correctly, and the file format (the 51 argument) is correct too based on this. I also looked at the docs for Workbook.Close and that all looks correct too.
I'm using powershell v2.0 (according to get-host) and Excel 2013 on Windows 7 x64.
What am I doing wrong?
