VBA to open web page, find text, and click on text's URL link

Thank you very much for your help.  I will try it tomorrow when i get into the office. This code will help me out so much as this process has to be done on 50 different navigated pages.  I will post the results as soon as it's done

Thanks again

I had to try the code just by itself tonight.  It works like a charm.  Thanks again.  

I really appreciate how you helped me to see the steps the code took to get to the end result.  Is there a way to automatically have the file that is downloaded save as a certain name in a certain directory?  For testing puroses, I would just save it as c:\test.zip.  I also realized that when saving it will be overwriting the existing file c:\test.zip in the file, can vba code say yes to overwrite the existing file as well, if so, can you include that in the code as well.

Thanks again, I am buying a vba for beginners book this week off of safari, so I can have a basic understanding of how it comes together.

Cory

You can save it with another file name, eg. "c:\test.zip".

-It is also possible to overwrite the existing file c:\test.zip if
the file already exists. - Try the following sample :




Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Command1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim o2
    Set o2 = CreateObject("internetexplorer.application")
        o2.navigate "http://www.vbaccelerator.com/home/vb/code/Controls/S_Grid/S-Grid_Documentation.asp"
        o2.Visible = True
        While o2.busy: DoEvents: Wend
     
Set o = o2.Document.All.tags("A")


M = o.length: mySubmit = -1
For r = 0 To M - 1

If InStr(1, o.Item(r).innerhtml, "S-Grid Documentation.zip (5K)", vbTextCompare) Then
    'MsgBox "= F O U N D =" & vbCrLf & o.Item(r).innerhtml
    o.Item(r).focus
    o.Item(r).Click: Exit For
End If
Next

mySleep 50 'wait five seconds
SendKeys "{enter}"
Clipboard.Clear
Clipboard.SetText "c:\test.zip"

mySleep 30 'insert new name
SendKeys "^v"

mySleep 10 'tab to go to the ok button
SendKeys "{tab}"

mySleep 10 'tab to go to the ok button
SendKeys "{tab}"

mySleep 30'press the 'save' button
SendKeys "{enter}"

mySleep 20 'Msgbox appears: 'file exists - do you want to overwrite'
SendKeys "{tab}", True 'because default is 'no' we need to send 1 tab

mySleep 20
SendKeys "{enter}", True

AppActivate Me.Caption
MsgBox "file saved"

'        o2.Quit
'        Set o2 = Nothing
End Sub



Sub mySleep(ByVal deciSeconds As Long)
    For r = 1 To deciSeconds: Sleep 100: DoEvents: Next
End Sub

For some reason i cannot call the "sleep" function. I realize that it is a windows API call, but I don't have it installed in my reference library.  Do you know which reference library it is located in. I am using VBA for office 2003

The actual error message I get when i run the code is

"complile error"
Sub or Function not defined.  It then Highlights Sleep.  I looked for it in the object broswer and didnt see it

I think the sleep function is part of the WSH. Can you add a reference to 'Windows Script Host' ?

Sorry About this I know why i got the sleep function error.  I was messing around with the code sample you wrote.  So now I am trying to figure out how to get it to work

I noticed that the second line Private Sub Command1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) is made to attach this to a button on a form (at least I think it is)  So I changed it to

Private Sub Command1_MouseDown () so that I can just run it for testing.

Once I am able to run it I get the following error:

"Compile Error" Invalid use of Me Keyword.  It highlights AppActivate.Me.Caption.  I am not sure what this means

Five minutes ago I read in the internet that neither VBScript nor JavaScript
provides a SLEEP function, and we must use a While Not loop to perform our count.

Sub CountDown
Dim i, time, interval

    time = 5000
    interval = 1
    time = interval * time
      While Not (time = 0)
         time = time - 1
      Wend
Msgbox "Hello"
End Sub

>>I get the following error:
>>"Compile Error" Invalid use of Me Keyword.  It highlights AppActivate.Me.Caption.

AppActivate.Me.Caption gives the visible focus back to your VB application.

I think we can comment out this "AppActivate.Me.Caption" line.

The only disatvantage will be that the messagebox will appear 'behind' the visible IExplorer window
instead in front of it.

If I delete the Appactivate.Me.Caption It continues, however I get a run time error for clipboard Clear

I progged another mySleep function. It doesn't use the sleep function.




Sub mySleep(ByVal deciSeconds As Long)
    t = Timer: While Timer - t < (deciSeconds / 10): DoEvents: Wend
End Sub

I changed the sleep function...but the clipcoard.clear is causing a runtime error for some reason

>>the clipcoard.clear is causing a runtime error

I progged all the code in VB6. If we can't get the "clipboard.Clear"
method running, i must see what method we can use instead.

-I need 3 days to check it out.

I've found this to clear the clipboard :

   ' Clear the Clipboard.
   X = EmptyClipboard()

OpenClipboard()

EmptyClipboard()

SetClipboardText("C:\test.zip")

CloseClipboard()

I tried that and it didn't work.  i did find this though

http://www.cpearson.com/excel/clipboar.htm

I'm trying to integrate it into the code as best I can lol.  Wish i could touch the VBA book and just understand it in 10 minutes or less.  LOL.  

Thanks again for all of your help I really appreciate it :)

oh waith i didnt try the comment you just typed..let me do that and I'll get back to it :)

Ok I did this

mySleep 50 'wait five seconds
SendKeys "{enter}"
OpenClipboard()

EmptyClipboard()

SetClipboardText ("C:\test.zip")

CloseClipboard()
mySleep 30 'insert new name
SendKeys "^v"

I just copied what had in place of

Clipboard.Clear
Clipboard.SetText "c:\test.zip"

it didnt work.  So I'm also going to look at

http://www.cpearson.com/excel/clipboar.htm

Do you see anything in there that could be of use.

Thanks man...works like a charm.  I've re-set the code so that it works for the application.  You are awesome.  Instead of tabs and enter, I just used ALT menu keys

Private Sub Command1_MouseDown() '(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim o2
    Set o2 = CreateObject("internetexplorer.application")
        o2.navigate "http://www.vbaccelerator.com/home/vb/code/Controls/S_Grid/S-Grid_Documentation.asp" 'IE navigates to a webpage
        o2.Visible = False 'hides IE
        While o2.busy: DoEvents: Wend
     
Set o = o2.Document.All.tags("A")


M = o.length: mySubmit = -1
For r = 0 To M - 1

If InStr(1, o.Item(r).innerhtml, "S-Grid Documentation.zip (5K)", vbTextCompare) Then
    o.Item(r).focus
    o.Item(r).Click: Exit For
End If
Next

mySleep 50 'wait five seconds
SendKeys "%S" 'Hits the save button
mySleep 50 'wait five seconds and starts typing in the path c:\test.zip to save the file
SendKeys "c"
SendKeys ":"
SendKeys "\"
SendKeys "t"
SendKeys "e"
SendKeys "s"
SendKeys "t"
SendKeys "."
SendKeys "z"
SendKeys "i"
SendKeys "p"

mySleep 10 'waits 1 second
SendKeys "%S" 'Hits the save button
SendKeys "%c" 'Hits the Close this dialog box when download completes

mySleep 20 'Waits 2 seconds and Msgbox appears: 'file exists - do you want to overwrite'
SendKeys "%y"

o2.Quit
Set o2 = Nothing
End Sub

Sub mySleep(ByVal deciSeconds As Long)
    t = Timer: While Timer - t < (deciSeconds / 10): DoEvents: Wend
End Sub

Thanks - i thought the alt-C (alt-S) combinations are best but
I didn't suggest these combinations because my IExplorer is in german
language and we have alt-S instead of alt-C .

Wow....Looks like this seems ed to work for the Guru's here. I have a somewhat similar question but a different approach. Please take a look at my question and help me out. I've worked days on this and I think I'm going to turn sick.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22961749.html