External data - macro question

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Aug 15, 2002
Messages
589
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is your text file comma separated or fixed width?

Post an example of a few lines from Notepad.
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Aug 15, 2002
Messages
589

ADVERTISEMENT

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
ABCD
1Over20
21
31
41
51
6
7
818
91
101
111
125
13
14
1517
161
171
181
199
Sheet1


What I want them to look like is this
Book1
ABCD
1Over201817
2111
3111
4111
5159
Sheet1


and when I run out of colums it should look like this
Book1
ABCD
1Over201817
2111
3111
4111
5159
6
7
81916
9132
1012
1121
1256
13
Sheet1


Thanks :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This seems to work:

Code:
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

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

Well-known Member
Joined
Aug 15, 2002
Messages
589
On 2002-10-11 07:01, Andrew Poulsom wrote:
This seems to work:

Code:
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 :)
 

Forum statistics

Threads
1,143,615
Messages
5,719,721
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top