webanalytics40
New Member
- Joined
- Oct 22, 2015
- Messages
- 1
Hi,
I have built a macro which loops through data from one sheet ("Sheet1") to build a URL (it's an API for the web analytics tool Webtrends). The macro then loads data from the API into a new sheet "display". Each dataset has the same 4 headers: Time Period, Page, Title, Page Views.
My issue is that the looped data is being displayed along columns (horizontally), but I need the data to display row after row (vertically).
The result I want is just 4 columns being filled with hundreds of rows of data, as opposed to hundreds of columns being filled with just a couple of rows of data. Transposing seems to just paste everything under one column.
Here is my code:
Sub ConcatTest()
Dim rCell As Range
For Each rCell In Sheet1.Range("F2", Sheet1.Cells(Sheet1.Rows.Count, "F").End(xlUp))
With Sheets("display").QueryTables.Add(Connection:= _
"URL;https://ws.webtrends.com/v3/Reporting/profiles/17716/reports/oOEWQj3sUo6/?totals=all&start_period=" & rCell & "&measures=1&format=html&suppress_error_codes=true", _
Destination:=Worksheets("display").Range("A1").End(xlUp)(2, 1))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Next rCell
End Sub
Can any body see why in this code my data is looping through columns as opposed through rows?
Thanks in advance!
I have built a macro which loops through data from one sheet ("Sheet1") to build a URL (it's an API for the web analytics tool Webtrends). The macro then loads data from the API into a new sheet "display". Each dataset has the same 4 headers: Time Period, Page, Title, Page Views.
My issue is that the looped data is being displayed along columns (horizontally), but I need the data to display row after row (vertically).
The result I want is just 4 columns being filled with hundreds of rows of data, as opposed to hundreds of columns being filled with just a couple of rows of data. Transposing seems to just paste everything under one column.
Here is my code:
Sub ConcatTest()
Dim rCell As Range
For Each rCell In Sheet1.Range("F2", Sheet1.Cells(Sheet1.Rows.Count, "F").End(xlUp))
With Sheets("display").QueryTables.Add(Connection:= _
"URL;https://ws.webtrends.com/v3/Reporting/profiles/17716/reports/oOEWQj3sUo6/?totals=all&start_period=" & rCell & "&measures=1&format=html&suppress_error_codes=true", _
Destination:=Worksheets("display").Range("A1").End(xlUp)(2, 1))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Next rCell
End Sub
Can any body see why in this code my data is looping through columns as opposed through rows?
Thanks in advance!