Get URL from intranet link name

200yrs · May 13th 2016

3 days ago from now i has been searching answers but still cant find it. Looking forwards this forum can help me...every month we downloaded over-time form from intranet by clicking a link...So i want to make a vba to get the URL from one of the link name in site. The attach image is the example...i want to get the URL encirled in red and paste into excel (filename otform.xlsm cell A1)..really need help...thanks
[ATTACH=CONFIG]69100[/ATTACH]

200yrs · May 14th 2016

Re: Get URL from intranet link name...need help.

Anyone here can help me? This forum suppose to be active.

200yrs · May 17th 2016

Re: Get URL from intranet link name...need help.

Last call for help [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_frown.gif]

mrmmickle1 · May 17th 2016

Re: Get URL from intranet link name...need help.

There are several ways to accomplish your task. Past this code in a regular code module.

Here is one way:

Code

Option Explicit
Private pWebAddress As String
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                                                                  ByVal lpOperation As String, ByVal lpFile As String, _
                                                                                  ByVal lpParameters As String, ByVal lpDirectory As String, _
                                                                                  ByVal nShowCmd As Long) As Long




Public Sub NewShell(cmdLine As String, lngWindowHndl As Long)
    ShellExecute lngWindowHndl, "open", cmdLine, "", "", 1
End Sub




Public Sub WebPage()
    pWebAddress = "https://google.starttest.com"
    Call NewShell(pWebAddress, 3)
End Sub

Display More

Please refer to this thread I answered a while back for another method using IE: CLICK HERE FOR SECOND OPTION

200yrs · May 21st 2016

Re: Get URL from intranet link name...need help.

Hello it is not the one i want actually...maybe I want to simplify my question....please help me fix my VBA code to look for "MyLink" link name from the site (intranet)....and then click it ...My code is below but cant make it to work....Please help...

Code

Sub Button1_Click()




    Dim internet As Object
    Dim internetdata As Object
    Dim div_result As Object
    Dim header_links As Object
    Dim link As Object
    Dim URL As String




    Set internet = CreateObject("InternetExplorer.Application")
    internet.Visible = True




    URL = "http://web.myweb.com/location/myplace/en-US/OnD/RH/RH%20Form/Pages/default.aspx"
    internet.Navigate URL




    While internet.ReadyState >= 4
        DoEvents
    Wend




    Application.Wait Now + TimeSerial(0, 0, 5)




   Set link = ie.document.getElementsByTagName("a")
   
   For Each h In header_links
    If h.innerText = "MyLink" Then
        Set link = h.ChildNodes.Item(0)
        link.Click




    End If
  
Next




End Sub

Display More

**pike** · May 21st 2016

Re: Get URL from intranet link name...need help.

200yrs,
Please edit your post and add code tags to the VBA

200yrs · May 21st 2016

Re: Get URL from intranet link name...need help.

Done....thanks...please help : -)

JohnCleary · May 21st 2016

Re: Get URL from intranet link name...need help.

Cross posted on MrExcel.

mrmmickle1 · May 21st 2016

Re: Get URL from intranet link name...need help.

Please don't cross post. It wastes community member's time. Sometimes it takes a lot of effort to provide a solution. When you cross post this effort is duplicated, which is very disrespectful to the people who are working on a solution....considering the question could very likely have already been answered on another forum.

Try this:

Code

Dim mySingleLink As Object
Dim myLinks As Object
Dim myClickLink As Object




Set myLinks = ie.document.getElementsByTagName("a")




For Each mySingleLink In myLinks
   If mySingleLink.innerText = "MyLink" Then
   Set myClickLink = mySingleLink.ChildNodes.Item(0)
    myClickLink.Click
   End If
Next

Display More

Note: I have posted the answer on the Mr. Excel Thread as well....

200yrs · May 24th 2016

Re: Get URL from intranet link name...need help.

Hello... looks not working...it only open the site and do nothing,...really thankful for your continual support and understanding a newbie like me. Below is my code....pls help me check what went wrong...thanks

Code

Sub Button1_Click()




Dim ie As InternetExplorer
Dim html As HTMLDocument
Dim Link As Object
Dim ElementCol As Object
Dim mySingleLink As Object
Dim myLinks As Object
Dim myClickLink As Object
 
Application.ScreenUpdating = False




Set ie = New InternetExplorer
ie.Visible = True








ie.Navigate "MyURL/Pages/default.aspx"
    




On Error Resume Next
Do While ie.ReadyState <> READYSTATE_COMPLETE
Application.StatusBar = "Loading website..."
DoEvents
Loop




Set html = ie.document




Set myLinks = ie.document.getElementsByTagName("a")
For Each mySingleLink In myLinks
    If mySingleLink.innerText = "OT Claim Form (May 2016 Payroll)" Then
        Set myClickLink = mySingleLink.ChildNodes.Item(0)
        myClickLink.Click
    End If
Next




Set ie = Nothing
Application.StatusBar = ""
Application.ScreenUpdating = True




End Sub

Display More

The image below is the link name i want to click using vba....thanks a lot.

[ATTACH=CONFIG]69218[/ATTACH]

**pike** · May 24th 2016

Re: Get URL from intranet link name...need help.

Can you add your cross post link to all forums

mrmmickle1 · May 24th 2016

Re: Get URL from intranet link name...need help.

Change this section of code to this and report back:

Code

Set myLinks = ie.document.getElementsByTagName("a") 
    For Each mySingleLink In myLinks 
        If mySingleLink.innerText = "OT Claim Form (May 2016 Payroll)" Then 
          
    msgBox "Link found....which means click code is wrong"
        'Set myClickLink = mySingleLink.ChildNodes.Item(0) 
        'myClickLink.Click 




        End If 
    Next

Display More

It's difficult to troubleshoot IE automation without html source code. Hopefully this helps you identify the issue, but I'm guessing it will just bring more questions.

If you get the msgbox then your link is being found correctly and your click code is the issue....otherwise the find the Link portion of the code is wrong....

If the click code is wrong maybe consider trying this:

Code

Set myLinks = ie.document.getElementsByTagName("a") 
    For Each mySingleLink In myLinks 
        If mySingleLink.innerText = "OT Claim Form (May 2016 Payroll)" Then 
        mysingleLink.Click
        End If 
    Next

Cross Posted Here: http://www.mrexcel.com/forum/e…t-link-name-pls-help.html

200yrs · May 26th 2016

Re: Get URL from intranet link name...need help.

Hello mrmmickle1...thanks a lot for the continual support...really owe you this if we can solve...i tried the code you recommended but it only open the intranet site and then do nothing....second code also got the same result...so i am sharing you the html code as per request, hope it can help...thanks a lot in advance.
The flow is like this actually, every end of the month we open the intranet site and then click the download link of the over time form. The download link name is "OT Claim Form (May 2016 Payroll)" and keep changing the link name depends on the month. But let me handle on the changing names...The only think i need is how the vba excel code to automatic open the intranet and click the download link name...thanks

Code

<tr class="fliesstext " valign="top" bgcolor="#ffffff"><td width="5%" valign="top" style="vertical-align:top"></td>
<td valign="top" style="width:412px;vertical-align:top"><div><a href="/location/place/place/20Form/OT_May_16.xls" target="_blank">OT Claim Form (May 2016 Payroll)</a></div></td></tr>
<tr class="fliesstext " valign="top" bgcolor="#eeeeee"><td width="5%" valign="top" style="vertical-align:top"><span></span><span></span></td>

mrmmickle1 · May 27th 2016

Re: Get URL from intranet link name...need help.

Did the msgbox appear when you ran the first code snippet?

200yrs · May 27th 2016

Re: Get URL from intranet link name...need help.

Quote from mrmmickle1;771493

Did the msgbox appear when you ran the first code snippet?

Nope...it only open the intranet then nothing happen next.

200yrs · May 31st 2016

Re: Get URL from intranet link name...need help.

Hello...hope you guys was having a great weekend....any update about this topic guys? thanks

**S O** · May 31st 2016

Re: Get URL from intranet link name

200yrs,

You were told to add your link(s) to your post on other site(s) in an earlier post - something you have ignored and something that is a requirement of the forum. This thread is now locked to prevent wasting anyone else's time.

Please have another read of the rules you agreed to upon joining OzGrid and ensure they are followed in future,

Regards,

S O

Get URL from intranet link name

Participate now!

Share