Is there a batch text-to-columns feature?

[Guest]

I would like to create Excel charts based on data stored in a large number of
text files. Each file consists of lines of x-y coordinates; on each line, a
semi-colon separates the x and y value.

To convert these to Excel (2000), I know I can open the each file and use
the text-to-columns converter. But for the number of files I have, this
would be quite cumbersome.

Is there a feature that allows me to select a batch of files and convert
them all to Excel?

Thanks.

 

[Dave Peterson]

Do all the .txt files have the same layout?

If yes, you could record a macro when you do one manually, then use that code as
the basis for your looping through the other workbooks.

Your recorded macro will have a line that looks similar to this:

Workbooks.OpenText Filename:="C:\My Documents\excel\filenamehere.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 1), Array(11, 1), Array(19, 1), Array(21, 1))

This would have to be modified slightly to point at the selected filenames.

This is the shell that I would use:

Option Explicit
Sub GetTextFiles()

Dim myFileNames As Variant
Dim fCtr As Long

myFileNames = Application.GetOpenFilename _
(FileFilter:="Text Files, *.txt", _
MultiSelect:=True)

If IsArray(myFileNames) Then
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Call DoTheWork(CStr(myFileNames(fCtr)))
Next fCtr
End If

MsgBox "done"

End Sub

Sub DoTheWork(myFileName As String)

Dim wkbk As Workbook
Dim wks As Worksheet

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(11, 1), _
Array(19, 1), Array(21, 1))

Set wks = ActiveSheet
Set wkbk = ActiveWorkbook

'do more work???
End Sub

When the File|open dialog opens, you can select as many as you need (click and
ctrl-click).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm