Windows Powershell 2.0 - How can I read ODBC from a System DSN into Excel 2007 worksheet

Question

I see in the Excel documentation the QueryTables collection and ODBCConnection object, but not how to use them in powershell.

I want to create a new workbook with a single worksheet, connect to a System DSN (doesn't need login/passowrd), and run "SELECT * FROM someTable" and have the results go into the worksheet. Excel is 2007; powershell is 2.0; the odbc connection is a system DSN.

Thanks

Answer 1

For the Excel part, you can use the new-object cmdlet with the -COM parameter to instantiate an Excel worksheet:

$xl = New-Object -Com Excel.Application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Cells.Item(1,1) = 1

Then for the data base access I would use a .NET DB access layer (PowerShell can access .NET rather easily). See this two part article(part one, part two) for details.

Answer 2

I would keep going the way that Keith was going.
Record a Macro in Excel and insert a Data Connection in the worksheet to draw down your table. The code in the recorded Macro will point to the objects and methods that you need to insert the table into your worksheet, directly from Powershell, using the approach above.

Answer 3

For me, it turned out to be as simple as (with $ws as a worksheet)

$qt = $ws.QueryTables.Add("ODBC;DSN=favoriteDSN", ws.Range("A1"), "SELECT * FROM faveTable")
$qt.Refresh()
$wb.SaveAs("H:\favoriteNewFile.xlsx")