Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46558
  • Last Modified:

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

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
cstraim
Asked:
cstraim
  • 11
  • 11
  • +1
1 Solution
 
vb_elmarCommented:
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
 
cstraimAuthor Commented:
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
 
cstraimAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vb_elmarCommented:
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
 
cstraimAuthor Commented:
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
 
cstraimAuthor Commented:
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
 
vb_elmarCommented:
I think the sleep function is part of the WSH. Can you add a reference to 'Windows Script Host' ?
0
 
cstraimAuthor Commented:
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
 
vb_elmarCommented:
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
 
vb_elmarCommented:
>>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
 
cstraimAuthor Commented:
If I delete the Appactivate.Me.Caption It continues, however I get a run time error for clipboard Clear
0
 
vb_elmarCommented:
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
 
cstraimAuthor Commented:
I changed the sleep function...but the clipcoard.clear is causing a runtime error for some reason
0
 
vb_elmarCommented:
>>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
 
vb_elmarCommented:
I've found this to clear the clipboard :

   ' Clear the Clipboard.
   X = EmptyClipboard()
0
 
vb_elmarCommented:
OpenClipboard()

EmptyClipboard()

SetClipboardText("C:\test.zip")

CloseClipboard()
0
 
cstraimAuthor Commented:
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
 
cstraimAuthor Commented:
oh waith i didnt try the comment you just typed..let me do that and I'll get back to it :)
0
 
cstraimAuthor Commented:
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
 
vb_elmarCommented:
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
 
cstraimAuthor Commented:
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
 
vb_elmarCommented:
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
 
piyushdabombCommented:
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
 
Hari1612Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now