Automatically Parsing Fixed-width Text Import

[Bill]

I want to automate this function for a daily feed of a text file with fixed
width fields. Ther are about thirty column breaks to select and this would
be a real pain to have to do every day. I know I can record a macro, but I
want to start the process after the File Open dialog. My VBA skills are
effectively non-existent but if I had some code I could probably modify it
to my needs. Can someone please point me in the right direction. .

 

[Dave Peterson]

Record a macro when you do it once. But then modify the macro to show that
File|open dialog.

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.

 

[Bill]

Thanks, Dave. This looks like it will do the trick.
Cheers,
Bill

 

[Bill]

Thanks again, Dave. Works fine!
Cheers,
Bill

Record a macro when you do it once. But then modify the macro to show
that
File|open dialog.

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.