Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
External data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
External data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
External data
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 "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 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 he 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
External data
Emiliano a écrit :
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 "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 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 he 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 there is no need to recreate each time - only refresh. how to know if the query exists and I hav'nt to re-create it? eggpap |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Getting External Data based on criteria insde of the external data | Excel Discussion (Misc queries) | |||
Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data | Excel Programming | |||
insert entire row for new data, external data range doesnt work | Excel Programming | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming |