Hi Team,
I am trying to pull the specific content of a webpage and rewrite it to MS Access 2010 table. I am using following script but not getting the desired result.
Function Check-Path($Database)
{
If (!(Test-Path -path (Split-Path -path $Database -parent)))
{
Throw “$(Split-Path -path $Database -parent) Does not Exist”
}
ELSE
{
If (!(Test-Path -Path $Database))
{
Throw “$Database does not exist”
}
}
} #End Check-Path
$SRU_WebPage = “New Rules”
if (-not $SRU_WebPage)
{
$SRU_WebPage = Read-Host -Prompt “Please enter the SRU Link”
}
$ie = New-Object -ComObject “InternetExplorer.Application”
$ie.Navigate(“$SRU_WebPage”)
#$ie.Navigate(“New Rules”)
#Write-Host “Loading Page”
#while ($ie.Busy)
#{
sleep -Seconds 10
#}
Write-Host “Processing Page”
$SRUNumber = (($ie.Document.body.innerText.Split(“`n”) | Select-String “This SRU Number: (.)").Matches.Groups[1].Value).ToString().Trim()
$SEUNumber = (($ie.Document.body.innerText.Split(“`n”) | Select-String "This SEU Number: (.)”).Matches.Groups[1].Value).ToString().Trim()
$PageSource = $ie.Document.body.innerHTML
$PageSource = $PageSource.Split(“`n”)
Function Connect-Database($Database, $Tables)
{
$adOpenStatic = 3
$adLockOptimistic = 3
$conn = New-Object -com "ADODB.Connection"
$rs = New-Object -com "ADODB.Recordset"
$conn.Open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Database;Persist Security Info=True;')
$Row = 2
$PageSource | % {
if ($_ -match "(\d+)(\d+)(.*)(.*)(off|drop)(off|drop)(off|drop)")
{
$rs.AddNew("SELECT * FROM $Tables", $conn, $adOpenStatic, $adLockOptimistic)
$rs.Fields.Item("SRU").value = $SRUNumber
$rs.Fields.Item("SEU").value = $SEUNumber
$rs.Fields.Item("GID").value = $Matches[1]
$rs.Fields.Item("SID").value = $Matches[2]
$rs.Fields.Item("Rule_Group").value = $Matches[3]
$rs.Update()
$conn.Close
$rs.Close
$Row++
$Row
}
}
}
*** Entry Point to Script ***
$Database = & “C:\Users\am288711\Documents\SRU_Database.accdb”
$Tables = “tbl_Rules”
Check-Path -db $Database
Connect-DataBase -db $Database -tables $Tables
#Kill the excel process
Get-Process | where {$.Name -like “MSAccess*”} | % {
$Id = $.id
if ((Get-WmiObject Win32_Process -Filter “ProcessID = ‘$id’” | select CommandLine) -like “embed”)
{
Stop-Process -Id $id
}
}
Kindly Help.
I sorted it a little bit.
$URL = “New Rules”
$OutputFile = “$env:temp\tempfile.html”
$data = Invoke-WebRequest -Uri $URL
@($data.ParsedHtml.getElementsByTagName(“table”))[0].OuterHTML | Set-Content -Path $OutputFile
$Excel = @(Resolve-Path “C:\Program Files*\Microsoft Office\Office*\MSACCESS.EXE”)[0].Path
& $Excel $OutputFile
It is working fine when I am saving it with EXCEL.EXE instead of MSACCESS.EXE.
When it opens a fresh ACCESS page, it is terminating with the error that “Microsoft Access does not support opening HTML pages”
Is there any way round to work out this situation?