+ Reply to Thread
Results 1 to 11 of 11

Need a Clickable link to a website - URL changes

  1. #1
    Registered User
    Join Date
    08-10-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    6

    Talking Need a Clickable link to a website - URL changes

    Hello,
    I'm new here and glad to be part of this community.

    This is what I am trying to achieve. Any help would be hugely appreciated.

    I want to visually validate a number that has been entered into the spreadsheet (This number is an Australian Business Number)
    My plan is to enter the Australian Business Number into the spreadsheet, then simply click a button to load up the Australian Business Number lookup website.
    The website will show me if the ABN I entered is actually valid. I will simply look at the website, match the ABN with the customer's business name, then close the webpage.

    To do this, I have an idea, but I need some help.

    I have downloaded a certain picture. It is a picture of a magnifying glass. I would like this magnifying glass to be clickable.
    It will link the user to the ABN Lookup website, which will have the search string added.

    The link I made below works. But I want to somehow add that link to the magnifying glass picture somehow.
    I want the link to update the search text dependent on the Australian Business Number entered into the spreadsheet

    =HYPERLINK("http://abr.business.gov.au/SearchByAbn.aspx?SearchText="&R2)

    I would be so grateful for any assistance or guidance.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need a Clickable link to a website - URL changes

    I am not sure I completely understood your request. It would be easier if you could attach an excel sample book (Not a picture) with the ABN no listed on column A and what you want from the site on column B. To attach a sample, click the Go Advanced Tab- then on the middle of the page, you can see "Manage attachment"

    I tested the following number: 83 106 657 309 and got the following data from a table.


    Entity name: RIAL INTERNATIONAL PTY. LTD.
    ABN status: Active from 13 Oct 2003
    Entity type: Australian Private Company
    Goods & Services Tax (GST): Not currently registered for GST
    Main business location: VIC 3124

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Need a Clickable link to a website - URL changes

    Please Login or Register  to view this content.
    Will do it - then Assign the Macro to your image

  4. #4
    Registered User
    Join Date
    08-10-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need a Clickable link to a website - URL changes

    Thank you PFDave, you're a champion! That worked exactly how I needed it. I just needed to add PtrSafe before Function so it would work with my 64 bit software

    AB33, thanks for your post. Do either of you gentlemen think that it's possible to import data from the website tables to help me validate the information without opening a web browser?

    I have attached a sample book showing how it might work.

    Thanks again, I truly appreciate your help.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool

    Hi !

    A demonstration for ABN in D1 cell :

    PHP Code: 
    Sub Demo1()
            
    Dim oDoc As ObjectR&, T%
            
    Set oDoc CreateObject("HTMLfile")
            
    Range("C1"Cells(Rows.Count4).End(xlUp)).Offset(1).Clear
    With CreateObject
    ("MSXML2.XMLHttp")
        .
    Open "GET""http://abr.business.gov.au/SearchByAbn.aspx?SearchText=" & [D1].ValueFalse
        
    .setRequestHeader "DNT""1"
            
    On Error Resume Next
        
    .send
            On Error 
    GoTo 0
        
    If .Status 200 Then
                 oDoc
    .body.innerHTML = .responseText
            With oDoc
    .getElementsByTagName("TABLE")
                For 
    0 To Application.Min(.Length 12)
                    If 
    oDoc.frames.clipboardData.setData("Text", .Item(T).outerHTMLThen
                        R 
    Cells(Rows.Count3).End(xlUp).Row 1
                        ActiveSheet
    .Paste Cells(R3)
                        
    Cells(R3).Resize(, 2).Clear
                        
    If Cells(13).Value "Trading name" Then Exit For
                    
    End If
                
    Next
            End With
        End 
    If
    End With
    If R Then
                       oDoc
    .frames.clipboardData.clearData "Text"
        
    With Range("C1"Cells(Rows.Count4).End(xlUp))
            .
    Hyperlinks.Delete
            
    .WrapText False
            
    .Columns.AutoFit
        End With
    End 
    If
            
    Set oDoc Nothing
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 08-10-2016 at 10:25 PM. Reason: optimization …

  6. #6
    Registered User
    Join Date
    08-10-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need a Clickable link to a website - URL changes

    Wow, that is absolutely phenomenal!

    How do I commend you or add a reputation? Fantastic work. Thanks so much.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Thanks for the rep' !

    A must see : Web Scraping tutorial

  8. #8
    Registered User
    Join Date
    08-10-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need a Clickable link to a website - URL changes

    Hi Marc L,
    Thanks for the link, I will spend some time studying it.

    I have just come across an error. If you have the time to help, I'd really appreciate it.

    It seems some business's have different results on the ABN lookup website.

    When I input ABN 72147972147, that business' ABN doesn't have a business name or trading name. If I click my button a second time it makes the table go down and not refresh in the same spot. It is also generating an invisible Hyperlink. I really wish I could trouble shoot this, but I just don't understand it yet.

    If I knew how to edit the code you gave me, I would make it so it looks up only the information below. It seems every business has that information stored, so it won't malfunction in the future.

    Entity name: LUMEX PTY LTD
    ABN status: Active from 21 Dec 2010
    Entity type: Australian Private Company
    Goods & Services Tax (GST): Not currently registered for GST
    Main business location: VIC 3803

    Thanks for your time

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    For ABN 72147972147, it's only an EXCEL basics issue !

    PHP Code: 
    Sub Demo2()
            
    Dim oDoc As ObjectR&, T%
            
    Set oDoc CreateObject("HTMLfile")
            
    Range("C1"Cells(Rows.Count3).End(xlUp)).Resize(, 2).Offset(1).Clear
    With CreateObject
    ("MSXML2.XMLHttp")
        .
    Open "GET""http://abr.business.gov.au/SearchByAbn.aspx?SearchText=" & [D1].ValueFalse
        
    .setRequestHeader "DNT""1"
            
    On Error Resume Next
        
    .send
            On Error 
    GoTo 0
        
    If .Status 200 Then
                 oDoc
    .body.innerHTML = .responseText
            With oDoc
    .getElementsByTagName("TABLE")
                For 
    0 To Application.Min(.Length 12)
                    If 
    oDoc.frames.clipboardData.setData("Text", .Item(T).outerHTMLThen
                        R 
    Cells(Rows.Count3).End(xlUp).Row 1
                        ActiveSheet
    .Paste Cells(R3)
                        
    Cells(R3).Resize(, 2).Clear
                        
    If Cells(13).Value "Trading name" Then Exit For
                    
    End If
                
    Next
            End With
        End 
    If
    End With
    If R Then
                       oDoc
    .frames.clipboardData.clearData "Text"
        
    With Range("C1"Cells(Rows.Count3).End(xlUp)).Resize(, 2)
            .
    Hyperlinks.Delete
            
    .WrapText False
            
    .Columns.AutoFit
        End With
    End 
    If
            
    Set oDoc Nothing
    End Sub 

  10. #10
    Registered User
    Join Date
    08-10-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need a Clickable link to a website - URL changes

    Thank you so much. That fixed it.

    I will study this now, I need to understand how it works.

    Thanks again

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    Previous code was just to show you issue was on Excel side and at beginner level as it was just a range clear !

    According to the 3 ABN codes, loading only desired tables is not difficult :

    PHP Code: 
    Sub Demo3()
            
    Dim oDoc As ObjectR&, T%, VW
            Range
    ("C1"Cells(Rows.Count3).End(xlUp)).Resize(, 2).Offset(1).Clear
    With CreateObject
    ("MSXML2.XMLHttp")
        .
    Open "GET""http://abr.business.gov.au/SearchByAbn.aspx?SearchText=" & [D1].ValueFalse
        
    .setRequestHeader "DNT""1"
            
    On Error Resume Next
        
    .send
            On Error 
    GoTo 0
        
    If .Status 200 Then
            Set oDoc 
    CreateObject("HTMLfile")
                
    oDoc.body.innerHTML = .responseText
        End 
    If
    End With
         
    If Not oDoc Is Nothing Then
                        W 
    = [{"Entity name:","Business name","Trading name"}]
           
    With oDoc.getElementsByTagName("TABLE")
                Do
                        
    Application.Match(.Item(T).Cells(0).innerTextW0)
                 If 
    IsNumeric(VThen
                    
    If oDoc.frames.clipboardData.setData("Text", .Item(T).outerHTMLThen
                        R 
    Cells(Rows.Count3).End(xlUp).Row 1
                        ActiveSheet
    .Paste Cells(R3)
                        
    Cells(R3).Resize(, 2).Clear
                    End 
    If
                        If 
    3 Then Exit Do
                 
    End If
                           
    1
                Loop 
    While < .Length
           End With
           
    If R Then
                       oDoc
    .frames.clipboardData.clearData "Text"
                
    With Range("C3"Cells(Rows.Count3).End(xlUp)).Resize(, 2)
                    .
    Hyperlinks.Delete
                    
    .WrapText False
                    
    .Columns.AutoFit
                End With
           End 
    If
            
    Set oDoc Nothing
         End 
    If
    End Sub 
    You like it ? So thanks to …
    Last edited by Marc L; 08-12-2016 at 06:24 AM. Reason: optimization …

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to click any link in a website and extracting the new link and text in our excel sheet
    By prahladsingh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2016, 11:55 AM
  2. Entering a clickable Explorer File link....
    By yozzdi in forum Excel General
    Replies: 2
    Last Post: 10-16-2014, 07:12 PM
  3. Format cell to be a clickable link when workbook is opened on iphone
    By CW2013-work in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2013, 07:10 PM
  4. Clickable link to file in CDO Email body
    By cschoyer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2012, 02:11 PM
  5. [SOLVED] generate "clickable" link leading to a cell reference?
    By magicbob007 in forum Excel General
    Replies: 5
    Last Post: 11-22-2011, 03:37 PM
  6. Use Sheet Label as Clickable Link to
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 05-02-2011, 12:45 PM
  7. Putting a clickable link in the body of email sent from Excel
    By raehippychick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2008, 06:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1