Edit

QueryTable.PreserveColumnInfo property (Excel)

  • Article

True if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is True. Read/write Boolean.

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.

If you import data by using the user interface, data from a web query or a text query is imported as a QueryTable object, while all other external data is imported as a ListObject object.

If you import data by using the object model, data from a web query or a text query must be imported as a QueryTable, while all other external data can be imported as either a ListObject or a QueryTable.

Use the QueryTable property of the ListObject to access the PreserveColumnInfo property.

Example

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

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

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.