Results 1 to 4 of 4

Thread: Import data from website

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0

    Import data from website

    Hi
    i want to download data from website in excel worksheet. i know we can download data from web with web queries.but when i open the link from Data->From Web and copy the url in address bar.
    when the webpage load completely. i dont get any import table option
    i have check the HTML code. Tables are define in code.
    i will appreciate any help on this

    web site link

  2. #2
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14

    Importing Data from Web

    Hi ExcelFun,

    Please find attached for your solution.


    Thanks & Regards
    Rahul Singh
    Attached Files Attached Files

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Another method. ENSURE that you have a sheet named DataSheet in your workbook.

    Code:
    'ASSUMPTION: THE HOST SYSTEM HAS INTERNET EXPLORER VERSION 5 OR GREATER INSTALLED
    Option Explicit
    Const strURLMain = "http://www.nseindia.com/live_market/...segmentLink=17"
    Sub ItemNumberArticleLookUp()
     
        Dim IE As Object 'InternetExplorer
        Dim htmDocument As Object 'htmlDocument
        Dim strFile As String
        Dim ihtmlTableSection As Object 'HTMLTableSection
       
        Dim lngLoop As Long
          
    ErrH:
        Err.Clear: On Error GoTo -1: On Error GoTo 0: On Error GoTo ErrH
        If Not IE Is Nothing Then
            lngLoop = lngLoop + 1
            If lngLoop > 5 Then
                MsgBox "Unable to continue!", vbOKOnly, "Cyclic Error"
                Exit Sub
            End If
            IE.Quit
            Set IE = Nothing
        End If
       
        'Start the internet explorer
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Visible = False
        'Navigate to the main URL
        IE.Navigate strURLMain
        'Hold your horses (so to say) till the page is ready :) [Ditto wherever used]
        IESTATUS IE
        Set htmDocument = IE.Document
        Set ihtmlTableSection = htmDocument.getElementsByTagName("TBODY")(2)
        strFile = ThisWorkbook.Path & "\NSE.htm"
        lngLoop = FreeFile()
        Open strFile For Output As lngLoop
        Print #lngLoop, ihtmlTableSection.parentElement.parentElement.outerHTML
        Close #lngLoop
        ThisWorkbook.Sheets("DataSheet").UsedRange.Clear
        With Workbooks.Open(strFile).Sheets(1)
            .Pictures.Delete
            .UsedRange.Columns(1).Offset(1).Clear
            .UsedRange.Columns(.UsedRange.Columns.Count).Offset(1).Clear
            .UsedRange.Copy ThisWorkbook.Sheets("DataSheet").Cells(1)
            .Parent.Close 0
        End With
        Kill strFile
        With ThisWorkbook.Sheets("DataSheet")
            .UsedRange.EntireColumn.AutoFit
            lngLoop = .Cells(1).MergeArea.Columns.Count
            .Cells(1).MergeArea.UnMerge
            .Cells(1, 2).Value = .Cells(1).Value
            .Columns(1).Delete
            .Cells(1).Resize(1, lngLoop - 1).Merge
            With .Cells(1).End(xlToRight)
                lngLoop = .MergeArea.Columns.Count
                .MergeArea.UnMerge
                .Resize(1, lngLoop - 1).Merge
            End With
            Application.Goto .Cells(1)
        End With
        IE.Quit
        Set IE = Nothing
        Exit Sub
    ErH:
        MsgBox Err.Description, vbCritical + vbOKOnly, "Unexpected Error"
        IE.Quit
       
    End Sub
    Private Sub IESTATUS(ByVal IE As Object)
     
        Do While IE.Busy = True: Loop
        Do Until IE.readystate = 4: Loop
        Application.Wait Now() + TimeValue("00:00:01")
       
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0
    Thank you so much Rahul & Admin...!
    Appreciate your time & Help !

Similar Threads

  1. Replies: 3
    Last Post: 06-10-2013, 06:12 PM
  2. Import Data From Multiple Workbooks To Another
    By Jorrg1 in forum Excel Help
    Replies: 2
    Last Post: 05-13-2013, 05:00 PM
  3. Replies: 1
    Last Post: 05-09-2013, 08:56 AM
  4. Replies: 7
    Last Post: 05-08-2013, 07:12 PM
  5. Replies: 2
    Last Post: 04-16-2013, 01:36 PM

Posting Permissions

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