automated file loading and saving

[M H]

Hi,
I have a folder containing hundreds of tab-delimited text files, and
need to convert all of them into Excel files. Presumably I should
automate the conversion in the following steps:
1. Use Application.FileDialog(msoFileDialogFilePicker) or .FileSearch to
point to the folder containing the files (I want it to be manual);
2. Excel loads individual files, one by one, with bypassing the wizard,
to become a worksheet of data;
3. Excel saves the data with the same name (but certainly will be .xls
not .txt) to the same directory of the source files.
4. Repeat the action until all files are done, show up a prompt msgbox
with time spent.

From Wrox Excel 2003 VBA book, I've got something like following after
modifications:

Public Sub FindDataFiles()
Dim FileName As Variant
Dim Message As String
Dim Count As Long

With Application.FileSearch
' Prepare search criteria
.NewSearch
.LookIn = "c:\data"
.SearchSubFolders = True
'.squ is the extension of my data files
.FileType = "*.squ"
.LastModified = msoLastModifiedAnyTime
Count = .Execute

' Prepare output text
Message = Format(Count, "0 ""Files Processed""")

' Here I need some codes to do what
' abovementioned and a timer.
' The Message has also to be modified
' to show the time
Call MsgBox(Message, vbInformation)
End With
End Sub

Much much thanks if someone could help for this.

rgds,
Maurice

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bunter_22]

Hi Maurice,

The code below should do what your after:

Sub FindDataFiles()
StartTime = Now
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = True
.Filename = "*.squ"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Application.DisplayAlerts = False
Opentxt = .FoundFiles(i)
Workbooks.OpenText Filename:=Opentxt, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlNone, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, Comma _
:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Opentxt, xlNormal
Next i
Timemsg = "The time taken was " & Round((Now - StartTime) * 24 * 60
* 60, 2)
MsgBox "All files that were found have now been saved as excel
files." _
& vbCr & Timemsg & " Second(s)"
End Sub

The timer that you wanted I presumed was to calculate how long the
macro took. If this is wrong then you will need to change the Msgbox at
the end of the code. The workbook open line opens the text file but
doesn't delimit the columns at all, this was also a guess.

Any problems then give me a shout.

James