[VBA] How to Import Fixed-Width Text Files Without Auto-Conversion

2025年12月26日

Introduction

Excel is very convenient, but it has a troublesome habit of automatically converting data into numbers or dates when opening external files. For example, an ID code like “A01” might become “A1”, and a section number like “B-01” might be treated as a date (e.g., “Feb-01”).

In this article, I will introduce a method using VBA to import fixed-width text files while keeping the values exactly as they are, without any automatic conversion.

File Structure

Assume we are reading the following fixed-width text file (e.g., autoconvertdata.txt). The data is separated by specific character positions.

A01   B-01  20250725  PROD-XYZ          12345  **Unwanted**
A02   B-02  20250726  ITEM-123          98765  **Unwanted**

The goal is to import this file while keeping the ID codes and section numbers exactly as text, rather than letting Excel convert them.

VBA Code: Import Fixed-Width Data as String

Sub ImportFixedFileWithoutConversion()
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\autoconvertdata.txt"
    
    Workbooks.OpenText _
        Filename:=filePath, _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array( _
            Array(0, xlTextFormat), _
            Array(6, xlTextFormat), _
            Array(10, xlYMDFormat), _
            Array(20, xlTextFormat), _
            Array(40, xlGeneralFormat), _
            Array(49, xlSkipColumn))
    
    ActiveSheet.Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub

Explanation of Parameters

The FieldInfo parameter defines how each column is handled based on its starting position.

Start Position	Format Type	Description
0	`xlTextFormat`	ID Code. Keeps the value as text (preserves “A01”).
6	`xlTextFormat`	Section Number. Prevents “B-01” from becoming a date.
10	`xlYMDFormat`	Date. Correctly interprets “20250725” as a date.
20	`xlTextFormat`	Product Code. Treated as text.
40	`xlGeneralFormat`	Amount. Treated as a standard number.
49	`xlSkipColumn`	Skip. Ignores unnecessary columns.

Execution Result (Excel)

When you run the macro, the data appears in Excel as follows:

ID	Section	Date	Product	Amount
A01	B-01	2025/07/25	PROD-XYZ	12345
A02	B-02	2025/07/26	ITEM-123	98765

Important Notes

xlTextFormat: Using this prevents Excel’s automatic conversion (to numbers or dates).
xlYMDFormat: This ensures that date strings (YYYYMMDD) are recognized correctly as dates.
xlSkipColumn: Use this to ignore columns you do not need to import.
Encoding: This method may not support UTF-8 encoding properly. If your file is UTF-8, you might need a different approach (like ADODB.Stream).

Advanced Tips

CSV Files: You can apply similar logic to CSV files using Workbooks.OpenText to prevent data corruption.
Formatting: You can add code to adjust fonts and borders after the import is complete, making it ready for reporting immediately.
Flexibility: You can adjust the FieldInfo array to match any file format you encounter in your work.

Summary

When importing fixed-width files in Excel, VBA control is essential to prevent important strings like “A01” or “B-01” from being changed automatically. By mastering the usage of FieldInfo introduced in this article, you can prevent unintended conversions and ensure accurate data processing.

If you have ever struggled with “values changing just by opening the file,” please give this method a try.

よかったらシェアしてね！