Attached is sample workbook having two sheets. Sheet URL, has Web page link in Cell B2.
Kindly have a look at it in Browser to know how the data is structured/presented. Please Right click on the browser to view the source code of the page, which reveals that it uses some xhtml JSP (java server page), perhaps reason why the conventional way of importing web query does not work.
I want a VBA macro that would read URL from Sheet URL Cell B2, and bring the data in Sheet (named) Web Range B2.
Conventional code is as under
Sub Web () With Worksheets("Web") .Activate .Cells.Delete With .QueryTables.Add(Connection:="url;" & Worksheets("URL").Range("B2"), Destination:=.Range("B2")) .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .Refresh BackgroundQuery:=False End With End With End Sub
When I run above, what happens all the Table Data comes in single cell B2, with delimiter like ',' etc and it is not correctly placed within the table, as it should have been. Making it difficult to read, or process.
SAMPLE FILE IS ATTACHED having run the above macro.