External data - macro question

[The Idea Dude]

Hi all,

I want to retrieve data from a text file that is on a website (the text file has peoples answers to a survey). Now, I can actually get the data retrieved, the problem is that I would prefer the data to go accross the spreadsheet, for example, the responses from person 1 would go in column A, and the responses for person 2 would go in column B, until I ran out of columns, and then data would continue back to column A but on X rows down.

Does this make sense? Any ideas?

I know this might be difficult with a plain text file as my source, but..

Thanks
This message was edited by The Idea Dude on 2002-10-14 22:59

 

[Andrew Poulsom]

You could import the text file one line at a time and do this.

Or you could import the text file as normal and use Copy/PasteSpecial Transpose to turn the data around.

 

[The Idea Dude]

Thanks Andrew,

But how do I import one line at a time from the text file?

Also, i didn't explain my needs too accurately. Let's say there are 5 questions in the survey. And 300 people have answered the survey. My end goal is to have the first persons answers in column A, the second in B and so on until I get to the last column in the sheet, I then want to have the remaining responses repat as above but a few rows down.

thanks

 

[Andrew Poulsom]

Is your text file comma separated or fixed width?

Post an example of a few lines from Notepad.

 

[The Idea Dude]

On 2002-10-11 05:08, Andrew Poulsom wrote:
Is your text file comma separated or fixed width?

Post an example of a few lines from Notepad.

I have set the results to be stored as HTML in the text file

This is some of the text file results with html disabled for this post

Over 20

1

1

1

 

[Andrew Poulsom]

Well now I am a bit stuck. You said you could actually get the data retrieved. How are you doing this and what does it look like? It needs to be plain text in columns and rows, like this:

<pre>
Person Ans 1 Ans 2 Ans 3 Ans 4 Ans 5
Bill Yes No Yes No Yes
Fred No Yes No Yes No
</pre>

 

[The Idea Dude]

On 2002-10-11 05:45, Andrew Poulsom wrote:
Well now I am a bit stuck. You said you could actually get the data retrieved. How are you doing this and what does it look like? It needs to be plain text in columns and rows, like this:<pre>
Person Ans 1 Ans 2 Ans 3 Ans 4 Ans 5
Bill Yes No Yes No Yes
Fred No Yes No Yes No</pre>

I have set up a Web Query that gets the data from the text file. The destination cell is A1 and the results look like this

Book1
	A	B	C	D
1	Over20
2	1
3	1
4	1
5	1
6
7
8	18
9	1
10	1
11	1
12	5
13
14
15	17
16	1
17	1
18	1
19	9
Sheet1

What I want them to look like is this

Book1
	A	B	C	D
1	Over20	18	17
2	1	1	1
3	1	1	1
4	1	1	1
5	1	5	9
Sheet1

and when I run out of colums it should look like this

Book1
	A	B	C	D
1	Over20	18	17
2	1	1	1
3	1	1	1
4	1	1	1
5	1	5	9
6
7
8	19	16
9	1	32
10	1	2
11	2	1
12	5	6
13
Sheet1

Thanks

 

[Andrew Poulsom]

This seems to work:

Sub Test()
    Const Answers As Integer = 5
    Const Blanks As Integer = 2
    Dim OldSh As Worksheet
    Dim LastRow As Long
    Dim a As Long ' First row of original record
    Dim b As Long ' Last row of original record
    Dim c As Long ' First row of new record
    Dim NewSh As Worksheet
    Dim Col As Integer
    Set OldSh = Worksheets("Sheet1")
    LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    a = 1
    b = Answers
    c = 1
    Set NewSh = Worksheets.Add
    OldSh.Activate
    Col = 1
    Do
        OldSh.Range(Cells(a, 1), Cells(b, 1)).Copy NewSh.Cells(c, Col)
        a = a + Answers + Blanks
        b = b + Answers + Blanks
        Col = Col + 1
        If Col > 256 Then
            Col = 1
            c = c + Answers + Blanks
        End If
    Loop While b <= LastRow
End Sub

 

[ChrisUK]

Take a look at the xmlhttp object as well, this is more flexible than a webquery and every bit as useful!

Here is some code I pinched off an ASP site.

Sub GetShareInfo()

Dim xmlhttp

'Create the XML document type
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

'Get my range of the stock quotes
Set rng = Range("A1:A10")

For Each c In rng

'Have we got the last quote
If IsEmpty(c.Value) Then Exit For

'Open a connection to the server and get the Heinz share price
strURL = "http://finance.yahoo.com/q?s=" & Trim(c.Value) & "&d=v1"

'get the strURL
xmlhttp.Open "GET", strURL, False, "", ""

'send the information
xmlhttp.Send

RtnPage = xmlhttp.ResponseText

If InStr(1, RtnPage, "No such ticker symbol") Then
c.Offset(0, 1).Value = "No Such Symbol"
Else
'Find the start of the quote
QuoteStart = InStr(RtnPage, Trim(c.Value) & "</a>")

'Find the second cell in which is where the actual quote is
FirstCellStart = InStr(QuoteStart, RtnPage, "<td")
SecondCellStart = InStr(FirstCellStart + 1, RtnPage, "<td")

'We now have the information required in the 3rd cell - the quote
'The actual quote is between the and tags, so get these positions
'and hence the quote!

FirstTag = InStr(SecondCellStart, RtnPage, "")
SecondTag = InStr(FirstTag, RtnPage, "")
Quote = Mid(RtnPage, FirstTag + 3, (SecondTag - FirstTag) - 4)

c.Offset(0, 1).Value = Quote
End If

Next

End Sub

Hope this helps you.

Chris

 

[The Idea Dude]

On 2002-10-11 07:01, Andrew Poulsom wrote:
This seems to work:

Sub Test()
    Const Answers As Integer = 5
    Const Blanks As Integer = 2
    Dim OldSh As Worksheet
    Dim LastRow As Long
    Dim a As Long ' First row of original record
    Dim b As Long ' Last row of original record
    Dim c As Long ' First row of new record
    Dim NewSh As Worksheet
    Dim Col As Integer
    Set OldSh = Worksheets("Sheet1")
    LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    a = 1
    b = Answers
    c = 1
    Set NewSh = Worksheets.Add
    OldSh.Activate
    Col = 1
    Do
        OldSh.Range(Cells(a, 1), Cells(b, 1)).Copy NewSh.Cells(c, Col)
        a = a + Answers + Blanks
        b = b + Answers + Blanks
        Col = Col + 1
        If Col > 256 Then
            Col = 1
            c = c + Answers + Blanks
        End If
    Loop While b <= LastRow
End Sub

OK, this code works like a charm

Try as I may however, I can't figure out how to specify to start placing data on row X (which is equal to B3 in Sheet1). The code still needs to be able to do exactly what it already does, but start placing data at a specified row which is equal to a cell value.

Thanks again! I am getting better at this stuff, slowly