Import Multiple Colon Delimited Files in to Excel

[jarvisaurus]

How do I convert multiple .txt files into excel from a specified path? I'd like each file to be in a separate workbook saved into the same folder. I have hundreds of files each month and converting one by one using the wizard is rather time consuming. Thanks in advance.

 

[Joe4]

This topic has been discussed a number of times on this forum. If you use the Advanced Search functionality, you should be able to locate a lot of threads on this topic. Post back if you have any problems locating those.

 

[jarvisaurus]

Sorry Joe, I have searched and everything I found was to consolidate all the files into a single sheet or multiple worksheets within the same workbook. I'm fairly new to vba and have tried editing a couple that I found but to no avail. Thanks anyway, I'll keep looking.

 

[jarvisaurus]

I got up to this far:

Sub getfile()
Dim TargetSht As Worksheet
Dim i As Integer
Dim Wks As Worksheet
Application.ScreenUpdating = False
Set TargetSht = ThisWorkbook.ActiveSheet
With Application.FileSearch
.NewSearch
.LookIn = "filepath"
.SearchSubFolders = False
.FileName = "*.txt*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Workbooks.OpenText FileName:= _
.FoundFiles(i), Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", 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), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
Set wb = ActiveWorkbook
wb.SaveAs FileName = Left(wb.FileName, Len(wb.FileName) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
wb.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub

_____________________________________________________

but I am having trouble saving error on this part:

Set wb = ActiveWorkbook
wb.SaveAs FileName = Left(wb.FileName, Len(wb.FileName) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
wb.Close SaveChanges:=False

Your help is greatly appreciated. Thanks.

 

[Joe4]

What exactly is the error that it is giving you?

If you add the line (before the code causing the error):
MsgBox wb.FileName
this will tell you the file name and may give some clues as to what the problem is.

 

[jarvisaurus]

"Save as Method of workbook class failed" and it also asks me if I want to save over the file "False".

 

[Joe4]

What does the message box line of code I gave you in my previous post return?

 

[jarvisaurus]

Sorry thought that was the message box...I put that line before the error and I received "runtime error 438...object does not support this property or method".

 

[Joe4]

Which version of Excel are you using?
I don't believe that "Application.FileSearch" works in versions of Excel 2007 and later. I'll see what I can whip up.

Is your file path going to always be the same, so it can be automated so that users aren't prompted to select it?

 

[jarvisaurus]

I am using version 2003-2007. Filepath won't always be the same but I can always change the path manually. Thank you!