Importing text-files

[GARY]

When I open a text-file, the Text Import Wizard is displayed which has
three steps:

In Step 1, I indicate whether the file is delimited or fixed width.
In Step 2, I indicate where I want the column breaks to occur.
In Step 3, I indicate the format of the data in each column.

I very frequently open text-files whose rows have a length of 1,274
characters and, when the column breaks are created, there are 108
columns.

Rather than going through all that work everytime, how can I set up a
"template" (?) that will automatically import the file, break the data
into the 108 columns and format the cells correctly?

 

[Dave Peterson]

I like to create a dedicated macro workbook that contains the code. And then I
put a big button from the Forms toolbar on the only worksheet in that workbook.
I'll add a few instructions to that sheet, too.

I'd tweak the code to get the name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.TXT", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

 

[GARY]

I opened the text file as a delimited file (so Col A contains cells
1,274 characters long).

In B1 thru DD1, I wrote formulas (referring to A1) to divide the data
in each row into separate fields (with the desired formatting).

I then copied and pasted those formulas down to the last row containing
data.

It took some time but the results are exactly what I wanted.

 

[GARY]

I opened the text file as a delimited file (so Col A contains cells
1,274 characters long).

In B1 thru DD1, I wrote formulas (referring to A1) to divide the data
into separate fields (with the desired formatting).

I then copied and pasted those formulas down to the last row containing
data.

(It took some time but the results are exactly what I needed)

 

[Dave Peterson]

I would think that using File|Open and parsing the input record would be
quicker, but it sounds like you have a solution.

 

[GARY]

Creating column-breaks to parse the 1,274-character rows into 107
fields then formatting the resulting fields ONE TIME would be very
tedious. Doing it EVERY TIME I open the files would be horrible!
That's why I took the time to write the formulas. Now, the formulas
are readily available to divide the contents of the cells in Col A and
to format the resulting fields.

 

[Dave Peterson]

My suggestion was to take the time to do it once with the macro recorder turned
on. Then use that macro to import the next million text files.

I gotta believe that importing the file with the macro recorder turned on would
be quicker than creating the 107 formulas.