Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a query for pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
VB way to remove the query from a Query Table? | Excel Programming | |||
How tp query a spreadsheet table? | Excel Programming | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |