CODEWITHSUNDEEP
excelvba
Gavin
Gavin

Reputation: 27

VBA - Fetch urls from excel file for IE to navigate

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!

Upvotes: 0

Views: 494

Answers (1)

NickSlash
NickSlash

Reputation: 5100

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

Upvotes: 1

Related Questions