QueryTable.PreserveColumnInfo Property

Syntax

expression.PreserveColumnInfo

expression A variable that represents a QueryTable object.

Remarks

This property has an effect only when the query table is using a database connection.

You can set this property to False for compatibility with earlier versions of Microsoft Excel.

Example

This example preserves column sorting, filtering, and layout information for compatibility with earlier versions of Microsoft Excel.

Visual Basic for Applications

Visual Basic for Applications
Dim cnnConnect As ADODB.Connection Dim rstRecordset As ADODB.Recordset Set cnnConnect = New ADODB.Connection cnnConnect.Open "Provider=SQLOLEDB;" & _ "Data Source=srvdata;" & _ "User ID=wadet;Password=4me2no;" Set rstRecordset = New ADODB.Recordset rstRecordset.Open _ Source:="Select Name, Quantity, Price From Products", _ ActiveConnection:=cnnConnect, _ CursorType:=adOpenDynamic, _ LockType:=adLockReadOnly, _ Options:=adCmdText With ActiveSheet.QueryTables.Add( _ Connection:=rstRecordset, _ Destination:=Range("A1")) .Name = "Contact List" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With

Dim cnnConnect As ADODB.Connection Dim rstRecordset As ADODB.Recordset Set cnnConnect = New ADODB.Connection cnnConnect.Open "Provider=SQLOLEDB;" & _ "Data Source=srvdata;" & _ "User ID=wadet;Password=4me2no;" Set rstRecordset = New ADODB.Recordset rstRecordset.Open _ Source:="Select Name, Quantity, Price From Products", _ ActiveConnection:=cnnConnect, _ CursorType:=adOpenDynamic, _ LockType:=adLockReadOnly, _ Options:=adCmdText With ActiveSheet.QueryTables.Add( _ Connection:=rstRecordset, _ Destination:=Range("A1")) .Name = "Contact List" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With

preserve preserve column info Preserve Column Info Property preservecolumn PreserveColumnInfo PreserveColumnInfo Property

See also: