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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,534
Messages
5,511,876
Members
408,867
Latest member
Ranjeet yamgekar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top