import fixed width text file

[Guest]

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.

 

[Bernie Deitrick]

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