+ Reply to Thread
Results 1 to 15 of 15

How to import data into excel from a URL using VBA

  1. #1
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    How to import data into excel from a URL using VBA

    Hello,

    Could anyone please help me to import data from a webpage into excel using vba? I have the following URL

    http://www.moneycontrol.com/stocks/c...ist.php?class=Abrasives&dest_value=&qtr=
    http://www.moneycontrol.com/stocks/c...ist.php?class=Aluminium&dest_value=&qtr=
    http://www.moneycontrol.com/stocks/c...ist.php?class=Aquaculture&dest_value=&qtr=

    The Structure of the URL is same and only the sector names are changing

    example Abrasives,Aluminium,Aquaculture etc., I have stored the sector names in Column1.

    I want to import the data into a single sheet.

    Thank you.

    Regards,
    Zaska
    Last edited by zaska; 08-28-2015 at 10:24 PM.

  2. #2
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: How to import data into excel from a URL using VBA

    Can anyone help me??

    Thank you.

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: How to import data into excel from a URL using VBA

    Take a look at this

    https://www.youtube.com/watch?v=7sZRcaaAVbg
    Cheers!
    Deep Dave

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

    Cool Hi, try this demonstration !


    Paste code to worksheet module :

    PHP Code: 
    Private Sub Demo()
        Const 
    URL "http://www.moneycontrol.com/stocks/cptmarket/sector/qtrlist.php?class="_
              SCT 
    "Aquaculture"UPD "  Updating "
        
    Application.Goto Cells(1), True:  Me.UsedRange.Clear:  [E5].Value UPD SCT " … "

        
    With CreateObject("MSXML2.XMLHttp")
            .
    Open "GET"URL SCTFalse
            
    .SetRequestHeader "DNT""1"
            
    On Error Resume Next
            
    .Send
            On Error 
    GoTo 0
            
    If .Status 200 Then T$ = .ResponseText
        End With

        
    If "" Then
            With CreateObject
    ("HTMLFile")
                   .
    write T
                
    If .parentWindow.clipboardData.setData("Text", .getElementsByTagName("TABLE")(3).outerHTMLThen
                    Application
    .ScreenUpdating False
                    Me
    .Paste [B1]:  Me.Hyperlinks.Delete
                   
    .parentWindow.clipboardData.clearData "Text"
                    
    Cells(2).Value SCT:  Cells(3).UnMerge:  Cells(3).Copy [B2]
                    
    Union([F2:G2], [I2:J2]).NumberFormat "[$-809]mmm yyyy"

                    
    With Cells(2).CurrentRegion.Rows
                        
    .Columns("B:D").Delete xlShiftToLeft
                        
    For R& = .Count 3 To 4 Step -5:  .Item(R).Resize(4).Delete xlShiftUp:  Next
                        
    .RowHeight 18.75:       .VerticalAlignment xlCenter:    .WrapText False
                        
    .Item(2).Font.Size 10:  .Item(2).HorizontalAlignment xlCenter
                        
    .Columns(1).IndentLevel 1:      .Columns(1).AutoFit
                        With 
    .Item("3:" & .Count).Columns("B:G")
                             .
    UnMerge:  .HorizontalAlignment xlRight:  .IndentLevel 1
                        End With
                        
    For 3 To .Count Step 2
                             With 
    .Item(R):  .Interior.ColorIndex CI:  .Borders.ColorIndex 15:  End With
                        Next
                    End With
                End 
    If
            
    End With
        End 
    If
                If [
    E5].Value Like UPD "*" Then [E5].ClearBeep
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 09-07-2015 at 10:46 AM. Reason: optimization …

  5. #5
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: Hi, try this demonstration !

    Thank you very much for the kind contribution. Could you please let me know how to download all the sectors at once?
    Last edited by zaska; 09-03-2015 at 01:12 PM.

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

    Exclamation Forum issue …


    My new demonstration is ready since hours but this forum refuses my post with code between tags !

    Sucuri WebSite Firewall - CloudProxy - Access Denied
    SQL injection was detected and blocked.


    But there is no SQL in my post, just as usual …
    It's not the first time I met this issue, maybe tomorrow forum will be fixed …

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

    Re: How to import data into excel from a URL using VBA



    Same forum issue today …

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

    Re: How to import data into excel from a URL using VBA



    I just succeed in another thread to post a code but here no way, even without code tags

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

    Lightbulb Try this new demonstration ‼


    I succeed to post code on another forums, problem only here !

    I found out the source issue codeline : #51. Issue gone with mod of each Replace to Replace$ !
    What a waste of time ‼

    Paste next code to worksheet module, run the new demonstration and see the result after a while (~ 4 min) :

    PHP Code: 
    Sub QTFormat(Rg As RangeSECTOR$, CI)
        
    With Rg.Rows
            
    .Cells(1).Value SECTOR:                 .Cells(21).Value = .Cells(2).Value
            
    .Range("E1:G1").Select:        Selection.HorizontalAlignment xlCenterAcrossSelection
            
    .Range("H1:J1").Select:        Selection.HorizontalAlignment xlCenterAcrossSelection
            
    .Item("1:2").Font.Bold True:  .Item(2).HorizontalAlignment xlCenter
            
    For R& = .Count 1 To 4 Step -3:  .Item(R).Resize(2).Delete xlShiftUp:  Next
            
    .RowHeight 18:                          .VerticalAlignment xlCenter
                                                 
    .Columns(1).IndentLevel 1
            With 
    .Item("3:" & .Count).Columns("E:J")
                 .
    HorizontalAlignment xlRight:            .IndentLevel 1
            End With
            
    For 1 To .Count Step 2
                With 
    .Item(R):  .Interior.ColorIndex CI:  .Borders.ColorIndex 15:  End With
            Next
        End With
    End Sub


    Sub DemoQ
    ()
        Const 
    URL "http://www.moneycontrol.com/stocks/cptmarket/sector/qtrlist.php?class="
          
    Dim Rg As Range
        Application
    .Goto Cells(1), True
        With Me
    .UsedRange:  .Clear:  .Rows.RowHeight Me.StandardHeight:  End With
                                   Cells
    (25).Value " Downloads in progress …"
                          
    Application.ScreenUpdating False
        With Me
    .QueryTables.Add("URL;" URLCells(2))
                    .
    AdjustColumnWidth False
                         
    .RefreshStyle xlOverwriteCells
                        
    .WebFormatting xlWebFormattingNone
                     
    .WebSelectionType xlEntirePage
            
    .WebDisableDateRecognition True
                                    On Error Resume Next
                                    
    .Refresh False
                                    
    If Err.Number Then BeepMe.UsedRange.Clear: .Delete: Exit Sub
                                    On Error 
    GoTo 0
            With 
    .ResultRange.Rows
                  Set Rg 
    = .Columns(1).Find("Aluminium", , , xlWhole)
                   If 
    Rg Is Nothing Then BeepMe.UsedRange.Clear: Exit Sub
                SCT 
    Rg.CurrentRegion.Value
                  Set Rg 
    = .Columns(2).Find("Company Name"Rg(12))
                   If 
    Rg Is Nothing Then BeepMe.UsedRange.Clear: Exit Sub
                
    .Item(Rg.Row Rg(30).CurrentRegion.Rows.Count ":" & .Count).Delete
                
    .Item("1:" Rg.Row 1).Delete xlShiftUp
                  Set Rg 
    Nothing:  CT = [{19,35}]:  QTFormat .Cells, (SCT(11)), CT(1)
            
    End With
                        
    .Delete
        End With

        
    For R& = 2 To UBound(SCT)
            
    With Me.QueryTables.Add("URL;" URL Replace$(Replace$(SCT(R1), " ""+"), "&""%26"), _
                                                                     Cells
    (Rows.Count2).End(xlUp)(4))
                        .
    AdjustColumnWidth False
                             
    .RefreshStyle xlOverwriteCells
                            
    .WebFormatting xlWebFormattingNone
                         
    .WebSelectionType xlSpecifiedTables
                                
    .WebTables "4"
                
    .WebDisableDateRecognition True
                
    .Refresh False:         N% = + (N% = 2)
                
    QTFormat .ResultRange, (SCT(R1)), CT(N):  .Delete
            End With
        Next
                                   Cells
    (1).Select
        With Me
    .UsedRange:  .Columns("B:D").Delete xlShiftToLeft:  .Columns(1).AutoFit:  End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 09-07-2015 at 09:11 AM. Reason: optimizing …

  10. #10
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: How to import data into excel from a URL using VBA

    Thank you very much. You helped me a lot.

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

    Re: How to import data into excel from a URL using VBA



    I've made a slight mod in QTFormat procedure …

  12. #12
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: How to import data into excel from a URL using VBA

    Thank you very much

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

    Re: How to import data into excel from a URL using VBA


    You're welcome and thanks for the rep'.

    For any tweak, post question in forum, not in private message …

  14. #14
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    359

    Re: How to import data into excel from a URL using VBA

    The Code is not importing anything on to the sheet all of a sudden. Could you please kindly look into the issue?

    I have upgraded from Office 2013 to Office365 Personal.

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

    Exclamation

    Hi !

    The website seems to have changed ‼

    Since more than one year you had time to learn how works a QueryTable within Excel !
    As I do not remember your need, first check website new address
    and update code accordingly … Do not forget Macro recorder !
    Last edited by Marc L; 02-26-2017 at 11:04 PM.

+ 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. Replies: 0
    Last Post: 05-06-2015, 10:39 AM
  2. Replies: 3
    Last Post: 01-19-2014, 12:27 PM
  3. [SOLVED] Power Pivot SQL Data Import - How to update import filter
    By minnesotaart in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-29-2013, 12:39 PM
  4. Replies: 5
    Last Post: 01-16-2013, 12:33 PM
  5. Import web data into excel does not import picture, how to substitute picture for a value
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2012, 03:29 AM
  6. Replies: 13
    Last Post: 08-08-2006, 05:05 AM
  7. [SOLVED] how to Import data in excel office2003 with data more 65000 rows
    By Abhijit in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2005, 01:07 AM

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