open text file, retain column-break settings

  • Thread starter Thread starter drabbacs
  • Start date Start date
D

drabbacs

I routinely need to open text files in excel that will
always have the same layout. Is there a way to save the
layout of column breaks that are needed to parse the file
for use in other files? What I mean is, after I have taken
the time to specify column breaks for file A.txt, can I
save those settings for use in opening file B.txt?

Drabbacs
 
You could record the macro and run that macro whenever you wanted to import
a like-formatted file (with extension .txt).

After you've recorded the macro, you'll probably want to adjust the code a
little to make it more generic. 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, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 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

I like to drop a big old button from the Forms toolbar on a worksheet in the
workbook that contains the code. I assign the macro to the button. And I add a
few notes to that worksheet.

Then just hit the big old button when I want to bring in my .txt file.
 
Back
Top