import fixed width text file

The import specification works as intended when used manually (via File>Get
External Data....). But when the spec is used within a Routine the data
imports into one column only. I think the error is in the syntax of
TransferText.

DoCmd.TransferText acImportDelim, "Tagged Transactions Import
Specification", _
"RawDataImport", TextFile2Open

The Help file says "...For a fixed-width text file, you must either specify
an argument or ... " in relation to the specification. Any help much
appreciated.

OE,

For importing fixed width data with a macro, you can use the OpenText
method:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Examples\Test.prn", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(3, 1), Array(5, 1), Array(8, 1), Array(11, 1),
Array(14, 1)), _
TrailingMinusNumbers:=True

Or you can use QueryTables:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Examples\Test.prn" _
, Destination:=Range("A1"))
.Name = "Test"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = Array(3, 2, 3, 3, 3)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I'm not familiar with the TransferText method, so I can't help you there.

HTH,
Bernie
MS Excel MVP


"OE" <OE@discussions.microsoft.com> wrote in message
news:C91773C7-E2D8-4509-B697-AA9198BBAB84@microsoft.com...
> The import specification works as intended when used manually (via
File>Get
> External Data....). But when the spec is used within a Routine the data
> imports into one column only. I think the error is in the syntax of
> TransferText.
>
> DoCmd.TransferText acImportDelim, "Tagged Transactions Import
> Specification", _
> "RawDataImport", TextFile2Open
>
> The Help file says "...For a fixed-width text file, you must either
specify
> an argument or ... " in relation to the specification. Any help much
> appreciated.