importing *.txt and save each file to .xls file

[ronabo]

Need help in creating a macro that reads multiple pipe delimited .txt files and save them into an .xls files. Please help me for I have been trying to make this work for weeks now with no luck.

 

[waynerenaud]

Hi ronabo,
I am not a developer so cant help much, but I do have a file with code to import all csv files from a folder. This code may help (Obviously you will need to change the file and dir paths appropriately).You may be able to adapt this to import pipe delimited txt files. Good Luck

Sub GetAllFiles()
Dim MyFile As String
'\* Returns first found file of specified type...
DirPath = ("C:\Software Licensing\software\&quot
MyFile = Dir("C:\Software Licensing\software\*.CSV", vbNormal)

'\* Pass filename to routine to import it...
ImportMyFiles MyFile
'\* Define loop...
Do Until MyFile = ""
'\* Returns next file of type above...
MyFile = Dir
'\* If no more files found, exit loop...
If MyFile = "" Then Exit Do
'\* If not this workbook (assuming this is saved
'\* in the same folder as the files to be imported),
'\* exit loop...
If Not MyFile = ThisWorkbook.Name Then _
ImportMyFiles MyFile
Loop
'\* Save the workbook
ThisWorkbook.Sheets("Sheet1&quot.Select
Range("A1&quot.Select
End Sub

'\* This process will open the file passed to it
'\* and copy the first sheet to the last sheet of
'\* the workbook the macro is run from.
'\* The file is then closed without saving changes.

Sub ImportMyFiles(FileToImport As String)
Dim TWB As Workbook, IWB As Workbook
Set TWB = ThisWorkbook
Set IWB = Workbooks.Open(Filename:=DirPath & FileToImport, UpdateLinks:=False)
IWB.Sheets(1).Copy After:=TWB.Sheets(TWB.Sheets.Count)
IWB.Close SaveChanges:=False
End Sub

 

[waynerenaud]

You will need to add
ThisWorkbook.Save
where it says '\* Save the workbook

 

[ronabo]

Thanks, Waynerenaud. That will get me started.