MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using QueryTable instead of OpenText with a file that has fixed width

  Asked By: Karina    Date: Nov 21    Category: MS Office    Views: 2525

I have a module that uses the following line of code.

Workbooks.OpenText Filename:="c:\GTX\Print\sysres.txt",

This works great but what I want to do is open the text file in the
same worksheet. I have done some searching and found that there is a
method called QueryTable that can be used to import into the same

Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
` Define the connection string and destination range.
strConnection = "TEXT;C:\Files\Sales.csv"
Set rngDestination = Sheet6.Range("A1")
` Create the QueryTable.
Set qryTable = Sheet6.QueryTables.Add(strConnection, rngDestination)

The issue I have is that I am not importing a csv file but a file
that uses fixed width columns.

Does anyone have any idea if I can do this using the query table
method. I have done a hunt around but haven't found anything.



1 Answer Found

Answer #1    Answered By: Varick Fischer     Answered On: Nov 21

The help files seem to indicate something like this to be inserted after the
code to define the qryTable:

With qryTable
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = Array(5, 4) 'this is the column widths in
.TextFileColumnDataTypes = _
Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
End With