How to Automate Fixed-Width Text Imports in Excel with VBA
Question details
Automate the process of importing fixed-width text files with predefined column break positions using a VBA macro.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing repetitive text data files into a spreadsheet where manual parsing via the Text Import Wizard is too time-consuming.
- Observed behavior
- Instead of manually defining column breaks every time, a programmable method is needed to automatically parse the data into columns using specific character positions.
Ensure your source text file strictly adheres to a fixed-width layout, and precisely count the character start positions for each column break before finalizing your script.
Use the Workbooks.OpenText Method in VBA
Write a VBA macro utilizing the OpenText method to define fixed-width parameters and dynamically select the target text file.
The Workbooks.OpenText method allows you to define exact start positions and data types for each imported column. By leveraging the FieldInfo parameter with an array of positions, you can entirely bypass the manual Text Import Wizard.
Launch Excel and press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
Click on Insert in the top menu, then select Module to create a blank workspace for your macro.
Input the following script: Sub ImportFixedWidth() Workbooks.OpenText Filename:=Application.GetOpenFilename, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(14, 1), Array(66, 1)), TrailingMinusNumbers:=True End Sub
Adjust the FieldInfo Array within the code to match your specific character break positions. For example, Array(14, 1) signifies a new column starting at character 14.
Press F5 or click the Run button. A file dialogue will prompt you to select the fixed-width text file, which will then automatically import based on your defined breaks.
Record an Import Macro and Modify It
If you prefer not to write the array manually, record your actions while using the Text Import Wizard and adjust the file path afterwards.
Automate Data Imports with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for VBA macros, enabling you to seamlessly run your OpenText automation scripts. It is a robust, highly compatible application that easily manages complex data parsing and fixed-width text formatting.
- 1. Install WPS Office: Download and install the free version of WPS Office, then open WPS Spreadsheet.
- 2. Access the Developer Tools: Navigate to the Developer tab to access macro features and the Visual Basic Editor.
- 3. Run Your Automation: Press Alt + F11, paste your OpenText VBA script into a new module, and execute it exactly as you would natively.
Frequently Asked Questions
How do I ensure a specific column imports as Text instead of General?
In your FieldInfo array, update the second parameter of that specific column's array item from 1 to 2. For instance, Array(14, 2) tells the macro to import the column starting at character 14 strictly as Text, which prevents the loss of leading zeros.
What is the purpose of TrailingMinusNumbers:=True in the code?
It instructs the spreadsheet to recognize and treat numbers that have a trailing minus sign (a format commonly found in mainframe and accounting exports, such as '150-') as actual negative numbers rather than standard text strings.
Can I specify custom date formats during a fixed-width import?
Yes. When setting the data type parameter to a date format (using values 3 through 9), you define the exact date structure (like MDY, DMY, YMD) that matches how the dates are formatted in your source text file.
How do I handle errors if I cancel the file selection prompt?
When the user cancels the Application.GetOpenFilename prompt, it returns 'False'. To prevent your macro from crashing, you should assign the result to a variable and add an error check, such as: If myFileName = False Then Exit Sub.
