|
© Matti Mattila, CPFA, CISA, CIA |
Text File Import Macro #2 |
|
| Purpose of the macro |
 |
| Picture 1: Macro's source code
|
|
| - |
The macro imports lines of a text file (source file) to MS Excel (Excel) worksheet.
You have two criteria available in limiting import to the lines of interest.
In Excel, using its functions, you can divide contents of each the line into columns.
|
|
| Import criteria |
|
| - |
First, import of text lines can be limited by the count of lines that the macro reads [and processes],
e.g. 1000 first lines in the source file.
|
| - |
Secondly, import can be limited by means of string that must have a matching string
in given line positions of the source file [1] (filter).
When filter is left unused, all records up to the count of lines determined by a user
will be read into Excel.
|
|
| Source Code
|
| - |
There is source code of the macro in picture 1.
|
| - |
The explanations about HeaderFooter macro
and the comments (in green) are expected to describe well enough how the macro works;
no other explanations are given here.
|
|
| UserForm
|
|
| - |
The macro uses one userform 'Xl07c2'.
On the form a user (1) selects how many lines of the source file are subject to import,
(2) defines [when needed] use of a filter, and (3) selects where to import text lines
(either a workbook containing the macro or to a new workbook).
|
|
| Download
|
|
| - |
You can download source code of the macro module and macro's userform ('Xl07c2.frm')
here.
|
|
| How to run it?
|
|
|
| - |
Guidance on how to make an operational macro with the source code and the userform is
is here ('Using the Macros').
|
|
|
|
|
[1]
Here is an example about using a criterion in import.
|
 | |
Picture 2 |
|
|
| - |
After selection of source file (selection not shown here)
a user is shown a dialog (picture 2),
where he/she can determine what lines to import (e.g. rows between and including 1-2000)
and where to import them
(either to a new worksheet of the workbook containing "TextImport2" macro or to a new workbook).
|
| - |
A user has the possibilities shown on the form in picture 2,
would he/she like to set conditions for text file import.
When the form is filled as appears in picture 2 the macro imports
only those text lines, among the 200 first source file lines,
where there is not a string "ZAD" in positions 11- 13.
|
| - |
In order to import all lines of a text file
a user does not need to know exact count of lines in a text file.
He/she can fill in the box e.g. the maximum row count of an Excel sheet (MS Excel 2000-2003: 65.536 lines).
The macro imports as many records as there are in the text file (e.g. 20.120),
finishing reading of lines to last line in the source file.
|
| - |
A user can stop running the macro by inputting zero (0) in box 'read following number of lines (0 = End)'.
or by leaving the form unfilled.
|
|
|