Unwanted Querytable Autofilter on ODBC Import
 

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:\;Drive r={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTrans actions=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

Unwanted Querytable Autofilter on ODBC Import
 

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

Replace Cells(1, 6).AutoFilter with

Activesheet.AutoFilterMode = False

If this post helps click Yes
---------------
Jacob Skaria


"CallScripter Dev" wrote:

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:\;Drive r={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTrans actions=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

Unwanted Querytable Autofilter on ODBC Import
 

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" wrote:

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:\;Drive r={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTrans actions=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

Unwanted Querytable Autofilter on ODBC Import
 

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



"Jacob Skaria" wrote:

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

Replace Cells(1, 6).AutoFilter with

Activesheet.AutoFilterMode = False

If this post helps click Yes
---------------
Jacob Skaria


"CallScripter Dev" wrote:

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:\;Drive r={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTrans actions=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

Unwanted Querytable Autofilter on ODBC Import
 

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


"joel" wrote:

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" wrote:

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:\;Drive r={Driver do
Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;" _
), Array( _

"MaxScanRows=20;PageTimeout=5;ReadOnly=1;SafeTrans actions=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