External data

[Emiliano]

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 OLEDBatabase 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 OLEDBon'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

 

[FSt1]

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]

FSt1 a écrit :

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

Thanks, you are right. I recorded the creation of the query not the
refreshement of it. Now I have used your code, but the problem remains.
I think I have not explained it much clearly. So I try to do better
here:

suppose the query is linked to a range of 5 columns x 10 rows. In this
range there is a minor range (I have called "LittleRange")
corresponding exatly to the second column of the big range (1 column x
10 rows). When I refreshed it manually I got the minor range resized
according to the number of the record of the table considered. If the
record number decreased to 5 the "LittleRange" rows decreased to 5.
Refreshing the query with your code the "LittleRange" rows number
remains of 10 instead of to decrease to 5.

Any tip?
eggpap

 

[Emiliano]

Emiliano a écrit :

FSt1 a écrit :

Thanks, you are right. I recorded the creation of the query not the
refreshement of it. Now I have used your code, but the problem remains. I
think I have not explained it much clearly. So I try to do better here:

suppose the query is linked to a range of 5 columns x 10 rows. In this range
there is a minor range (I have called "LittleRange") corresponding exatly to
the second column of the big range (1 column x 10 rows). When I refreshed it
manually I got the minor range resized according to the number of the record
of the table considered. If the record number decreased to 5 the
"LittleRange" rows decreased to 5.
Refreshing the query with your code the "LittleRange" rows number remains of
10 instead of to decrease to 5.

Any tip?
eggpap

Sorry, I had another question:

you have said
how to know if the query exists and I hav'nt to re-create it?

eggpap