Home » Forums » Open Source Learning Center » Question and Answer

Help of method to copy web page table to excel sheet

Submitted by Ruchika on 4 November, 2011 - 10:20
hello Myself Ruchika and i need your help in solving a part of my project, where in i am trying to pull web page table to a excel file. and for that i tried to use the code that we have for web query. though its not displaying any error but many a time it don’t pull the data. though we have a table available on web page. below is the code that i had put in to pull that data. 
Sub imp_data_workflow_summary(e_time, loc)
Sheets("imp_data").Select
    Cells.Select
    Selection.ClearContents
 
 
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://genesis.prod.fedex.com:7001/genWeb/cse/reports/stationWorkflow/stationWorkflow.jsp?reportType=summary&discrepancy=false&stationId=" & loc & "&endTime=" & e_time & " &batchDestCd=All" _
        , Destination:=Range("$A$1"))
        .Name = _
        "stationWorkflow.jsp?reportType=summary&discrepancy=false&stationId=" & loc & "&endTime=" & e_time & " &batchDestCd=All"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub
thanks in advance for your Guidance regards, Ruchika
‹ Copy a range of data to new worksheet when matching data found Log linear (money market) interpolation "Pricing derivatives" ›
»
Vishesh's picture

Please provide the parameters

Submitted by Vishesh on 4 November, 2011 - 19:09.

Please provide the parameters for testing the above function.

»

Parameters Details

Submitted by Anonymous on 8 November, 2011 - 11:26.

hi Vishesh
in this i had used parameters e_time& loc where
e_time is a date and if i take yesterday date then its going to be "11%2F08%2F2011"
loc is for location like "dela"

»