VBA - Fetch urls from excel file for IE to navigate

Question

so far I have this code referring from an older one which fetches data from excel and puts it in SAP.

What I was hoping to do was for VBA to fetch the links in excel for IE to navigate in a single window after performing specific tasks.

Sub accounts()


Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
'ShowWindow ie.hwnd, SW_SHOWMAXIMIZED
ie.Visible = True


'Create objects to application, workbook and sheets
Set xclapp = CreateObject("Excel.Application")
Set xclwbk = ActiveWorkbook
Set xclsht = xclwbk.Sheets("accounts")

'Line Reference
line_index = 2
link = ""


'Loop line
Label1:
    While Sheets("accounts").Cells(line_index, 1).Value <> ""

        'Get websites from Excel
        link = xclwbk.Sheets("accounts").Cells(line_index, 1).Value

        'Open link
        ie.Navigate link

            While ie.ReadyState <> 4 Or ie.Busy
               DoEvents
            Wend

        line_index = line_index + 1 'Loop

        Application.Wait (Now + TimeValue("0:00:3")) 'wait

    Wend

End Sub

It is now opening websites from excel but navigation fails after the 2nd line. Any thoughts?

I'm still trying to study VBA and any help would be appreciated. Thanks!

Hey @NickSlash, thanks for the reply. However, IE navigates to h t t p s://Trim(link) instead. :( — Gavin
– Gavin, Commented Jun 8, 2020 at 20:48
With ie and ie.visible = true should be outside of loop over cells containing urls; there is a missing End With, missing page wait lines and missing incrementing of line_index during loop; While loop also not complete in terms of syntax. — QHarr
– QHarr, Commented Jun 8, 2020 at 21:02
Is this actually VBA in excel? or is it VBS or something SAP related? — NickSlash
– NickSlash, Commented Jun 8, 2020 at 21:37
Hi @QHarr, I have edited the code and moved outside those you have mentioned. What is happening now is it's clicking the first link correctly, but when it goes to the second link, the program goes into error: Method 'Navigate' of object 'IWebBrowser2' failed — Gavin
– Gavin, Commented Jun 8, 2020 at 22:56

NickSlash · Accepted Answer · 2020-06-09 14:18:24Z

1

Your code as it stands in your post wont/shouldn't work. I don't think you're using VBA, possibly VBS within SAP? (that shouldn't be an issue) If your code example is a shortened version of your actual sub, it would be good to include the whole thing as your code as it might make understanding your problem easier.

Regardless, the browser opens and nothing happens.

In your line .navigate "Trim(Link)" not actually passing a trimmed version of link. You are passing the literal text "trim(link)"

The example below will navigate to URLs stored in range Sheet1!A1:A10 The code below the navigate command pauses the script while the page loads completely before continuing.

Sub Main()
Dim Browser As Object
Dim Sheet As Worksheet
Dim Row As Integer
Dim Link As String

Set Browser = CreateObject("InternetExplorer.Application")
Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Browser.Visible = True

For Index = 1 To 10
    Link = Sheet.Cells(Row, 1)
    Browser.Naviage Trim(Link)

    While Browser.ReadyState <> 4 Or Browser.Busy
        DoEvents
    Wend
Next Index

End Sub

Update

This is just a copy of your code above, adding the reference to internet controls wont fix your problem but having access to the intellitype data (code completion, method names etc) in the IDE can help with debuging.

The page(s) you are trying to click something on, are you able to give a link to see what you're trying to click?

When I run the below code it will navigate to all links on the sheet, ive added some lines to debug its process too.

If you'r in Excel, there is no need to create an excel object as its provided by default.

' Add Reference to "Microsoft Internet Controls" (Tools->References)

Sub AccountsTest()
Dim IE As New InternetExplorer
IE.Visible = True

Dim Sheet As Worksheet
Set Sheet = ThisWorkbook.Worksheets("accounts")

Dim Row As Integer
Dim Link As String

Row = 2

Debug.Print "Starting Processing Links"

While Sheet.Cells(Row, 1).Value <> ""
    Link = Sheet.Cells(Row, 1).Value2 ' here

    Debug.Print "Navigating to <" & Link & ">"

    IE.Navigate Link

    While IE.ReadyState <> 4 Or IE.Busy
        DoEvents
    Wend

    Debug.Print "Navigation Complete"

    Application.Wait (Now + TimeValue("0:00:03"))

    Row = Row + 1
Wend

Debug.Print "No More Links - End"

IE.Quit
Set IE = Nothing

End Sub

edited Jun 9, 2020 at 14:18

answered Jun 8, 2020 at 22:00

NickSlash

5,1954 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gavin Over a year ago

Hi @Nickslash, I have updated the code and is now reading the data from the excel file. What is happening now is it's clicking the first link correctly, but when it goes to the second link, the program goes into error: Method 'Navigate' of object 'IWebBrowser2' failed I'm using VBA now, but the codes I'm using are heavily based from previous VBS from SAP. Thanks for your response. :)

Gavin Over a year ago

It is now opening websites from excel but navigation fails after the 2nd row in excel. It just stops on that page and is not moving to the 3rd row. Though VBA seems to be still running as it is not showing any error, but not doing anything as well. Any thoughts?

Gavin Over a year ago

Run-time error '-2147467259 (800040005)': Automation error Unspecified error It still stops on the second link. :( Right now I'm just trying to complete the navigation of the urls in the excel file. I also have added the reference to "Microsoft Internet Controls". This is really weird. I'm using MS office 365, IE 11, if this helps.

NickSlash Over a year ago

Try running it with some sample urls (google, so, bbc etc). Is link #2 similar to #1? can you share them?

Gavin Over a year ago

Thanks again @NickSlash. Your code works when I use other websites (google, yahoo, etc.) I am trying to go to links of duplicate facebook profiles and for some reason, error shows up after the 2nd profile is loaded, and when I debug, it points to While IE.ReadyState <> 4 Or IE.Busy Here are some samples: - facebook.com/kara.lozano.7777 - facebook.com/henry.lozano.587 - facebook.com/henry.lozano.182 - facebook.com/henry.lozano.165033 - facebook.com/henry.lozano.547727

|

Collectives™ on Stack Overflow

VBA - Fetch urls from excel file for IE to navigate

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related