Query file without copy inside Excel

[JHH]

I have a rather long URL-query in a file, "353.iqy". (The URL contains 353
characters)
I have let excel2000 write a macro which launches this query. Unfortunately
the resulting sheet saves the query inside itself under an ever 'increasing'
name . For the moment I manually go to external data , properties of table,
then unhatch the save the query and 'force' it to accept that, but next time
the internal name has become 353_2 f.inst. then next time 353_3 etc. which
means that the continuation of the macro that erases this query-table and
saves the file without macro and without query, just the data, cannot just
delete the query (as it has changed name).

Is it possible to include something in the query-file to avoid saving a copy
'inside' under a changed name ?

I tried of course to edit the inside file and no longer use the 353.iqy on
disk, but that often did not work because it exceeds the 254 character length
that excel readily accepts.
It seemed to work when started manually, but when started by scheduler at
the appropriate time it often failed.

Is this 254 character limit the same in excel2003 and 2007 ?

 

[Don Guillett]

What is the url? Have you tried an external query instead of the iqy?
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

 

[JHH]

I discover I can't attach the FStrunc0.xls file with its macro to do the
refresh and the failing delete querytables, so I paste the macro here :

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 12.09.2009 par P-1
'
' Touche de raccourci du clavier: Ctrl+m
'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;E:\Shared\FSdata\fs-b2.iqy", Destination:=Range("A1"))
.Name = "fs-b2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "19"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Sheets("Feuil1").QueryTables("fs-b2_1").Delete
End Sub

And the iqy-file content :

WEB
1
http://futuresource.quote.com/quote...gh,low,last,netchg,vol,openint,exchg,datetime

Selection=19
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False

I hope this makes it clearer what a really simple task I'm trying to
acomplish (at least as a first step)