CODEWITHSUNDEEP
sqlexcelpowershell
Glowie
Glowie

Reputation: 2309

Read single-value output that is written to EXCEL

My powershell script runs an SQL query and outputs it to EXCEL

$SQL2 = "SELECT
    COUNT(DISTINCT EVENT_DESC) AS Number_of_Downloads,
    COUNT(DISTINCT dbo.V_SEM_COMPUTER.COMPUTER_NAME) AS Number_of_Computers
FROM
    dbo.V_AGENT_SYSTEM_LOG,  dbo.V_SEM_COMPUTER

$ws = $wb.Worksheets.Item(2)
$ws.name = "Totals"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL2)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }

The EXCEL output is

enter image description here

How do I read the value of 2484 into powershell variable?

Right now, I query the database twice, and the value of "Number_of_Computers" changes in a matter of seconds, hence I need to record the value only once.

Upvotes: 0

Views: 53

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36277

Get the .Value2 property of the cell in question. This should do it for you:

$NumOfComps = $ws.Cells.item(2).value2

Sorry, you can get an exact cell with:

$NumOfComps = $ws.Range("B2","B2")

That just specifies a single cell range.

Upvotes: 1

Adrian
Adrian

Reputation: 6101

How about using range A1:B1

$Range = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1:B1"), $SQL2)

Upvotes: 1

Related Questions