Conver text file to excel macro

[look]

Hi All,

Recorded macro below to import text file to Excel.
Need to make macro more universal.
File name and location could change. Can I point to file that needs to be converted.
Any help is appreciated.
Use Excel 2003.
Thanks

ChDir "C:\Users\osoto\Desktop"
Workbooks.OpenText Filename:= _
"C:\Users\osoto\Desktop\CCR_20171001_20180930_BFCS.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 3), Array(7, 3), Array(8, 3), Array(9, 3), _
Array(10, 2), Array(11, 1), Array(12, 2), Array(13, 1), Array(14, 2), Array(15, 1), Array( _
16, 2), Array(17, 1), Array(18, 1), Array(19, 2), Array(20, 2), Array(21, 1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 1), Array( _
29, 1), Array(30, 2), Array(31, 1), Array(32, 2), Array(33, 1), Array(34, 2), Array(35, 1), _
Array(36, 2), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _
42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
Array(49, 1), Array(50, 1)), TrailingMinusNumbers:=True
Cells.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.RowHeight = 15
ActiveCell.FormulaR1C1 = "ORIG_CLAIM_NUM"
Columns("F:I").Select
Selection.NumberFormat = "m/d/yy;@"
End Sub

 

[look]

offthelips,

I thought that was part of the code your code replaces?

This is the code that my code replaces:
Code:
Workbooks.OpenText Filename:= _
"C:\Users\osoto\Desktop\CCR_20171001_20180930_BFCS.txt"


Thanks

 

[look]

offthelip,

The name of the file will also change.
It depends on what I point to.

Thanks

 

[offthelip]

I thought that was part of the code your code replaces?

This is the code that my code replaces:
Code:
Workbooks.OpenText Filename:= _
"C:\Users\osoto\Desktop\CCR_20171001_20180930_BFCS.txt"

Your original code does include a workbooks open line but the code i posted does as well and it is slightly different:

Sub test()Dim FSODim fileselected As Variant
Dim fDialog As FileDialog
Path = ActiveWorkbook.Path & "\"








    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
         
    'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = Path
    'Show the dialog. -1 means success!
    If fDialog.Show = -1 Then
        fileselected = fDialog.SelectedItems(1)
    End If
[COLOR=#ff0000]Workbooks.OpenText Filename:=fileselected[/COLOR]
End Sub

The name of the file will also change.
It depends on what I point to.

Yes!! that is what you asked for!! Is ther a problem!!

 

[look]

offthelip,

No problem.
Thanks for your help.
Works fine now.
Combine code.

Sub test()
Dim fDialog As FileDialog
Path = ActiveWorkbook.Path & ""




Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.Title = "Select a file"
fDialog.InitialFileName = Path
'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
fileselected = fDialog.SelectedItems(1)
End If
Workbooks.OpenText Filename:=fileselected
Cells.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.RowHeight = 15
Columns("A:AY").Select
Selection.Columns.AutoFit
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Columns("F:I").Select
Selection.NumberFormat = "m/d/yy;@"
Range("K6").Select
ActiveWindow.SmallScroll ToRight:=35
Columns("AM:AR").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
ActiveWindow.SmallScroll ToRight:=8
End Sub