Using Excel to Navigate in I.E.

[makavity]

I'm the only person in my department who has any idea of writing excel macros, so I've been given the task of writing a macro to screen scrape a web site. "They're both microsoft programs, so it should be easy."

I've tried a number of applications, and it appears that the site would have to be opened, and then you'd actually have to click on links to get to where you need to go (has to do with a session id, and your chosen 'location'. Changing locations you'd need to click on a link for the session ID to be attributed to the new location).

I'm able to list the links using something like:
For i = 0 To IeDoc.links.Length - 1
Cells(i + 1, 1).Value = IeDoc.links(i).href
Next i
where IEDoc is Internet Explorer

Is it possible to do something like:
.Navigate "

http://www.tek-tips.com/index.cfm

Find the link "Personal Profile" and click on it?

The site is refreshed frequently, so the location of the link I need can move, but it's always the same link name.

I'm basically a self-taught beginner, thrust into probably a rather complex application... so any help would be appreciated.

 

[CautionMP]

makavity,
From the sound of it something like this might work for you.

Sub NavigateLinks()
On Error GoTo NavigateLinks_Error
Dim objIE As Object
Dim objAnchors As Object
Dim objAnchor As Object

'Spawn Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")

'Remove all the controls since we don't want the user to
'monkey with it
With objIE
  .AddressBar = False
  .StatusBar = False
  .MenuBar = False
  .Toolbar = 0
  'Or leave everything alone and just hide the sucker
  .Visible = True
  .Navigate "[URL unfurl="true"]http://www.tek-tips.com"[/URL]
End With

'let IE do it's thing and settle before we touch it
While objIE.Busy
  'Do Nothing
Wend
While objIE.Document.ReadyState <> "complete"
  'Again do nothing
Wend

'It appears to be so navigate to the My Replies page
objIE.Navigate "[URL unfurl="true"]http://www.tek-tips.com"[/URL]

'let IE do it's thing and settle before we touch it
While objIE.Busy
  'Do Nothing
Wend
While objIE.Document.ReadyState <> "complete"
  'Again do nothing
Wend

Set objAnchors = objIE.Document.Links
If objAnchors.Length <> 0 Then
  'Add information about each anchor to the output file
  For Each objAnchor In objAnchors
    'This will navigate to a specific link
    If objAnchor.InnerText = "VBA Visual Basic for Applications (Microsoft)" Then
      objAnchor.Click
      Exit For
    End If
  Next objAnchor
End If

Stop

Cleanup:
'Comment the following line to keep the browser open
'objIE.Quit
Set objIE = Nothing
Exit Sub

NavigateLinks_Error:
Debug.Print Err.Number, Err.Description
Stop
End Sub

I also have two FAQ's in this forum on similar subjects that may be of interest to you.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[makavity]

Excellent!!! 1 more question. Is it also possible to operate Drop Down menus, such as "

http://www.discounttire.com/dtcs/findTireSection.do"

and radial buttons like "

http://www.discounttire.com/dtcs/findRegionsByState.do?state=AZ"?

 

[CautionMP]

makavity,
Discount Tire, that's where I recognized the handle from.

Check out faq707-6399 : How to automate web forms from VBA using Internet Explorer, it covers one way to fill out web forms in Excel using VBA.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[makavity]

CautionMP
I have seen and attempted to use your FAQ, but seem to keep running into trouble understanding how to use the SETFIELDS function.

Using your program as a tool, I'll run the GetFields portion and receive some great info. However I can't tell what to do with the spreadsheet to have it entered back into the site when running the SetFields Function. I see Highlighted cells in black or yellow (or not at all) and have tried entering info into the colored fields, non color fields, etc.

Can you give me a dummy walk-through on this task? Go to

http://www.discounttire.com/dtcs/findTiresBySizeSection.do

and using your program, how do I change the field from Arizona to California?

Would this also work for the next page

http://www.discounttire.com/dtcs/findRegionsByState.do?state=CA

to select San Diego?

It may just be the functionality of where things go in the spreadsheet that is throwing me off. I've even tried stepping through it and notice it recognizes there is a value in the cell but then moves to the "Debug.Print" section in the macro.

Thanks in advance

 

[CautionMP]

makavity,
Colors are for fields you don't usually input into:Black is for buttons and Yellow is hidden fields.

[tt]SetFields()[/tt] should take the value, if any, contained in column 8(H) of the current row and pass it back to the appropriate field in the web form. If you are stepping through the code and hit one of the [tt]Debug.Print[/tt]... lines it means there is an error of some sort.[ul][li]What error are you getting?[/li][li]What input element ([tt]objInputElement[/tt]) is current when the error occurs?[/li][li]Are you familiar enough with watches or the local windows to see what the root of the error is?[/li][/ul]

On kind of a funny note, the routines from the FAQ won't run on my machine anymore, it's hanging up on [tt]GetIEApp()[/tt]. I suspect IE7. I will put it on my agenda to revisit this.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[makavity]

I have tried it a few times this morning and it seems to be having trouble with some fields and not others, so it may just be tempremental.

In choosing the STATE where the State Name "California" is listed as a Select Element, the output in the spreadsheet from the Getfields() shows "CA": California When I enter CA, California, or CA: California the macro does nothing... however on another page where I can choose a vehicle name or tire size, it works.

The program also seems to have trouble with Radio buttons (such as the cities in California) in choosing the specific city.

I'll play with some other options as it may just be what it is I'm sending back to the web site. It is definitely a useful program.