Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Eliminate Doubleclick link and convert to real link

    This is a real thought-provoking one (at least for me).

    A spreadsheet has thousands of links like: http://pubads.g.doubleclick.net/gamp...l_CPA_Tracking
    which point to a website, but through doubleclick tracking. Is there a way to automate converting that doubleclick link to the real link and place the real one next to it in Excel? The real link is: https://order.dominos.com/en/?route=1

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    This function will return the actual URL of the doubleclick URL. In this example, the doubleclick url is in cell A1. In A2, enter the formula =RealURL(A1). Best to start with all Internet Explorer Windows closed.

    Note: If you place this code in you own workbook, you will need to reference the Microsoft Internet Controls by opening the VB Editor > Tools > References... > Scroll down to Microsoft Internet Controls and place a check mark next to it > OK.

    This code will most likely need some modification to suit your needs.

    HTH,
    Maud

    KW_RealURL.png

    Place in a standard module:
    Code:
    Public Function RealURL(rng As Range) As String
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim OpenWindow As New SHDocVw.ShellWindows
    Dim ieApp As New SHDocVw.InternetExplorer
    Dim test As String
    '--------------------------------------------
    'OPEN INSTANCE OF IE AND NAVIGATE TO SITE
    ieApp.Visible = True
    ieApp.Navigate rng
    Do While ieApp.Busy
    Loop
    ieApp.Visible = False
    '--------------------------------------------
    'GET AND RETURN ACTUAL URL
    ThisWorkbook.Activate
    MsgBox "Getting Real URL"
    For Each ieApp In OpenWindow
        RealURL = ieApp.LocationURL
    Next ieApp
    End Function

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I did the VB check, etc. and still got the doubleclick URL in A2. Hmmm.
    Looks like yours but got the old URL

    Clip0002.jpg

    Clip0003.jpg
    Last edited by kweaver; 2015-03-12 at 21:38.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    hmmm Works fine for me. BTW, it will return the URL in any cell you put the formula. even B1.
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-12 at 21:39.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    HMMM...Yes it does in your version but not mine. Obviously, some missing setting in mine.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    This is interesting. In yours, I filled the doubleclick down and did the same in the B column.
    Notice all but 1 of these is the same and the one I really want is in B3, and one of them is blank.

    Double-Hmmm

    Clip0005.jpg

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I noticed that I had to slow down the code so that the internet speed could catch up. That was the reason for the message box. Having many links being processed with an internet window for each is most likely slowing the internet connection further. I knew this would have to be tweaked.

    I will have to experiment with placing a browser control within Excel (must be earlier than Excel 2013) or grabbing the url from a web connection.

    Maud

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm running Excel 2010 and IE 11 and FireFox 36.0.1 on Windows 7

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    While I am looking at these options, have you considered an legalities on bypassing the double click?

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I have not considered that...this is for a client, so I feel THEY need to make that decision.
    I offer no legal advice or opinion.

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Here's my legal opinion:

    "justice must be seen to be done, and must be paid to be done"

    zeddy

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    Here is the tweaked code using a browser control within Excel. There are no IE windows that open so it is much faster, however the speed of the process is dependent upon your Internet connection.

    HTH,
    Maud

    KW_Book.png

    Code:
    Sub UseInternetExplorer()
    Application.ScreenUpdating = False
    Dim site As String
    '---------------------------------------
    'GET DOUBLE CLICK URL
    With Worksheets(1)
        .WebBrowser1.Visible = True
    For I = 3 To 8
        site = .Cells(I, 1)
    '---------------------------------------
    'NAVIGATE DOUBLECLICK URL
        .WebBrowser1.Navigate2 site
        Do
        DoEvents
        Loop Until .WebBrowser1.ReadyState = 4
    '---------------------------------------
    'GET REAL URL
        'MsgBox "Getting Url " & I - 2
        Cells(I, 2) = .WebBrowser1.LocationURL
    Next I
    '---------------------------------------
    End With
    Application.ScreenUpdating = True
    End Sub
    
    
    Public Sub RESET()
        [B3:B8].ClearContents
    End Sub
    Attached Files Attached Files

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The above code will work only with Excel 2007-2010 and IE. Excel 2013 has a registry key that must be set to 0 to turn on the Internet Browser control so it can be inserted on the sheet.
    http://support.microsoft.com/en-us/kb/2793374

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That worked in the sense that it gave me the real URLs, but I also got a copy of the site, scroll bar and all for page 1 of the site. How'd that happen?

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    What you are seeing is the browser control. The site must be navigated so that there can be a URL to extract. Now that control does not necessarily need to be on that sheet. It could be on a hidden sheet but there would need to have slight adjustments to the code to look for it. The control can also be resized to make it smaller but I was unsuccessful in hiding it behind a button because it always comes to the front when the code is run. It could also be placed out of view. So, placing it on a hidden sheet is probably the best way to go.

    Basically, I left it viewable and large so you would have an idea how it works.

    Maud

  16. The Following User Says Thank You to Maudibe For This Useful Post:

    kweaver (2015-03-14)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •