How to copy specific value from a Webpage to Excel?

maq_ans

New Member
Joined
Jul 24, 2010
Messages
2
Greetings,
I would like to copy some specific information from a Website to Excel Sheet. I need to copy the Company Name, Address, Phone, Business Details (About Us), Contact Person into Excel Table format.
This is the link for Website : http://office-supplies.exportersindia.com/furniture/
If you goto "View Details" it would take you to below link:
http://www.exportersindia.com/company/566062/Ideal-Worldwide/
I started with manually copying and pasting into Excel Sheet, however, this is a time consuming process and requires lot of patience.
One of my friends gave me a shortcut of doing this. He asked me to copy the entire page into Sheet1 and using a simple formula (ex. =Sheet1!B16) copy the content into another sheet. This seems to be working excellent with me if everytime the Webpage details gets copied to exact Excel Cell. Say for example if I am copying the entire Webpage to Excel Sheet (A1), it will dump almost entire Website everywhere in the Exccel Sheet. If the Supplier details gets pasted to exact cell everytime then this formula works fine with me.
The problem here is, if I copy the web content into Excel Sheet it may not be necessarily get into the Cell I want to recall. Sometimes Company Name goes to A124 and for a different Supplier (when copying the entire page into Excel) may goto A126.

Is there anyway I can copy the entire page into Excel Sheet and then pull the required information to another Sheet?
I would really apprecaite your support.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Here is some code.
There is a more automated method here - but it needs lookup data in a worksheet
http://www.mrexcel.com/forum/showthread.php?t=482148

Code:
'========================================================================================
'- EXTRACT REQUIRED DATA FROM A WORKSHEET AND TRANSFER TO A DATA TABLE
'- NEEDS A WORKSHEET CALLED "DATABASE". RUN FROM THE SHEET TO SEARCH.
'- Change the header list in the code ############
'---------------------------------------------------------------------------------------
'- The inital Paste is to paste a copied web page into the active sheet
'- Here the return value is in the next column to the data header
'- In this example the dataheaders need a colon : at the end to avoid wrong find (duplicate)
'- "Web Address" has no colon.
'---------------------------------------------------------------------------------------
'- Brian Baulsom July 2010
'========================================================================================
Option Base 1
Dim ws As Worksheet
'---------------------------
Dim DataSheet As Worksheet
Dim ToRow As Long
Dim ToColumn As Integer
'---------------------------
Dim DataHeader As String
Dim DataHeaders As Variant
Dim DataItemCount As Integer
Dim DataItemNumber As Integer
Dim ReturnValue As String
Dim FoundCell As Range
'========================================================================================
'- MAIN ROUTINE
'========================================================================================
Sub GET_DATA_FROM_SHEET()
    '====================================================================================
    '- HEADERS TO LOOK FOR #########################
    DataHeaders = Array("Company Name:", "Contact Person:", "Address:", "Web Address")
    DataItemCount = UBound(DataHeaders) ' number of items in the list
    '===================================================================================
    '- PASTE FROM CLIPBOARD TO THE ACTIVE SHEET
    Set ws = ActiveSheet
    With ws
        .Cells.ClearContents
        .Paste Range("A1")
    End With
    '-------------------------------------------------------------------------------------
    '- DATASHEET SETUP
    Set DataSheet = Worksheets("Database")
    With DataSheet
        DataSheet.Range(.Cells(1, 1), .Cells(1, DataItemCount)).Value = DataHeaders ' headers to data sheet
        ToRow = DataSheet.Range("A65536").End(xlUp).Row + 1
        ToColumn = 1
    End With
    '------------------------------------------------------------------------------------
    '- LOOP ITEMS
    With ws
        For DataItemNumber = 1 To DataItemCount
            DataHeader = DataHeaders(DataItemNumber)
            '----------------------------------------------------------------------------
            '- FIND THE ITEM
            Set FoundCell = .Cells.Find(What:=DataHeader, After:=.Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
            '----------------------------------------------------------------------------
            '- DATA TO DATABASE
            If FoundCell Is Nothing Then
                ReturnValue = "n/a"
            Else
                ReturnValue = .Cells(FoundCell.Row, FoundCell.Column + 1).Value
            End If
            DataSheet.Cells(ToRow, ToColumn).Value = ReturnValue
            '----------------------------------------------------------------------------
            '- next column
            ToColumn = ToColumn + 1
        Next
    End With
    '------------------------------------------------------------------------------------
    MsgBox ("Done")
End Sub
'========================================================================================
 

maq_ans

New Member
Joined
Jul 24, 2010
Messages
2
Greetings Brian,
Thank you so much for your reply first of all.
I am afraid I am not really technical with Excel. Below code needs to be entered in Excel or any other application?
Please advise. Looks like this is what exactly I have been looking for a while and I would definetely like to try it out.
If it works, you would be my Man.

Regards,
Maqsood.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Alt + F11 key to get a macro sheet.
Copy paste code.
Put a few barcodes in a worksheet column A row 2 downward.
Alt + F8 keys to open the macro dialog & run the macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,810
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top