Multiple .txt file
Posted by harish shelat on April 20, 2001 9:54 AM
Barry Davidson,
I made a mistake in stating my problem. I have a 24 .txt file on c:\temp, They come from VAX every month and i have to convert them into excel spreadsheet. These file are saved on C:\temp as a
for example 3a.txt,5a.txt,9f.txt,220g.txt ext.
I like to open all these files and convert them into excel spreadsheet.
Thanks
Harish
Posted by Barrie Davidson on April 20, 2001 11:09 AM
Harish, try this and let me know if it works for you. Make sure you change the array information for opening the file.
Sub Macro1()
'
'
Dim File_Names As Variant
Dim File_count As Integer
Dim Active_File_Name As String
Dim Counter As Integer
Dim File_Save_Name As Variant
File_Names = Application.GetOpenFilename("Text Files (*.txt), *.txt", , , , True)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
File_count = UBound(File_Names)
Counter = 1
Do Until Counter > File_count
Active_File_Name = File_Names(Counter)
Workbooks.OpenText FileName:=Active_File_Name, Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), _
Array(20, 1), Array(40, 1), Array(60, 1))
'You need to complete the array information so the file is opened properly
File_Save_Name = InStr(1, Active_File_Name, ".txt", 1) - 1
File_Save_Name = Mid(Active_File_Name, 1, File_Save_Name) & ".xls"
ActiveWorkbook.SaveAs FileName:=File_Save_Name, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Counter = Counter + 1
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Barrie
