Reputation: 27
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
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