Hello, I am working on a project where I go to a websight and enter a print number in a field using VBA with excel. It does a search and gives me a hyperlink of the print number that it searched for. Is there anyway to copy the hyperlink address and bring it into excel. I hoped I explained this right.
How are you entering the data into the fields? If you're using an Internet Explorer object, you can get the URL by using IE.LocationURL
Or is the address somewhere in the page?
I am using Explorer Object. The address is somewhere on the page. I know how to get to a field on a page and enter text then submit but i dont know how to copy a link's address on a page. It is the last piece to my puzzle here and I just cant figure it out.
Do the links all follow a certain pattern (eg. all start with "http://www.domain.com/directory/*" ) or if not are they located in the same place on the page or anything? Do they all have the same link text, with a different url?
It is definately possible to do this, if you're only looking for a yes/no answer. If you want help with this, we'll need to know some more information.
If its not something you can share with us, you can always loop through the links on a page:[vba] Dim IE As InternetExplorer, IEDoc As Object, IELink As Object
Set IE = GetObject(, "internetexplorer.application")
Set IEDoc = IE.Document
For Each IELink In IEDoc.Links
Debug.Print IELink.innerText & " (" & IELink.toString & ")"
Next
Set IE = Nothing
Set IEDoc = Nothing
Set IELink = Nothing[/vba]Matt
I definetly want help with this, yes the links are usually one in the same place but may have 2 or 3 in a certain pattern. All have the same URL. I can't share the webpage for work reasons. If i link though the page how can i copy the links. Can you show me that with the code you gave me. I wish I could stay longer but I have to go home and I will be back tomorrow. Hope you can still help me. I really would appreciate it.
I'm happy to help, but I can't say I fully understand what you mean.. You're looking to get 1-3 links from a website, that all have the same URL? Are you trying to get the underlying text from it?Originally Posted by gsouza
Say the html was:
<a href="http://www.vbaexpress.com">VBAX</a>
Do you want the VBAX from that or the http://www.vbaexpress.com ?
To use my code snippet above, for testing, open up an IE window, make it the only window open (no windows explorer either, I don't think), and run the code. In the Immediate window of the VBA editor, you'll lines like (using the above example):
VBAX (http://www.vbaexpress.com)
for each of the links on the page. the "IELink.innerText" refers to the "VBAX" portion, and IELink.toString refers to the "http://www.vbaexpress.com"
Since you're already using IE to paste your form data, just add this to your code after you're at the page you want to take links from (change IE to whatever your IE object variable is):
[vba] Dim IELink As Object
For Each IELink In IE.Document.Links
Debug.Print IELink.innerText & " (" & IELink.toString & ")"
Next[/vba]
This is just one example of getting links from a page, you can also easily just parse the text looking for your links, if they follow a pattern. But I can't tell you which is better without knowing what your level of knowledge is, and exactly what you're doing. Though the above routine isn't the most efficient, it is the easier way to do it, and should at least help you stumble upon what you're looking for.
Matt
I get to the page I want open, there is one link, even if there links I only want the first link. I pasted your sample in my code but it does not work. I am going to talk to my IT guy and see if he can help. I know i am doing something wrong but I don't know what it is. you see this is the address http://pdneng01/pdm/doc_queryx.exe mabye it wont work here because it is intranet.
Maybe this will help. this is the code I am using with a little explaination.
[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
drawingnum = ActiveCell
Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate "http://pdneng01/"
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
For Each ieForm In ie.document.forms
ULogin = True
ieForm(3).document.drawingform.doc_num.Value = drawingnum
ieForm.submit
Exit For
Set ie = Nothing
Next
'Here i have 4 seconds to right click on the link and copy the link shortcut.
Application.Wait (Now + TimeValue("0:00:4"))
foo ' this is a macro that closes sight.
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Select
End If
End Sub [/VBA]
Can you post your foo macro too? Seems strange you'd be closing IE from a separate macro when you can just use a single line in your existing macro...
In any case, try this, see if it works, I've shortened the way you're submitting stuff on the forms:[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Dim IE As InternetExplorer, ieForm As Object, ULogin As Boolean, FirstLink As String
Set IE = New InternetExplorer
IE.Visible = True
IE.Navigate "http://pdneng01/"
Do Until IE.readyState = 4 '4=READYSTATE_COMPLETE
DoEvents
Loop
IE.Document.forms(0)(3).Document.drawingform.doc_num.Value = ActiveCell.Value
IE.Document.forms(0).submit
Do Until IE.readyState = 4 '4=READYSTATE_COMPLETE
DoEvents
Loop
FirstLink = IE.Document.Links(0).toString
Set IE = Nothing
foo ' this is a macro that closes sight.
ActiveCell.Offset(0, 1).Value = FirstLink
End If
End Sub[/vba]
If that doesnt work, this should at least (more like your original, but with extracting a link)[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Dim IE As InternetExplorer, ieForm As Object, ULogin As Boolean, FirstLink As String
Set IE = New InternetExplorer
IE.Visible = True
IE.Navigate "http://pdneng01/"
Do Until IE.readyState = 4 '4=READYSTATE_COMPLETE
DoEvents
Loop
For Each ieForm In IE.Document.forms
ULogin = True
ieForm(3).Document.drawingform.doc_num.Value = ActiveCell.Value
ieForm.submit
Do Until IE.readyState = 4 '4=READYSTATE_COMPLETE
DoEvents
Loop
FirstLink = IE.Document.Links(0).toString
Exit For
Next
Set IE = Nothing
foo ' this is a macro that closes sight.
ActiveCell.Offset(0, 1).Value = FirstLink
End If
End Sub[/vba]Matt
[VBA]Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Const WM_CLOSE As Long = &H10
Sub foo()
Dim Ret As Long
Ret = FindWindow("IEFrame", vbNullString)
PostMessage Ret, WM_CLOSE, 0&, 0&
End Sub [/VBA]
If all that does is close the IE window, just put[vba]IE.Quit[/vba]Right before the line [VBA]Set IE = Nothing[/vba] to do the same (and avoid the API calls).
OH MY GOD, IT WORKS GREAT. Also what I have it do is go down a list of print numbers in a column to the right and it loops until the last cell. It works great. You saved me so many hours. I will have about 3000 links automatically entered without me having to do anything except double click once in a cell. It works great. I love you for this. Thank you. I never thought I was going to get past this part of the code. My boss is going to think I am genious. You made my thanksgiving perfect. Thanks again and if you ever need anything just ask.
Thats exactly the kind of response I love to see!
Let me know if you need any more help with it, to automate it further. I'd be happy to help. You can also close this by going to Thread Tools at the top then Mark Thread Solved, though I'll still be here if you run across any other issues.
Matt
Maybe another thing, is it possible to hide internet explorer just like you would make Excel invisible or other applications invisible.
application.visible=false?
It would be nice if this worked without having to see internet explorer opening and closing all the time.
If you dont need it open, just remove the IE.Visible = True line, as it is invisible by default
Oh dah!! I did not even see that line there or I would have done it. Thanks again. This is so cool.
Glad to help!
Hi again, maybe you can help again : ), what would I put in the code if i wanted to print out the IE document that was open at the time? Say when the print link page was open how could i print that out before it closed? Hope your on line and thanks again for everything you have done for me.
Posting Permissions
Reply With Quote