software

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.

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:=quot;C:\My Documents\excel\filenamehere.txtquot;, _
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:=quot;Text Files, *.txtquot;, _
MultiSelect:=True)

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

MsgBox quot;donequot;

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:
www.mvps.org/dmcritchie/excel/getstarted.htm

hmm wrote:
gt;
gt; I would like to create Excel charts based on data stored in a large number of
gt; text files. Each file consists of lines of x-y coordinates; on each line, a
gt; semi-colon separates the x and y value.
gt;
gt; To convert these to Excel (2000), I know I can open the each file and use
gt; the text-to-columns converter. But for the number of files I have, this
gt; would be quite cumbersome.
gt;
gt; Is there a feature that allows me to select a batch of files and convert
gt; them all to Excel?
gt;
gt; Thanks.

--

Dave Peterson