Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query TexttoColumns
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query TexttoColumns
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 wrote in message oups.com... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please assist with TextToColumns Thanks!! | Excel Programming | |||
sub TextToColumns | Excel Programming | |||
Q TextToColumns | Excel Programming | |||
texttocolumns | Excel Programming | |||
TextToColumns | Excel Programming |