Web Import Query

[robcarr]

Dear All,

I am using the coding below to import data in to an excel sheet range a10, when the data is imported it puts all the data into column A, what I would like it do is put all the data into indivdual columns (b thru wherever it ends)

Sub test()
Sheets("Data&quot.Select
pathname = [c3].Value

Set shFirstQtr = Sheets("data&quot
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:=pathname, _
Destination:=shFirstQtr.Cells(10, 1))
With qtQtrResults
.WebSingleBlockTextImport = True
.Refresh
End With
End Sub

example of finished results currently.

Name,£10 Offer(Offer),£150 Offer(Offer),Business(Letters),Business(E-Mails),Business(Faxes),Business(Phone),Employee(Offer),Pensioners(Offer),Residential(Letters),Residential(E-Mails),Residential(Faxes),Residential(Phone),Referral(Letters),Referral(E-Mails),Referral(Faxes),Referral(Phone),Vantive(Letters),Vantive(E-Mails),Vantive(Faxes),Vantive(Phone)
agents,0,0,0,0,0,0,0,0,0,19,0,0,0,0,0,0,0,0,0,0
agent1,0,0,4,0,0,0,0,0,1,0,0,0,3,0,0,0,0,0,0,0
agent2,0,0,4,0,8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
agent3,0,0,0,0,0,0,15,0,0,0,0,0,0,0,0,0,0,0,0,0
agent4,0,0,12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
agent5,0,0,21,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
agent6,0,0,0,3,0,9,0,0,0,16,1,1,0,0,0,0,0,0,0,0

all this data ends up in column A starting at a10, down to a19, does anyone know how to get this to work.name to Vantive(phone) is the header row).

regards




Thanks Rob.

 

[acron]

Try the following line of code just before End Sub :

[A10:A20].TextToColumns Destination:=[A10], DataType:=xlDelimited, Comma:=True

Or perhaps do it manually after the results are returned from the Data menu, Text to Columns option.


A.C.

 

[robcarr]

thanks acron, works fine now.



Thanks Rob.