The Shifty Lie of a Query Table
 

I am trying to leverage a query tables refresh ability with a simple txt
file import. I am having a problem with the Query table shifting columns over
when the query table code is run. I need to have the query table to simply
€˜paste into worksheet as if I selected Cells(1,1) and pasted them in. At
first I thought there should be a parameter or option to do this but I have
not found one. Does anyone know how to do this?

Private Sub OpenFile( cflaSpec As Range)
Dim FileDir As QueryTable
Dim DirPath As String
'Initialize
DirPath = "\\gershwin\majorprojects\CFLA\CFLAReportName & ".txt"
Set ProperPallet = ActiveWorkbook.Worksheets.Add
Set FileDir = ProperPallet.QueryTables.Add( _
Connection:="TEXT;" & DirPath, _
Destination:=wksht.Cells(1, 1) _
)
With FileDir
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Wksht.name= UCase(cflaSpec)
End Sub

The Shifty Lie of a Query Table
 

Add this line just before the Refresh line in your code:

..RefreshStyle = xlOverwriteCells

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DynamiteSkippy" wrote:

I am trying to leverage a query tables refresh ability with a simple txt
file import. I am having a problem with the Query table shifting columns over
when the query table code is run. I need to have the query table to simply
€˜paste into worksheet as if I selected Cells(1,1) and pasted them in. At
first I thought there should be a parameter or option to do this but I have
not found one. Does anyone know how to do this?

Private Sub OpenFile( cflaSpec As Range)
Dim FileDir As QueryTable
Dim DirPath As String
'Initialize
DirPath = "\\gershwin\majorprojects\CFLA\CFLAReportName & ".txt"
Set ProperPallet = ActiveWorkbook.Worksheets.Add
Set FileDir = ProperPallet.QueryTables.Add( _
Connection:="TEXT;" & DirPath, _
Destination:=wksht.Cells(1, 1) _
)
With FileDir
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Wksht.name= UCase(cflaSpec)
End Sub

The Shifty Lie of a Query Table
 

Works Perfectly... Thanks Ron

-D

"Ron Coderre" wrote:

Add this line just before the Refresh line in your code:

.RefreshStyle = xlOverwriteCells

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DynamiteSkippy" wrote:

I am trying to leverage a query tables refresh ability with a simple txt
file import. I am having a problem with the Query table shifting columns over
when the query table code is run. I need to have the query table to simply
€˜paste into worksheet as if I selected Cells(1,1) and pasted them in. At
first I thought there should be a parameter or option to do this but I have
not found one. Does anyone know how to do this?

Private Sub OpenFile( cflaSpec As Range)
Dim FileDir As QueryTable
Dim DirPath As String
'Initialize
DirPath = "\\gershwin\majorprojects\CFLA\CFLAReportName & ".txt"
Set ProperPallet = ActiveWorkbook.Worksheets.Add
Set FileDir = ProperPallet.QueryTables.Add( _
Connection:="TEXT;" & DirPath, _
Destination:=wksht.Cells(1, 1) _
)
With FileDir
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Wksht.name= UCase(cflaSpec)
End Sub