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
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