cstraim
asked on
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("InternetExpl orer.Appli cation")
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
URL
Private Sub DrawingIn_Click()
Set browser = CreateObject("InternetExpl
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
ASKER
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
Thanks again
ASKER
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
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("internetexpl orer.appli cation")
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
-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("internetexpl
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
ASKER
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
ASKER
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
"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' ?
ASKER
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
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
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.
>>"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.
ASKER
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
Sub mySleep(ByVal deciSeconds As Long)
t = Timer: While Timer - t < (deciSeconds / 10): DoEvents: Wend
End Sub
ASKER
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 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()
' Clear the Clipboard.
X = EmptyClipboard()
OpenClipboard()
EmptyClipboard()
SetClipboardText("C:\test. zip")
CloseClipboard()
EmptyClipboard()
SetClipboardText("C:\test.
CloseClipboard()
ASKER
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 :)
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 :)
ASKER
oh waith i didnt try the comment you just typed..let me do that and I'll get back to it :)
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("internetexpl orer.appli cation")
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
Private Sub Command1_MouseDown() '(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim o2
Set o2 = CreateObject("internetexpl
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 .
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.
https://www.experts-exchange.com/questions/22961749/Opening-a-text-file-on-a-webserver-and-using-it.html
https://www.experts-exchange.com/questions/22961749/Opening-a-text-file-on-a-webserver-and-using-it.html
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"
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("internetexpl
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