Read Domains Froms Excel File and Open in IE

Question

I have a Excel file which which looks like this:

Visted     Domains        Comments
           yahoo.com
           google.com
           hotmail.com

All of columns are already populated.

I am trying to read the domain from Excel file, open in IE. Once it's visited, write 'yes' under 'Visited' column.

So far, this current script read from the Excel file, and opens in IE. Once I close the current IE Window, it opens the next URL.

$ExcelObject = New-Object -comobject Excel.Application
$ExcelObject.Visible = $true
$ExcelObject.DisplayAlerts = $False

$excelFile = "C:\Users\muafzal\Documents\Files\EMIE\Analyzing\list.xlsx"

$Workbook = $ExcelObject.workbooks.open($excelFile)
$Sheet = $Workbook.Worksheets.Item(1)

$row = [int]2
$domain = @() # beginnt bei 2,1... 3,1... 4,1
Do {
    $domain += $Sheet.Cells.Item($row,2).Text ; $row = $row + [int]1
} until (!$Sheet.Cells.Item($row,1).Text)

ForEach($url in $domain){
    #Start IE and make it visible
    $ie = new-object -com "InternetExplorer.Application"
    $ie.Visible = $true

    #Navigate to the URL
    $ie.Navigate($url)

    # Output URL that have been visited to a text file.
    $url |  Out-File $done -Append

    #Sleep while IE is running
    while($ie.visible){
            start-sleep -s 1
        }
}

I would like the Excel document to be writable, so I can enter comments about the website.

hallo ;) what do you mean exactly by "i would like the excel document to be writeable"? do you want to write the comment directly into the excel sheet or into, say, an input box spawned by your ps script? — wullxz, Jul 02 '15 at 00:00
I would import the info into PowerShell with [Import-Xls from the Microsoft Script Gallery](https://gallery.technet.microsoft.com/office/17bcabe7-322a-43d3-9a27-f3f96618c74b). Then visit your sites, update comments, and paste your updated info back into the Excel book. — TheMadTechnician, Jul 02 '15 at 00:19

score 0 · Answer 1 · edited May 23 '17 at 11:43

I guess by "I would like the excel document to be writeable" you mean, the PS script should do the job for you.

For that, we have to solve 2 problems:

How do we write into excel cells:
First of all, you probably don't want to use the Text property of the Range object returned by $Sheet.Cells.Item($row, 1) because it always returns what would be displayed if you had the excel sheet open (and that includes the hashes you could get if the text doesn't fit in the cell). For more information, see this question.
My guess is, that Value will be ok instead of Text - and because it's a property, you can also use it to write information.
My suggestion for your script would be to move the logic from your ForEach loop into your Do loop as you could use the $row index to also address the Visited and Comment column.
To set a Column to visited you could write for example:
```
Do {
    $domain += $Sheet.Cells.Item($row,2).Text

    # (browser magic here!)

    # edit exel sheet:
    $Sheet.Cells.Item($row, 1).Value = 'yes'

    $row = $row + [int]1
} until (!$Sheet.Cells.Item($row,1).Text)
```

How do we ask the user for the comment?
You can use the InputBox method from the Microsoft.VisualBasic.Interaction class:

# this will import the Microsoft.VisualBasic assembly and make
# the Interaction class available in $vbi
# add those two lines to the top of your script
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
$vbi = [Microsoft.VisualBasic.Interaction] # convenience variable

# ask the user for a comment
$comment = $vbi::InputBox("Write a comment about that website:", "Comment")
if ($comment -eq "") {
    echo "input box was cancelled!"
}
else {
    echo "comment:`t$comment"
}

At the end, your code might look like this:

    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
    $vbi = [Microsoft.VisualBasic.Interaction] # convenience variable
    $ExcelObject = New-Object -comobject Excel.Application
    $ExcelObject.Visible = $true
    $ExcelObject.DisplayAlerts = $False

    $excelFile = "C:\Users\muafzal\Documents\Files\EMIE\Analyzing\list.xlsx"

    $Workbook = $ExcelObject.workbooks.open($excelFile)
    $Sheet = $Workbook.Worksheets.Item(1)

    $row = [int]2
    $domain = @() # beginnt bei 2,1... 3,1... 4,1
    Do {
        $domain += $Sheet.Cells.Item($row,2).Text 

        # browser magic here:
        #Start IE and make it visible
        $ie = new-object -com "InternetExplorer.Application"
        $ie.Visible = $true

        #Navigate to the URL
        $ie.Navigate($url)

        # Output URL that have been visited to a text file.
        $url |  Out-File $done -Append

        #Sleep while IE is running
        while($ie.visible){
                start-sleep -s 1
        }

        # ask the user for a comment
        $comment = $vbi::InputBox("Write a comment about that website:", "Comment")
        if ($comment -eq "") {
            # cancel was pressed, so maybe revisit later?
            $Sheet.Cells.Item($row, 1).Value = 'no'
        }
        else {
            # edit exel sheet:
            $Sheet.Cells.Item($row, 1).Value = 'yes'
            $sheet.Cells.Item($row, 3).Value = $comment
        }

        # next row...
        $row = $row + [int]1
    } until (!$Sheet.Cells.Item($row,1).Text)

PS: I don't have an excel installed to test the code but I think it should work right away. I hope that is what you really wanted to know ;)

Excel is wonky in that you may have issues trying to set the `Value` property of a cell. I have had better luck setting the `Value2` property instead. — TheMadTechnician, Jul 02 '15 at 01:32
yeah, I was thinking about Value2 vs Value... for this purpose, Value should be ok because it's just text that gets inserted. I guess you need to try which works best. The question I mentioned in my answer might help. Couldn't test it myself. The OP has to test and maybe change Value to Value2. — wullxz, Jul 02 '15 at 01:37

Read Domains Froms Excel File and Open in IE

1 Answers1