Import *.asc file into excel fixed width

[Guest]

I am opening a .asc file into excel. When i do so i get the import text wizard and choose fixed width. The file contains several hundred lines of information with about 300 characters on each line. The default column dividers are not correct for the way i need the data broken up. For instance; The 25 characters in position 41-65 is the name of a person but the column divider is set at position 53 splitting up the name. It takes me a considerable amount of time to change the dividers for this whole file. What I would like to do is automate the task. What tool can I use to import the file into excel and automatically set 110 column dividers at very specific points.

 

[Nick Cranham]

Hartsell,
Look up OpenText in the help and/or record a macro of your manual actions.

nickHK

I am opening a .asc file into excel. When i do so i get the import text

wizard and choose fixed width. The file contains several hundred lines of
information with about 300 characters on each line. The default column
dividers are not correct for the way i need the data broken up. For
instance; The 25 characters in position 41-65 is the name of a person but
the column divider is set at position 53 splitting up the name. It takes me
a considerable amount of time to change the dividers for this whole file.
What I would like to do is automate the task. What tool can I use to import
the file into excel and automatically set 110 column dividers at very
specific points.

 

[Dave Peterson]

I think I'd start a new workbook and record a macro when I did it once manually.

Then I could just replay this macro whenever I wanted to import a file with the
same layout.

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 text file.

 

[Dave Peterson]

And you could use this instead of .txt:

filefilter:="ASC Files, *.ASC"

To make it slightly prettier/easier.