Open delimited file

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I am modifying existing code to sit my purposes. The existing code loops
through a folder and opens files (xls) I need to modify to open a delimited
file.
The exisiting code will open the file but does not columnize the file.

Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))

I have this bit for opening the delimited file...

Workbooks.OpenText Filename:="G:\MyPath\MyFile.txt", Origin:=437
,StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False,
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), Array(30, 1), Array(31, 1)), TrailingMinusNumbers:=True

How do I combine the two?
Thanks
 
It looks if your code will open a text file (.txt) and parse the data
into seperate columns on each tab or colon ":" in the text file. If
there are no tabs or colon characters, all the data would remain in
column A.

I would only expect to see only one delimiter for the file, either the
tab or the colon.
Goshute
 
The second bit of code does open the text file in itself if I define the
path/file.

How do I add the delimited part to this line....Set mybook =
Workbooks.Open(MyPath & MyFiles(Fnum))
 
Dim fCtr as long
dim myBook as workbook
....get your list of .txt files in to myFiles().

for fctr = lbound(myfiles) to ubound(myfiles)
Workbooks.OpenText Filename:=myPath & myfiles(fctr), Origin:=437, _
...etc
set mybook = activeworkbook
'do whatever you want to continue processing mybook
mybook.worksheets(1).range("A1").value = "hi there!"
next fctr
 
Back
Top