Here is the situation, I am using Elcel XP on a Windows XP machine. I have a spreadsheet that lists all of the files in a directory. It starts in cell B3. This list is text files that I have to convert to excel files. The problem is that the file names change regulary and so does the number of files. I need to get a macro that will open and convert each file, then save it under the same name but as a .xls file.

Here is what I got by recording a macro.

Workbooks.OpenText FileName:="C:\File1010704.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, 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)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs FileName:="C:\File1010704.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

This will open and convert the file corectly and save it but only for that file and since the names change, and the number of fiels cahnges, this woun't work right. I need to get it so that instead of opening the specific file listed that it goes through my list of files and does this process to each of them. Any help would be greatly appreciated. I know how to record macros and do basic stuff but this is a little out of my league.

Thanks for any help,

Mike

Mike,

Here's one solution

Sub AllFiles()
Dim cLastRow As Long
Dim i As Long
Dim sh As Worksheet

Set sh = ActiveWorkbook.ActiveSheet

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cLastRow
UpdateTextFile Left(sh.Cells(i, "B").Value, Len(sh.Cells(i,
"B").Value) - 4)
Next i

End Sub

Sub UpdateTextFile(name As String)
Dim wb As Workbook
On Error Resume Next
Workbooks.OpenText _
Filename:=name & ".txt", _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
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))

With ActiveWorkbook
If Left(.FullName, Len(.FullName) - 4) = name Then
Application.DisplayAlerts = False
.SaveAs Filename:=name, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Close
Application.DisplayAlerts = True
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mike Etzkorn" wrote in message
...
Here is the situation, I am using Elcel XP on a Windows XP machine. I
have a spreadsheet that lists all of the files in a directory. It starts in
cell B3. This list is text files that I have to convert to excel files.
The problem is that the file names change regulary and so does the number of
files. I need to get a macro that will open and convert each file, then
save it under the same name but as a .xls file.

Here is what I got by recording a macro.

Workbooks.OpenText FileName:="C:\File1010704.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, 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)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs FileName:="C:\File1010704.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

This will open and convert the file corectly and save it but only for that
file and since the names change, and the number of fiels cahnges, this
woun't work right. I need to get it so that instead of opening the specific
file listed that it goes through my list of files and does this process to
each of them. Any help would be greatly appreciated. I know how to record
macros and do basic stuff but this is a little out of my league.

Thanks for any help,

Mike