I'm trying to pull a specific cell value from an Excel document, and add that cell value to the end of a URL. Currently the rest of the code works, successfully opening the file, grabbing the specific cell value, and printing it, but I'm not sure how to save the variable in the Invoke-webrequest.
# start Excel
$excel = New-Object -comobject Excel.Application
#open file
$FilePath = 'FilePath'
$workbook = $excel.Workbooks.Open($FilePath)
#make it visible (just to check what is happening)
$excel.Visible = $true
#print file name
$workbook.sheets.item(1).activate()
$workSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Name
#print cell value
$WorkbookTotal=$workbook.Worksheets.item(1)
$value = $WorkbookTotal.Cells.Item(1, 1)
$value.Text
# First retrieve the website
$result = Invoke-webrequest -Uri "https://www.website.com/$value" -Method Get
$resultTable = @{}
# Get the title
$resultTable.title = $result.ParsedHtml.title
# Return the table we have built as an object
Write-Output New-Object -TypeName PSCustomObject -Property $resultTable
#close excel
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
spps -n Excel
Once it visits the domain it should print the page title, which I also have working successfully. Any help is much appreciated, and thank you in advance.
First of all, I think instead of .Text you want to use Value2
Look at this stackoverflow post to learn about string interpolation in PowerShell.
You could do it like this:
"https://www.website.com/$($value.Value2)"
or use a second variable:
$cell = $WorkbookTotal.Cells.Item(1, 1)
$value = $value.Value2
$result = Invoke-webrequest -Uri "https://www.website.com/$value" -Method Get
Does this answer your question or did you have other issues?