Prompting user to open file

Hi all,

I wanted some help with an issue.

I am writing a macro that opens and formats a text file. I can record the macro to get this result:

Workbooks.OpenText filename:= _
"N:\Customer Relationships\Working Files\nealeP\Customer Relationships\Reports\PARMS Reports\HR reports\November 2006 Leave.doc" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(11, 1), Array(19, 1), Array(20, 1), Array(22, 1), Array(42, 1), _
Array(49, 1), Array(59, 1), Array(69, 1), Array(79, 1), Array(89, 1), Array(99, 1), Array( _
109, 1), Array(119, 1), Array(129, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

What I want is the Macro to bring up the open file box so that the user can select whatever file they want then when the user selects the file I want the string of text to be broken up into the arrays as outlined in the recorded macro.

Please let me know if I have not been clear enough and thank-you very much for you assistance!!

Pedros.

Pedros

Use the GetOpenFileName function


Dim sFile As String
sFile$ = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:= _
sFile _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(11, 1), Array(19, 1), Array(20, 1), Array(22, 1), Array(42, 1), _
Array(49, 1), Array(59, 1), Array(69, 1), Array(79, 1), Array(89, 1), Array(99, 1), Array( _
109, 1), Array(119, 1), Array(129, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Thanks mudraker much appreciated.

I had played around with the getopenfilename function but I couldn't figure out how to tie it back up with the arrays.

thanks again,

Pedros.