gparreira
Oct 27th, 2000, 01:34 PM
Hi, guys,
I have a problem concerning the refreshing of a QueryTable. I use a connection to get data from a Oracle RDB database located in a Alpha VMS server. To make that I use some code adapted from some code generated by the Excel macro recorder. Here it is:
With Dados.QueryTables.Add(Connection:= _
ConnString, _
Destination:=Dados.Cells(1, intCol))
.Sql = SQLStr
.FieldNames = True
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = False
End With
The problem is that I refresh this query every 15 minutes to create some graphs of the process. Every time I refreshed it some information about the query definition is saved in the sheet in some internal place, the new ones above the old ones. We can see this information in the External Data Range properties of the Data menu (maybe the forms names are a little bit different because I'm using a portuguese version of Excel and I donīt know the correct names in english). When I save the worksheet all that is saved together with the data and makes the file bigger and bigger.
To avoid this I need some code that makes the same as showing the External Data Range properties form and unchecking the option Save query definition. Unfortunately the macro recorder donīt generate some satisfatory code for this operation.
Any ideas???
I have a problem concerning the refreshing of a QueryTable. I use a connection to get data from a Oracle RDB database located in a Alpha VMS server. To make that I use some code adapted from some code generated by the Excel macro recorder. Here it is:
With Dados.QueryTables.Add(Connection:= _
ConnString, _
Destination:=Dados.Cells(1, intCol))
.Sql = SQLStr
.FieldNames = True
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = False
End With
The problem is that I refresh this query every 15 minutes to create some graphs of the process. Every time I refreshed it some information about the query definition is saved in the sheet in some internal place, the new ones above the old ones. We can see this information in the External Data Range properties of the Data menu (maybe the forms names are a little bit different because I'm using a portuguese version of Excel and I donīt know the correct names in english). When I save the worksheet all that is saved together with the data and makes the file bigger and bigger.
To avoid this I need some code that makes the same as showing the External Data Range properties form and unchecking the option Save query definition. Unfortunately the macro recorder donīt generate some satisfatory code for this operation.
Any ideas???