Invoking SQL query within excel in powershell script

Question

I am trying to invoke SQL query within EXCEL worksheet in a powershell script, so the query will print to EXCEL worksheet

$username = $credentials.UserName
$password = $credentials.GetNetworkCredential().Password
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=10.10.10.10;Initial Catalog=database;User Id=$username;Password=$password;"

$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1

if ($currentWorksheet -lt 4){
    $ws = $wb.Worksheets.Item($currentWorksheet)
}
else
{
    $ws = $wb.Worksheets.Add()
}

$currentWorksheet += 1
    $qt = $ws.QueryTables.Add($conn.ConnectionString, $ws.Range("A1"), $SQL)

When I run the script I get error

Exception calling "Add" with "3" argument(s): "Exception from HRESULT: 0x800A03EC"

I thought I am entering the correct number of parameters and values in $ws.QueryTables.Add(

How to fix this?

Glowie · Accepted Answer · 2014-08-06 20:21:04Z

1

I had to create DSN, as demonstrated here

http://blog.mclaughlinsoftware.com/2012/09/12/sql-server-odbc-osn/

Then I created variable at beginning of script

$DSN = 'SQL Server ODBC' (same as the example)

Now, value of $qt is changed,

$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL)

answered Aug 6, 2014 at 20:21

Glowie

2,30324 gold badges63 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dieter Over a year ago

Hello, I'm using sortalike code and I get stuck when trying to refresh. $qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL) if ($qt.Refresh()) I've used $excel.Visible = $True, and what happens is that Excel asks for which data source to use. How can that happen when all is configured?

Collectives™ on Stack Overflow

Invoking SQL query within excel in powershell script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related