Fill in cells automatically with information from a website

Hello,

I'm wondering if it is possible to make a table where I can just enter the reference number and excel can fill up the remaining cells with data specific to the reference number from a website. What I mean is that I would like to put in company registration number and hopefully it will be able to pull in other data from companies house website about the company.

Just trying to make my life a bit easier. I hope it makes sense. Any help will be greatly appreciated.

Many Thanks

Abdullah

You could probably use a macro like this:

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("A1")) Is Nothing Then
 With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.companylookup.co.uk/company/" & Range("A1"), Destination:=Range( _
        "A2"))
        .Name = Range("A1")
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End If
End Sub

to detect if cell A1 has changed and load a web query of the company registration in A1 in A2. I'm not sure if it would work with companies house though, the site above uses the reference number in the http address which makes it a bit easier.

To use this, press alt+F11 to open the vba editor, click the sheet object module in the project explorer it applies to and paste the code into that module.

Thank you!

I will take my time with it as I have never used macro in my life. Your response is quite easy to follow.

Thank you again for taking the time to reply.