CODEWITHSUNDEEP
sqlexcelpowershell
Glowie
Glowie

Reputation: 2309

Invoking SQL query within excel in powershell script

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?

Upvotes: 1

Views: 1174

Answers (1)

Glowie
Glowie

Reputation: 2309

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)

Upvotes: 1

Related Questions