Web Query TexttoColumns

[becalm]

Hope someone can help me out with a problem I am trying to fix.

I have a web query that I would like to run every 15 minutes. It grabs
a comma delimited file from the web and then I want it to break it up
into different columns. For some reason if I click my macro button it
will work perfectly, but when I leave the file open and it updates on
its own it doesnt split the data up into the columns. It just lumps
everything into one column. Here is the code I have so far.

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" &
qurl, Destination:=DataSheet.Range("E8"))
.Name = "Query"
.FieldNames = True
.PreserveFormatting = True
.AdjustColumnWidth = True
.BackgroundQuery = True
.RefreshPeriod = 15
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=True
.SaveData = True
End With

Range("E8").CurrentRegion.TextToColumns
Destination:=Range("E8"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False,
other:=False

Could anyone help me out? Thanks!

 

[NickHK]

I would suggest changing the .Refresh from BackgroundQuery:=True to False.
As it is, you code continues running after the .Refresh is called so the
TexttoColumns (probably) executes before any data is returned from the
query.
Also, the .savedata may error if executed before the refresh finishes.
If you call this routine multiple times, you will create a new query each
time (as you are using .Add), which is probably not what you intend. You can
see this by checking the names that you have; probably "Query", "Query_1",
"Query_2" etc. So you only need to create it once.

But apart from that, although the query may refresh every 15 minutes, there
is no code to trigger the TextToColumns again, so the new data remains as it
is delivered from the query.
It may be easier to trigger the .refresh yourself, following it with the
formatting routine, started from the workbook_Open event, or a button click.
e.g.

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:15:00"), "RefreshAndFormat"
End Sub

Function RefreshAndFormat() As Long
With Worksheets(1).QueryTables("Query")
.Refresh BackgroundQuery:=False
.Destination.CurrentRegion.TextToColumns _
Destination:=Range("E8"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False
End With
End Function

That should give you some ideas.

NickHK