Excel Get Formatted Tables From Web Pages

This was done in 2004B. It's a pretty neat way to get formatted 'tables' form Web pages, or entire pages with multiple tables and have the data correctly parsed into a worksheet. It has been tested with the 3 examples provided [just read it over to see what to comment/uncomment when testing.


;////////////////////////////////////////////////////////////////////////
; Winbatch 2004B - Excel Web Query Import                              //
;                                                                      //
; A simpler way to parse pages with pre-existing Tables                //
; Two techniques are illustrated [refer to sections in code]           //
; :section1  -  import a full page                                     //
; :section2  -  import specific tables                                 //
;       Stan Littlefield March 28, 2004 - please preserve my header    //
;////////////////////////////////////////////////////////////////////////

;constants
xlInsertDeleteCells = 1
xlEntirePage = 1
xlSpecifiedTables = 3
xlWebFormattingAll = 1
lAll = @TRUE

;assumes active Internet Connection, not a lot of error checking
:section1
;for testing full page, uncomment the URL to use, comment all of section2
;cURL = "URL;http://quote.dailystocks.com/nysepa.asp?code=xdaily"
;cURL = "URL;http://www.cric8.com/livescorecard/od_scorecard_full2_553.htm"

:section2
;for testing specific tables, comment all of section 1 
stocksymbol = "INTC"  ;Intel
lAll = @FALSE
cTables = "8,9,11"
cURL = "URL;http://quote.money.cnn.com/quote/quote?symbols=%stocksymbol%"


msg =  StrCat("Performing Excel WebQuery",@CRLF,"Importing Tables From",@CRLF,cURL)
BoxOpen("Please Wait....",msg)
oXL                  = ObjectOpen("Excel.Application")
oXL.Visible          = @FALSE   ;if testing have this set to @TRUE until you are sure 
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.ScreenUpdating   = @FALSE   ;set this to @TRUE also
oXL.Workbooks.Add()
oWS = oXL.ActiveWorkbook.Worksheets("Sheet1")
oWS.Activate

oQ = oWS.QueryTables.Add(:: Connection=cURL,Destination=oWS.Range("A1"))
oQ.Name = "MyQuery"
oQ.FieldNames = @True
oQ.RowNumbers = @False
oQ.FillAdjacentFormulas = @False
oQ.PreserveFormatting = @False
oQ.RefreshOnFileOpen = @False
oQ.BackgroundQuery = @True
oQ.RefreshStyle = xlInsertDeleteCells
oQ.SavePassword = @False
oQ.SaveData = @True
oQ.AdjustColumnWidth = @True
oQ.RefreshPeriod = 0
If lAll
   oQ.WebSelectionType = xlEntirePage
Else
   oQ.WebSelectionType = xlSpecifiedTables
   oQ.WebTables = cTables
Endif
oQ.WebFormatting = xlWebFormattingAll
oQ.WebPreFormattedTextToColumns = @True
oQ.WebConsecutiveDelimitersAsOne = @True
oQ.WebSingleBlockTextImport = @False
oQ.WebDisableDateRecognition = @False
oQ.WebDisableRedirections = @False
oQ.Refresh(::BackgroundQuery=@False)

;show results
oXL.Visible          = @TRUE
oXL.ScreenUpdating   = @TRUE

;note, file is not saved, just shown, put your own code in to save
:end
ObjectClose(oQ)
ObjectClose(oWS)
ObjectClose( oXL )
BoxShut()

Exit
;///////////////////////////////////////////////////////////////////////////

Article ID:   W16611

File Created: 2005:02:18:12:21:38

Last Updated: 2005:02:18:12:21:38

Database Search

TechHome

Excel Get Formatted Tables From Web Pages