Web Query Result Formatting...

[Sheltered]

Hi, I have a web query in Excel which gathers data from a single table on a web page.

When the data is returned it is inserted into excel over 7 rows, is there a way to format the returned data so that it is spread out on just 1 row?

The code i have so far is...

    Range("I1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://www.yell.com/search/DoSearch?state=INI&screen=VAL&companyName=Electrotech[/URL] Solutions&location=B77 1AG" _
        , Destination:=Range("I1"))
        .Name = _
        "DoSearch?state=INI&screen=VAL&companyName=Electrotech Solutions&location=B77 1AG"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "13"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = True
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With

Thanks
Pete

 

[tyhand]

Hey Shelter,

Try selecting your desired range, copying it and then pasting it as transposed. This, of course, is a quick fix, but it does the job. Peace!

'select your range
code yada yada blah blah
Selection.Copy 'copy
'select and empty cell to paste data as transposed
'to paste as transpose, paste-to cell must be empty
code blah blah yada
'paste selection
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'Voila

NOTE: I noticed you are pulling repetitive data. Mainly:
TXT details
Map/Directions
Save details

The above code works well if you only select from
"Electrotech Solutions" to "Classification". If you select all data, including the "TXT details" etc., the remaining data will paste on the rows beneath "Electrotech Solutions".


Thank
You
Have
A
Nice
Day

 

[Sheltered]

Thanks tyhand, i'll have a play around with your idea :O)

Pete