copying data from webpage and pasting to ms access

Welcome Forums General PowerShell Q&A copying data from webpage and pasting to ms access

This topic contains 1 reply, has 2 voices, and was last updated by

2 years, 8 months ago.

  • Author
  • #38982

    Points: 1
    Rank: Member

    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"
    If (!(Test-Path -Path $Database))
    Throw "$Database does not exist"
    } #End Check-Path

    $SRU_WebPage = ""

    if (-not $SRU_WebPage)
    $SRU_WebPage = Read-Host -Prompt "Please enter the SRU Link"

    $ie = New-Object -ComObject "InternetExplorer.Application"

    #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]


    # *** 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 = $
    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 = ""
    $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?

  • #38985

    Points: 0
    Rank: Member

    What error are you getting? Could you be more specific?

The topic ‘copying data from webpage and pasting to ms access’ is closed to new replies.