I want to use a powershell script to get data from an excel sheet to query an online database

I used this code to open the spreadsheet from powershell

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Documents\Book4.xlsx")
$ws = $wb.Sheets.Item(1)

But I can't really work on the spreadsheet ie reference cells. What I need is to select contents of individual cells, store in a variable and use to populate a query for an online database.

I used this code to access the website url

$IE=new-object -com internetexplorer.application

But I've not been able to fill the form or query the database.

Please how can I do this using powershell? Thanks


2 Answers


Use the $ws.Columns

Example (get the text in column2, row 2):

$query = $ws.Columns[2].Rows[2].Text

Then you could use and navigate to the google using the query string, example:

$IE.navigate2(("http://www.google.com?q={0}" -f $query))

You could also use the Range to fetch the cells (if multiple) and select it's text.

Example (get cells between A2-A99, and select the text):

$queries = $ws.Range("A2","A99")|select Text

Then you will have an array with the queries in mind if you want to iterate over them.


Thank you so much guys.

This code works well for google although you might have to do some formatting to the query when the browser launches

#To open spreadsheet
$xl = New-Object -COM "Excel.Application" 
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Users\Documents\Book4.xlsx")
$ws = $wb.Sheets.Item("Team Sheet") #Incase of multiple sheets
$queries = $ws.Range("D9")|select Text #specify cell use ("D9", "D99") for range 

#To open urls
$IE=new-object -com internetexplorer.application
$IE.navigate2(("http://www.google.com?q={0}" -f $queries))

Just in case anyone needs it