need code to iterate Excel query table properties

[gtejas]

Looking for help with the following:

Sub ListQueryTableProperties()
Dim ws As Worksheet, P As propterty
Dim wsQry As QueryTable

Set ws = Worksheets("Sheet4")
Set wsQry = ws.QueryTables(1)

For Each P In wsQry.Properties
Debug.Print P.Name; Tab; P.Value
Next P

Exit1:
If Not wsQry Is Nothing Then
Set wsQry = Nothing
End If
If Not ws Is Nothing Then
Set ws = Nothing
End If
End Sub

 

[PHV]

Which version of excel ?
In my xl2k3 there is no QueryTable.Properties ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gtejas]

Office Pro 2003

Yes I have found that there is not QueryTable.Properties.

What I was hoping that someone might have a work around.

 

[PHV]

Have a work around to do WHAT ?

 

[SkipVought]

What querytable properties are you interested in?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gtejas]

All properties.
AdjustColumnWidth, Application, BackgroundQuery, CommandText, CommandType, Connection, Creator, Destination, EditWebPage, EnableEditing, EnableRefresh, FetchedRowOverflow, FieldNames, FillAdjacentFormulas, ListObject, MaintainConnection, Name, Parameters, Parent, PostText, PreserveColumnInfo, PreserveFormatting, QueryType, Recordset, Refreshing, RefreshOnFileOpen, RefreshPeriod, RefreshStyle, ResultRange, RobustConnect, RowNumbers, SaveData, SavePassword, Sort, SourceConnectionFile, SourceDataFile, TextFileColumnDataTypes, TextFileCommaDelimiter, TextFileConsecutiveDelimiter, TextFileDecimalSeparator, TextFileFixedColumnWidths, TextFileOtherDelimiter, TextFileParseType, TextFilePlatform, TextFilePromptOnRefresh, TextFileSemicolonDelimiter, TextFileSpaceDelimiter, TextFileStartRow, TextFileTabDelimiter, TextFileTextQualifier, TextFileThousandsSeparator, TextFileTrailingMinusNumbers, TextFileVisualLayout, WebConsecutiveDelimitersAsOne, WebDisableDateRecognition, WebDisableRedirections, WebFormatting, WebPreFormattedTextToColumns, WebSelectionType, WebSingleBlockTextImport, WebTables, WorkbookConnection

 

[SkipVought]

Not all properties are applicable for a query table. Depends if its a WEB query or DATABASE or TEXT

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gtejas]

True, but for the properties that are applicable it would be much less hassel to loop thru them and let the loop generate a report instead of listing them out one by one.

They are in a object container somewhere and I am using a computer. But for now I just keep writting them out on the chalk board one by one.