hi
the code you are running is not refresh code. it creates a new querry each
time it's run with the querytable.add line. ADD.....not refresh.
try this
sub EmilianosRefresh()
sheets('Sheet1").Range("A1").Querytable.refresh Backbroundquery:=False
msgbox "Refresh complete"
end sub

once a query has been created, there is no need to recreate each time - only
refresh. there's a difference.
once created, excel assigns the query a named range with query propeties and
remembers.
right click the the query data range, click view propertiles. the name
should be at the top. check the name box or the define name dialog. the query
name should be there. click it. xl with high light the query range.
if you use the refresh code i provided, i think your ranges will resize the
way they do on the manual refresh.

Regards
FSt1

"Emiliano" wrote:

Hello,

I've a sheet with a range "DataList" linked to external data I refresh
with the following code got from the macro recording command and
sligtly modified according to my needs:

Sub RangeRefresh(Password As String, DataSource As String, destination
As String, _
ListName As String, RangeName As String, Fileodc As String)
'
' Macro1 Macro
' Macro registrata il 18/12/2008 da Emiliano Paperfetti
'
Dim appath As String
appath = ThisWorkbook.Path & "\"
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=" & appath & OrigineDati & ";Mode=Share Deny
Write;Extended Propert" _
, _
"ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password=" & Password & ";Jet OLEDB:Engine
Type=5;Jet OLEDB:" _
, _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password" _
, _
"="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLE" _
, "DB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False"), Destination _
:=Range(dest))
.CommandType = xlCmdTable
.CommandText = Array(NomeElenco)
.Name = NomeRange
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = appath & Fileodc
.SourceDataFile = appath & OrigineDati
.Refresh BackgroundQuery:=False
End With
End Sub

Before to modify the VBA code I refreshed "DataList" manually by the
External Data Toolbar. Since "DataList" has a more little range (named
"LittleRange") in it, this range resized its columns lenght according
to the recordcount of the "DataList" range.

This automatic resize does not happens with the VBA macro.
Someone knows why the VBA macro behavior differs from that of the
manual refreshment?

Thanks and best wishes to all.
Emiliano