Edit Text file import code

Hello,

This is a mix of code that one forum member referred me to on Jerry Beaucaires website as well as some i've tried pathetically to incorporate by recording a macro while imported a text file. Jerry's code worked to put each text file onto a seperate sheet in one work book, but perhaps to my lack of experience it didn't seperate it out into rows and columns like it does when you use the import wizard. Any help is much appreciated, I try not to trouble the forum unless I really can't find the answer anywhere else. Thanks in advance to any contributors.

Bryan

Please Login or Register  to view this content.

I have done some more work on this and made a macro by recording me turning a whole folder of text files into excel files. it is rather long and i know it can be simplified, any help would be very appreciated.

Thank you,
Bryan

Sub TXTTOEXCELSCAL()
'
' TXTTOEXCELSCAL Macro
' CONVERTS SCAL WORK QUEUES TO EXCEL WORKBOOKS
'

'
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB IE 2524.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB IE 2524.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB IE 2525.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB IE 2525.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB IE 2526.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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), Array(30, 1), Array(31, 1)), TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB IE 2526.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB ML 2288.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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)), TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB ML 2288.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB ML 2289.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB ML 2289.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB ML 2290.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB ML 2290.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012"
Workbooks.OpenText Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\SCAL HB OC 2417.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
ChDir _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bryan\Desktop\KP DHMO PROJECT\DHMO ROLL FILES\SCAL HB Accounts from WQ\04.04.2012\EXCEL FILES\SCAL HB OC 2417.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub