Hi,
I recorded a macro that can convert a text file into an Excel workbook. But the code looks messy with quite some redundant statements. I tried to streamline it but with no success. Like the Arrays, do we need that many arrays?
Besides, what if I have more than 30 fields? That being the case, is the macro going to work?
Thanks in advance.
Sub TEXT_TO_EXCEL(fname As String)
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\All Users\Documents\" & fname & Chr(46) & "TXT" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=0, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Documents\Zephyr\PASSPORT PC TO HOST\" & fname & Chr(46) & "xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub GoGetIt()
TEXT_TO_EXCEL "FACTS_DETAIL"
TEXT_TO_EXCEL "FACTS_SUMMARY"
End Sub
I recorded a macro that can convert a text file into an Excel workbook. But the code looks messy with quite some redundant statements. I tried to streamline it but with no success. Like the Arrays, do we need that many arrays?
Besides, what if I have more than 30 fields? That being the case, is the macro going to work?
Thanks in advance.
Sub TEXT_TO_EXCEL(fname As String)
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\All Users\Documents\" & fname & Chr(46) & "TXT" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=0, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Documents\Zephyr\PASSPORT PC TO HOST\" & fname & Chr(46) & "xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub GoGetIt()
TEXT_TO_EXCEL "FACTS_DETAIL"
TEXT_TO_EXCEL "FACTS_SUMMARY"
End Sub