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

 

[offthelip]

This code will allow you to select a file:

Sub test()
Dim FSO
Dim 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
End Sub

 

[look]

Thanks, offthelip I'll try asap.

 

[look]

Thanks offthelip can the two macros be combined?

 

[offthelip]

this will open the selected file for you, I have no idea what your macro is doing but assuming it worked on the file you gave it and the file selected is the same, it should work

Sub test()Dim FSO
Dim 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
Workbooks.OpenText Filename:=fileselected
End Sub

This is the code that my code replaces:

[COLOR=#333333]Workbooks.OpenText Filename:= _[/COLOR]
[COLOR=#333333]"C:\Users\osoto\Desktop\CCR_20171001_20180930_BFCS.txt"[/COLOR]

 

[look]

Thanks offthelip,

The macro opens a text file parses it and formats the file.
Will try on Monday.

Thanks

 

[look]

Hi offthelip,

I replaced the lines with code you me and the macro does not show up in the macro list box under this workbook.

Thanks

 

[offthelip]

Why don't you post the code that you have ended up with, it is difficult to diagnose the problem with nothing to look at.

 

[look]

Hi offthelip,

Get compile error where your sinpet ends.
Thanks


Sub test()
Dim FSO
Dim 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
,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

 

[offthelip]

you left out the instruction to open the file!!

[COLOR=#333333]Workbooks.OpenText Filename:=fileselected _[/COLOR]