Changing fixed width (column break) defaults when importing text

[Guest]

I run the same reports for numerous accounts, month after month. The report
is always in text and is opened in Excel. I then need to use Fixed Width to
break it up into the proper column widths. The column width pattern is the
same every time(for example, 10 characters then 27 characters then 13
characters, etc) . With every report, I need to Change, Delete, and Move the
default break lines. Since I do this so often with the same report, does
anyone know how to set up the break lines so they default to the widths that
I need? The entire department will be very grateful as we all suffer from
having to do this. Thank you very much.

 

[Dave Peterson]

I don't think you'll be able to change the way excel guesses at those field
breaks.

But since the text file layout never changes, you could make life a lot easier
by recording a macro when you do it manually.

Then just rerun that recorded macro when you need to bring in that next set of
data.

Saved from a previous post:

I do this when I have this situation (it's useful if you import the same file
format repeatedly).

1. Make sure the input file is named *.txt (well, anything but *.csv)
2. Start a new workbook
3. tools|macro|record new macro (record in this new workbook)
4. file|open your .txt file
5. Run that text to columns wizard and divide your records into 100 fields
6. continue formatting the data (add headers/subtotals/page layout/filters...)
7. Stop recording
8. Now close the .txt file and save that workbook with your recorded macro.
9. Show the Forms toolbar (view|toolbars|forms)
10. Draw a nice big old button on that worksheet. Assign your macro to it.

If the filename never changes, you may not need to change the code at all.

If the filename could vary, you could tweak your code to ask the user to select
a file.

When you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1,...

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

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

You may have to tweak some other code--depending on how much other stuff you did
in your recorded macro.

If you have trouble, post back with a question.