Solved

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

Posted on 2006-11-01
24
44,428 Views
Last Modified: 2013-12-26
I am trying to have VBA open an instance of IE and browse to a specific
URL


Private Sub DrawingIn_Click()
   Set browser = CreateObject("InternetExplorer.Application")
   browser.Navigate ("www.mywebpage.com")
   browser.StatusBar = False
   browser.Toolbar = False
   browser.Visible = True
   browser.Resizable = False
   browser.AddressBar = False
End Sub


I got that portion to work


Now I need to search for a hypertext link named "spreadsheet" within
the page i've navigated to. The hyperlink is to download a spreadsheet.
 Clicking on it brings up the SaveAS dialog box.  I then need to save
the spreadsheet to a specific location.


Can someone please help me complete the code to


1)  Find the Spreadsheet hyperlink
2)  Click it
3) Save the target into a specified directory.


Thanks again


Cory
0
Comment
Question by:cstraim
  • 11
  • 11
  • +1
24 Comments
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17854176
This sample searches for the link "S-Grid Documentation.zip (5K)". It will be found
in the window no. 25

The link will be clicked automatically, and the download starts.

Needs 1 Form and 1 Button :
===================

Private Sub Command1_Click()

    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
     
AppActivate Me.Caption
Set o = o2.Document.All.tags("A")


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

zz = zz & "Link Index : " & r & " of " & o.length - 1
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . tabindex : " & o.Item(r).TabIndex
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . tagname : " & o.Item(r).tagname
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . href : " & o.Item(r).href
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . type : " & o.Item(r).Type
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . name : " & o.Item(r).Name
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . innerhtml : " & o.Item(r).innerhtml
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . outerhtml : " & o.Item(r).outerhtml
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . rel : " & o.Item(r).rel
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . rev : " & o.Item(r).rev
zz = zz & String(3, vbCrLf)
'
zz = zz & "A . id : " & o.Item(r).Id
zz = zz & String(3, vbCrLf)


MsgBox zz
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).Click: Exit For
End If
Next


'        o2.Quit
'        Set o2 = Nothing
End Sub
0
 

Author Comment

by:cstraim
ID: 17854519
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
0
 

Author Comment

by:cstraim
ID: 17854635
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
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17856189
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


0
 

Author Comment

by:cstraim
ID: 17858162
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
0
 

Author Comment

by:cstraim
ID: 17858193
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
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858365
I think the sleep function is part of the WSH. Can you add a reference to 'Windows Script Host' ?
0
 

Author Comment

by:cstraim
ID: 17858432
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

0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858435
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
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858474
>>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.
0
 

Author Comment

by:cstraim
ID: 17858519
If I delete the Appactivate.Me.Caption It continues, however I get a run time error for clipboard Clear
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858526
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:cstraim
ID: 17858562
I changed the sleep function...but the clipcoard.clear is causing a runtime error for some reason
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858593
>>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.
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858729
I've found this to clear the clipboard :

   ' Clear the Clipboard.
   X = EmptyClipboard()
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17858876
OpenClipboard()

EmptyClipboard()

SetClipboardText("C:\test.zip")

CloseClipboard()
0
 

Author Comment

by:cstraim
ID: 17858899
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 :)
0
 

Author Comment

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

Author Comment

by:cstraim
ID: 17858947
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.
0
 
LVL 17

Accepted Solution

by:
vb_elmar earned 500 total points
ID: 17859124
instead ...

Clipboard.Clear
Clipboard.SetText "c:\test.zip"
mySleep 30 'insert new name
SendKeys "^v"


... you can use the following :

SendKeys "c"
SendKeys ":"
SendKeys "\"
SendKeys "t"
SendKeys "e"
SendKeys "s"
SendKeys "t"
SendKeys "."
SendKeys "z"
SendKeys "i"
SendKeys "p"


the new code is :
*******************************************
mySleep 50 'wait five seconds
SendKeys "{enter}"

mySleep 30 'waiting 3 seconds before inserting new name
SendKeys "c"
SendKeys ":"
SendKeys "\"
SendKeys "t"
SendKeys "e"
SendKeys "s"
SendKeys "t"
SendKeys "."
SendKeys "z"
SendKeys "i"
SendKeys "p"




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"

0
 

Author Comment

by:cstraim
ID: 17860618
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
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 17860755
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 .
0
 

Expert Comment

by:piyushdabomb
ID: 20285281
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
0
 
LVL 2

Expert Comment

by:Hari1612
ID: 22649028
Is it possible to get the tags from a frame inside main page. If i try to access any element within fram , i get a error "Access denied"
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now