import fixed width text file
 

The import specification works as intended when used manually (via FileGet
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.

import fixed width text file
 

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" wrote in message
...
The import specification works as intended when used manually (via
FileGet
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.