Match Destination Formatting - Importing Table Data from a Web Query

corollary

New Member
Joined
May 31, 2014
Messages
18
I'm trying to import a table from a webpage, but am having problems with formatting. For example, using this webpage and slice of code as an example: www.kenpom.com

Code:
    With ActiveSheet.QueryTables.Add(Connection:="URL;http://kenpom.com", _
        Destination:=Range("$A$1"))
        .name = "kenpom_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

If a team's record is, say, 12-20, that will show up as a date (12/20/15), rather than the text '12-20'.

As a general question on importing tables from a webpage - is there a way to import that table to either have no formatting, or to match the existing formatting in the destination location? I know there are other options like xmlHTTP for VBA scraping needs, but can't say I'm comfortable writing those on my own at this point - would like any solution to be adaptable to other things I can do.

Please let me know if you do - thanks.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
Try...

Code:
.WebDisableDateRecognition = [COLOR=#ff0000]True[/COLOR]

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,630
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top