Unwanted Querytable Autofilter on ODBC Import

[CallScripter Dev]

Hi,

I am using the following vba code to import data from an external Excel
workbook, into a another reporting worksheet:

Sheets("CV TEL Data").Select

Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Cells.Select
Selection.ClearContents


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DBQ=W:\INI_Tel_Data.xls;DefaultDir=W:\;Driver={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Sheet1$`.*" & Chr(10) & "FROM `W:\INI_Tel_Data`.`Sheet1$`
`Sheet1$`" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_TCM_Tel_Data"
.Refresh BackgroundQuery:=False
End With


When this runs, it gets the data OK, but an unwanted autofilter is created,
which changes the data in 2 number columns in the first row:

01/04/2009 00:29:26 02890839200 288513 00:00:04 F5 F6
01/04/2009 05:56:29 02088813556 288596 00:00:14 14 0
01/04/2009 07:59:58 07814934347 288513 00:00:13 13 0

When this removed using

Cells(1, 6).AutoFilter

The Data in two of the cells is replaced by "F5" "F6"

Is there a way to stop this happening?

Thanks

Andrew

 

[Jacob Skaria]

I could not test this. Please try and feedback......

Replace Cells(1, 6).AutoFilter with

Activesheet.AutoFilterMode = False

If this post helps click Yes

 

[joel]

Are you sure the filter wan't on before you ran the macro?

Try putting in the following to make sure filters arre removed before
running macro

Worksheets("CV TEL Data").ShowAllData

 

[CallScripter Dev]

Hi Jacob,

Many thanks for your reply.

Sorry, this does not remove the filter, and/or the filter is still
being created, when the data is loaded.

Thanks Again

Andrew

 

[CallScripter Dev]

Hi Joel,

Many thanks for your reply.

I am fairly certain that the filter is not there before the macro is
run.

To confirm, I deleted the old data and querytable before running.

When I add "Worksheets("CV TEL Data").ShowAllData" as you suggested,
I get a Run-time error 1004 ShowAllData mehod of worksheet class failed.

I am using Excel 2007, not sure if this makes a difference.

Thanks Again

Andrew